Discussion :

[fsmrel]

Bonsoir,

Visiblement, on sort du cadre de la recherche pure, on commence à s’intéresser aux souhaits des utilisateurs ayant joué les cobayes et dont on a étudié les réactions, en conséquence de quoi des éléments n’ayant rien à voir avec la théorie relationnelle viennent « compléter » le langage, et ça n'est pas fini...

Hormis que ce soit à la demande des utilisateurs, et non un choix issue de la théorie, je ne comprends même pas pourquoi la théorie a fait une impasse sur cet clause.

Heu... Vous attribuez à SQLpro des propos qui sont les miens... Je répondrai donc :

Si la théorie a fait une impasse sur la clause ORDER BY c’est parce celle-ci n’a rien à voir avec celle-là. Comme je l’ai déjà précisé, Ted Codd, père de la théorie relationnelle, s’est situé dans le contexte de la théorie des ensembles et de la logique des prédicats. Or, le père de la théorie des ensembles, Georg Cantor, et celui de la logique moderne (avec notamment, en 1879, la théorie de la quantification, sans laquelle Ted Codd serait resté sec), Gottlob Frege, ont fait l’impasse puisque qu’ils n’en avaient strictement rien à faire : en fait, en remontant aux origines, c’est au grand ancêtre, Aristote soi-même qu’il faudrait poser la question, mais c'est un peu tard...

Peut-être un début de réponse :

En se référant à Frege, Quine propose a son tour (cf. méthodes de logique, page 259) la formule qui sous-tend l'induction mathématique (« Nx » se lit « x est un nombre ») :

Nx ←→ (∀α) {(0 ∈ α) . (∀z) [(z ∈ α) → (1+z ∈ α)] ➙ (x ∈ α)}

C'est-à-dire : Pour toute classe α, si 0 appartient à α et si la proposition « si tout nombre z appartient à α alors son successeur appartient aussi à α » alors tout nombre appartient à α.

Autrement dit, être un nombre c'est appartenir à chaque classe à laquelle appartient 0 ainsi que le successeur de chaque membre de la classe.

On a là un certain ordre, puisqu’on parle de successeur. Le problème est quand même qu’on ne s’intéresse ici qu’à 0, à un certain z et son successeur, mais pour les autres nombres, libre à chacun d’ordonner comme il l’entend...

Mais attention, on entre désormais dans la logique du 2e ordre, sur laquelle Codd s’est du reste appuyé dans son article de 1969 (Derivability, Redundancy and Consistency of Relations Stored in Large Data Banks), mais en 1970 il est prudemment revenu à la logique du 1er ordre (A Relational Model of Data for Large Shared Data Banks)...


Je reviendrai un peu plus tard sur le concept de clé primaire...

[escartefigue]

Bon. Je termine ce sujet où je n'ai pas eu les réponses que j'attendais.

Nous en sommes tout de même à la 3ème page d'explications détaillées, de la part de 4 contributeurs différents, qui ont argumenté avec de nombreux exemples concrets ainsi que les bases théoriques étayant ces explications et la référence aux ouvrages sur le sujet . Que faut il de plus ?

[aieeeuuuuu]

Il n'a pas dit qu'il n'avait pas eu de réponse, il a dit que ce n'était pas celle qu'il attendait.

il attendait

Oui, vous avez raison, c'est un bogue de MySQL

Il a eu

Non, vous avez tort et faites fausse route

[fsmrel]

Bonsoir,

Le rôle de la clef primaire ne se cantonne pas uniquement à obtenir d'une part l'unicité de la valeur introduite et d'autre part de ne pas avoir de valeur 'NULL'. Ça, c'est la définition mathématique ou théorique.

Ne varietur ! Le concept de clé primaire est né il y a 45 ans le 6 juin 1970 (cf. A Relational Model of Data for Large Shared Data Banks). Toute autre interprétation de la définition qu’en a donné Codd relève de l’homonymie et de la récupération. Si vous remettez le nez dans la documentation VSAM d’il y a 40 ans (par exemple, le Programmer’s Guide), vous verrez que, dans le cas d’un KSDS de base, on n’y causait que de clés (de type UNIQUE par construction), sans le qualificatif « primaire ». Dans le cas d’un index alternatif (doublons autorisés), on utilise le terme d’« alternate key » pour un ensemble de champs constituant une clé alternative. Cet index alternatif contient l’ensemble des pointeurs (« prime key pointer » ou en abrégé « prime key ») vers le KSDS de base.

Comme le terme « primary key » est devenu populaire, grâce à SQL, il était tentant de rebaptiser ainsi la clé d’un KSDS, et c’est ce qui s’est passé, ça fait à la page, mais même pour un KSDS d’aujourd’hui, le rôle de la clé primaire se limite à garantir l’unicité des valeurs qu’elle contient. Je vous renvoie à la documentation de référence DFSMStvs Administration Guide.

Extrait du glossaire :

primary key. One or more characters within a data record used to identify the data record or control its use. A primary key must be unique.

alternate key. One or more characters within a data record used to identify the data record or to control its use. Unlike the primary key, the alternate key can identify more than one data record. An alternate key is used to build an alternate index or to locate one or more base data records through an alternate index. See also generic key, key, and key field. application owning region.

alternate index. A key-sequenced data set that contains index entries organized by the alternate keys of its associated base data records. It provides an alternate means of locating records in the data component of a cluster on which the alternate index is based.

Un autre document qui est intéressant : VSAM Demystified.

En tout cas, VSAM lui aussi adhère à la définition théorique, et ce n'est qu'un gestionnaire de fichiers, on est loin de la théorie relationnelle...

[fsmrel]

Bonsoir à nouveau,

Le rôle de la 'primary key' est de faire le lien entre la ligne insérée et la valeur de la clef. C'est-à-dire d'indiquer son emplacement sur le disque dur.
Et comme cette clef primaire est aussi un indexe, l'indexe est trié selon l'ordre de la clef.
Donc il ne faut pas mélanger l'ordre des valeurs de la clef dite primaire, et l'ordre de l'insertion des lignes, qui se fait selon la 'rowid'.
De plus, s'il n'y avait pas de 'Primary key', je ne sais pas comment on pourrait retrouver la ligne, juste à partir de sa clef.
Ensuite, chaque indexe fait toujours référence à la 'Primary Key' pour savoir où se trouve la ligne dont la valeur qui est indexée, y fait référence.
ET oui, ne pensez pas que la tuyauterie soit moins importante que l'aspect théorique du sql dont vous semblez en faire l'éloge.
Sans cette tuyauterie, les performances seraient désastreuses !

En vertu de ce qui précède, non la clé primaire n’a rien à voir avec le disque dur, et d’après ce qui suit, elle n’est pas un index : sous le capot, ce sont les valeurs de la clé primaire qui sont hébergées dans un index (lui-même logeant dans un index space), nuance. Quant à la tuyauterie et les performances, ça me connaît ! Pour la partie SQL (qu’à l’instar de Hugh Darwen je surnomme le Askew Wall), je suis plutôt moyen, et je préfère Tutorial D de Date et Darwen.

Pour étayer mes dires, examinons ce qui se passe avec DB2 par exemple. Je reprends ce que j’ai déjà écrit en 2009, en réponse à une question de CinePhil.

Un index DB2 a une structure d'arbre équilibré (balanced tree), cela signifie que toutes les feuilles sont à la même distance de la racine : autrement dit, le temps d’accès est le même pour toutes les feuilles. Ceci est capital quand il s’agit de s’attaquer au réglage des performances, lesquelles seraient aléatoires si l’arbre n’était pas équilibré. Les index sont donc le plus souvent, des arbres B, et dans le cas de DB2 ce sont même des B+ (chaînage des feuilles).

N.B. J’ai écrit que le temps d’accès est le même pour toutes les feuilles : ceci est vrai quand l’index vient d’être créé ou réorganisé, mais au fil du temps il peut y avoir une dégradation perceptible des performances du fait de nombreuses mises à jour, et il est alors préférable de procéder à une réorganisation de l’index. DB2 tient à notre disposition toutes informations utiles à cet effet (stats du catalogue).

Observons maintenant comment DB2 (disons version 5) organise un index. Celui que je dépiaute (appelons-le MEMBRE_X1) est destiné à contenir les valeurs de la clé primaire de la table MEMBRE des membres de DVP : '0012' (valeur de la clé primaire du membre CinePhil), '4089' (valeur de la clé primaire du membre fsmrel), etc.

Supposons que l’index vient d’être défini et vide. Lors du premier INSERT, par exemple du membre SQLpro dans la table MEMBRE, DB2 crée un enregistrement dans le table space d’accueil des images des lignes de la table. Appelons MEMBRE_TS ce table space.





La page P1 contient un certain nombre d’informations « système », dont une petite table appelée id map contenant l’adresse dans P1 de la ligne qui vient d’être insérée. Le rôle de l’id map est capital : les index branchés sur la table ne connaîtront jamais l’adresse exacte d’une ligne dans le table space, mais seulement l’adresse de la page P1 et le numéro du poste (invariable) de cette ligne dans l’id map (1 dans l’exemple). Ainsi, si les enregistrements bougent dans la page, ou sont carrément expulsés vers d’autres pages (« Ôte-toi de là, que je m’y mette ! » dit le gros enregistrement au petit), ces phénomènes n’auront aucun impact sur les valeurs connues par les index (dans l’exemple, la seule information connue est 'P1,1', à savoir l’adresse de la page et le numéro de poste dans l’id map).
Concernant l’index MEMBRE_X1, DB2 réalise les opérations suivantes à l’occasion de ce premier insert :

Création de la page racine et d’une page feuille.
DB2 note dans la page feuille les coordonnées de SQLpro dans MEMBRE_TS, à savoir un record identifier (RID), composé du numéro de la page hébergeant SQLpro, et du numéro qui lui est affecté dans l’id map : 'P1,1'.
DB2 note dans la page racine la plus grande valeur de clé au niveau inférieur et l’adresse de celle-ci (symbolisée par '↑').






Effectuons un deuxième INSERT, par exemple du membre CinePhil. Il reste de la place dans la page P1 du table space MEMBRE_TS, et elle ressemblera à quelque chose comme ceci :





L’id map s’est enrichie d’un élément (poste), portant le numéro 2 et contenant l’adresse dans P1 du nouvel enregistrement.
L’index MEMBRE_X1 est mis à jour en conséquence :
La feuille est enrichie d’un élément permettant d’adresser dans MEMBRE_TS le membre dont la valeur de clé est égale à '0012'. Les éléments dans la feuille sont triés, ce qui fait que l’élément "3500 (RID = 'P1,1')" passe derrière le nouvel arrivant.
La racine ne change pas de contenu, par construction elle conserve toujours la plus grande valeur de clé de la feuille cible.






Les INSERT se suivent et se ressemblent : fsmrel (clé = '4089'), Antoun (clé = '0967'), etc.

Après un certain nombre d’inserts, le table space contiendra de plus en plus de pages et l’index suivra : à un moment, il n’y aura plus assez de place dans la feuille F1 : DB2 créera une feuille supplémentaire F2 et en notera l’adresse dans la racine. Dans la figure ci-dessous, la racine contient deux entrées, une pour F1 et une pour F2. Chaque entrée contient la plus grande valeur de clé pour chaque feuille ainsi que l’adresse de celle-ci.

La séquence des clés doit être strictement respectée, aussi les feuilles seront-elles chaînées à cet effet : ceci favorise les traitements de masse (batch) pour lesquels les requêtes SQL SELECT comportent une clause ORDER BY (attention, au sein des pages de données, P1, etc., les lignes ne sont pas triées). Ce chaînage des feuilles fait que les index DB2 ne sont pas seulement des arbres B, mais B+.





Si l’on soumet l’instruction :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT  NOM 
FROM    MEMBRE  
WHERE   MembreId = '3500'

Alors DB2 lira le contenu de la racine de l’index Membre_X1, et comme '3500' est supérieur à '0967' et inférieur à '4089', ce sera la feuille F2 qui sera lue à son tour. La lecture suivante sera celle de la page de données P1. La consultation de l’id map permettra de retrouver les données du membre '3500'.

Les INSERT continuant, de nouvelles feuilles vont être crées et le nombre d’entrées dans la racine croîtra en conséquence. Arrivera un moment où à son tour elle sera pleine :

C’est alors que DB2 effectue un split de la racine : DB2 répartit l’ensemble de ses éléments dans deux pages, pour moitié entre la première et la deuxième, à l’image de la scissiparité des vers de terre. Mais comme par définition la racine ne comporte qu’une page, ces deux pages deviennent des nœuds intermédiaires et une nouvelle racine est créée, n’adressant plus cette fois-ci les feuilles, mais ces nœuds intermédiaires. Je résume la situation dans le dessin ci-dessous, extrait d’un cours que j’ai monté il y a plus de vingt cinq ans (DB2 V 2) et que j’ai retouché pour les besoins de la cause :





A chaque fois que la racine sera pleine, il y aura split et création d’un nouveau niveau intermédiaire.

J’en resterai là, pour la suite reportez-vous à la discussion à laquelle j’ai fait référence.

[Artemus24]

Salut à tous.

En fait, j'en suis toujours au même point !

Vous avez polué mon sujet, en me faisant bien comprendre que j'étais nul, que je ne savais rien faire, et que je ferais bien d'aller voir ailleurs.

Il y a juste 'aieeeuuuuu', par compassion je suppose, dans son message du '24/07/2015, 10h38' qui semble avoir compris que la réponse :

Non, vous avez tort et faites fausse route

n'est en fait qu'une façon de me rabaisser !

'escartefigue' croit que tous les intervenants ont répondu à mes questions, et avec de nombreux exemples.
Il faudrait changer de paire de lunettes, car les exemples, c'est moi qui les ai donnés.

Ce qui me suprend pour des professionnels comme vous tous, c'est de ne pas capable de répondre à quelque chose d'aussi facile, enfin il me semble.

Je remercie au passage 'fsmrel' d'avoir rafraichi ma mémoire sur le sujet de l'organisation physique des données sous DB2.
Au moins lui, essaye de nous faire part de sa connaissance !

Je ne parle même pas de SQLPRO qui à chaque message qu'il m'adresse, c'est juste pour m'insulter !
A l'avenir, j'espère qu'il tiendra sa promesse de ne plus s'adresser à moi, car il me fait perte mon temps.

Je fais une dernière tentative de mon problème (voir l'exemple que je donne dans mon premier message).
J'ai une table avec une 'primary key' et un 'index'.
Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe et non selon l'ordre de la 'primary key' ?

C'est ça ma question !

@+

[SQLpro]

Salut à tous.

En fait, j'en suis toujours au même point !

Vous avez polué

Pollué prend 2 "l"

mon sujet, en me faisant bien comprendre que j'étais nul

C'est vous qui le dite et je suis pas loin de le penser... Mais surtout vous êtes borné !

, que je ne savais rien faire

La vous extrapolez...

, et que je ferais bien d'aller voir ailleurs.

Vous mentez, personne ne vous a dit cela et les multiples poste en réponse indique bien que l'on tente de vous aider, mais que vous refusez les vérités que l'on vous démontre... En nous cela s’appelle de l'intégrisme !

Il y a juste 'aieeeuuuuu', par compassion je suppose, dans son message du '24/07/2015, 10h38' qui semble avoir compris que la réponse :

n'est en fait qu'une façon de me rabaisser !

Ben voyons !

'escartefigue' croit que tous les intervenants ont répondu à mes questions, et avec de nombreux exemples.
Il faudrait changer de paire de lunettes, car les exemples, c'est moi qui les ai donnés.

N'importe quoi. Vous êtes à la limite de l'insulte compte tenu des énormes efforts déployés par tous et spécialement fmsrel !

Ce qui me suprend

Au passage ajoutez-y un "r" : surprend et non suprend !

pour des professionnels comme vous tous, c'est de ne pas capable

Il manque un verbe : "c'est de ne pas capable" = > "c'est de ne pas être capable"

de répondre à quelque chose d'aussi facile, enfin il me semble.

Pour la n^ieme fois, c'est la réponse qui ne vous plait pas car elle ne va pas dans votre sens. D’où le fait que vous êtes borné et à la limite de l'intégrisme. Relisez TOUTES les réponses apportées. Lisez les livres indiqués...

Je remercie au passage 'fsmrel' d'avoir rafraichi ma mémoire sur le sujet de l'organisation physique des données sous DB2.
Au moins lui, essaye de nous faire part de sa connaissance !

Je ne parle même pas de SQLPRO qui à chaque message qu'il m'adresse, c'est juste pour m'insulter !

Encore une fois, vous affirmez n'importe quoi. Je ne vous ais jamais insulté. Mais j'ai effectivement dit que vos propos était stupide. Preuve que vous ne lisez visiblement pas bien les messages que l'on vous adresse ! Si vous n'êtes même pas capable de faire la différence entre vos propos et votre personne, alors là oui, je pense que vous êtes stupide et là je m'adresse bien à la personne et non plus aux propos !

A

"A" étant à ce niveau une préposition, il prend un accent grave... donc "À"... Deux solutions :

• commencez par mettre l'accent grave à l'aide de ALT GR + touche 0 et tapez la lettre A en majuscule
• appuyez sur ALT et au clavier numérique faite 0192

l'avenir, j'espère qu'il tiendra sa promesse de ne plus s'adresser à moi, car il me fait perte

"perdre" et non perte

mon temps.

Perdu, car lorsque l'on m'attaque personnellement je me défend !

Je fais une dernière tentative de mon problème

Erreur de style : manque un verbe et ce qui va avec... Par exemple "Je fais une dernière tentative pour résoudre mon problème"

(voir l'exemple que je donne dans mon premier message).
J'ai une table avec une 'primary key' et un 'index'.
Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe

Un index ne prend par de "e" en finale.

et non selon l'ordre de la 'primary key' ?

Vidage ce terme est inapproprié. On parle d'extraction lorsque l'on fait un SELECT.

Encore une fois on a répondu à de multiples reprises : il n'y a aucun ordre prédéterminé de restitution des lignes en sortie d'un SELECT, quelque soit le SGBDR, tant que vous ne mettez pas un tri explicite à l'aide de la clause ORDER BY. Comme déjà indiqué, l'ordre de sortie est arbitraire du fait de l'optimisation à l'instant t de la vie des données (indexation, règles sémantiques et statistiques de l'optimiseur, disponibilité des données dans le cache, parallélisme...).

C'est ça ma question !

Et notre réponse indiqué ci avant, sera toujours la même... mais vous ne voulez pas l'entendre !

@+

A +

PS : votre dernière remarque me fais penser aux interviews de Georges Marchais avec Elkabbach... "... vous arrivez avec vos questions, moi j'arrive avec mes réponses !"
http://www.ina.fr/video/I08014459

[fsmrel]

Bonsoir,


A propos de DB2 :

Ma question était de savoir si à un moment donné, dans DB2, un 'select *' se faisait dans l'ordre de stockage et non selon la clef primaire ?

Créons la structure suivante pour la table PSN des personnes :

CREATE TABLE PSN
(
        PSN_ID         INT          NOT NULL
      , MATRICULE      CHAR(8)      NOT NULL
      , COLX           CHAR(32)     NOT NULL
      , COLY           CHAR(40)     NOT NULL
      , COLZ           CHAR(40)     NOT NULL
      , COLT           CHAR(40)     NOT NULL
      , COLU           CHAR(200)    NOT NULL
    , CONSTRAINT PSN_PK PRIMARY KEY (PSN_ID)
) ;

Et les index suivants

CREATE UNIQUE INDEX PSN_XP ON PSN (PSN_ID) CLUSTER ... ;

CREATE UNIQUE INDEX ALT_KEY ON PSN (MATRICULE) ... ;

L’index PSN_XP est cluster. Après avoir chargé la table en conséquence, l’état des lieux est le suivant, comme le montre DB2 Estimator :


Table PSN :




Index PSN_XP utilisé pour loger PSN_ID (colonne clé primaire) :





Index ALT_KEY utilisé pour loger la colonne MATRICULE :





Exécutons la requête suivante :

SELECT * FROM PSN ;

=>






Il y a un table scan, donc pas d’utilisation d’index.

Le cluster ratio est égal à 100, l’ordre de rangement des lignes de la table PSN dans le table space correspond pile-poil à la séquence PSN_ID de l’index cluster.

Parcourons le résultat à l’aide d’un curseur : je n’ai pas de DB2 for z/OS sous la main mais je sais que les lignes seront délivrées dans l’ordre des IDN_PSN, puisqu’elles sont rangées dans le table space selon la séquence définie par l’index cluster. Maintenant, les équipes de développement vont reprendre ma requête, qui va peut-être se retrouver dans une centaine de programmes, lesquels obtiendront des résultats identiques au mien.

Mais, après six mois de production, il est décidé que l’index cluster sera désormais l’autre index, ALT_kEY : la production effectue un UNLOAD/RELOAD et le rangement des lignes de la table dans le table space ne sera plus du tout le même (en passant, le cluster ratio de l’index « primaire » PSN_XP en aura pris un coup) :





Là encore, la requête SELECT * FROM PSN ; est traitée selon le régime du table scan :





Il y a un table scan, donc pas d’utilisation d’index. Mais cette fois-ci, les lignes seront délivrées aux programmes dans l’ordre des matricules : ce changement risque de provoquer des réactions véhémentes de la part de certains utilisateurs...

Moralité : pour s'assurer de la stabilité des résultats et de la confiance des utilisateurs il fallait dès le départ coder :

SELECT * FROM PSN ORDER BY PSN_ID ;

quand on fait un "select * from test" par exemple, je m'attends à obtenir un 'full scan' de ma table triée selon l'ordre de ma "primary key' et non sur un autre indexe. C'est pas logique !

En vertu de ce qui précède, au moins avec DB2 c’est logique, puisque c'est « cluster dépendant ».

Il est probable que ce qui vaut pour DB2 vaut pour d’autres SGBD...

Il y a manifestement un consensus ès matière, et ce n’est pas le sage al1_24 qui nous démentira (cf. « Comment les opérateurs ensemblistes trient-ils ? »).

Un indexe (secondary indexes) fait toujours référence à l'indexe cluster afin d'obtenir une autre façon d'accéder à tes lignes

Disons que cela vaut pour les KSDS (VSAM), un index alternatif fait effectivement référence au cluster de base. Cela vaut sans doute pour certains SGBD. Mais avec DB2, que nenni ! quel que soit l’index, cluster ou pas, les feuilles adressent directement et seulement le table space hébergeant la table concernée, aucun index ne fait référence à un autre index. Ainsi en va-t-il pour les index PSN_XP et ALT_KEY. Ceci permet de réduire de façon très sensible le nombre entrées/sorties pour accéder aux données.

quand on crée un indexe, celui-ci fait toujours référence à la primary key

Cf. ce que je viens d’écrire. Avec DB2, quel que soit l’index, cluster ou pas, les feuilles n’adressent aucun autre index, mais directement et seulement le table space hébergeant la table concernée.

Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe et non selon l'ordre de la 'primary key' ?

Au moins pour DB2 for z/OS, mes réponses devraient vous convenir. Pour les autres SGBD, je n’ai pas soulevé le capot, j’en resterai à la proposition 7 du Tractatus de Wittgenstein :

« Sur ce dont on ne peut parler, il faut garder le silence. » ( Wovon man nicht sprechen kann, darüber muß man schweigen).

[escartefigue]

Salut à tous.

En fait, j'en suis toujours au même point !

Vous avez polué mon sujet, en me faisant bien comprendre que j'étais nul, que je ne savais rien faire, et que je ferais bien d'aller voir ailleurs.

Il y a juste 'aieeeuuuuu', par compassion je suppose, dans son message du '24/07/2015, 10h38' qui semble avoir compris que la réponse :

n'est en fait qu'une façon de me rabaisser !

'escartefigue' croit que tous les intervenants ont répondu à mes questions, et avec de nombreux exemples.
Il faudrait changer de paire de lunettes, car les exemples, c'est moi qui les ai donnés.

Ce qui me suprend pour des professionnels comme vous tous, c'est de ne pas capable de répondre à quelque chose d'aussi facile, enfin il me semble.

Je remercie au passage 'fsmrel' d'avoir rafraichi ma mémoire sur le sujet de l'organisation physique des données sous DB2.
Au moins lui, essaye de nous faire part de sa connaissance !

Je ne parle même pas de SQLPRO qui à chaque message qu'il m'adresse, c'est juste pour m'insulter !
A l'avenir, j'espère qu'il tiendra sa promesse de ne plus s'adresser à moi, car il me fait perte mon temps.

Je fais une dernière tentative de mon problème (voir l'exemple que je donne dans mon premier message).
J'ai une table avec une 'primary key' et un 'index'.
Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe et non selon l'ordre de la 'primary key' ?

C'est ça ma question !

@+

Pour ma part, je fais également une ultime tentative pour vous convaincre :

- Non, je ne cherche nullement à rabaisser qui que ce soit, je suppose qu'il en va de même pour les autres intervenants
- reprenez le post de fsmrel que vous pourrez remercier pour son abnégation, tout y est, les principes, les exemples, les schémas
- relisez sereinement l'ensemble des réponses sans a priori, oubliez vos préjugés et analysez la documentation, puis, expérimentez ce qui vous a été proposé et analysez les résultats

[Antoun]

Encore une fois vous supposez des choses qui n'existent pas; Ni maintenant ni avant les SGBDR restituait les données dans un ordre quelconque prédéfini, par exemple l'ordre de stockage.

Cette question ne s'adressait pas à vous, mais à fsmrel. Et ce n'est pas parce que vous êtes encore très jeune qu'il faut croire que les bases de données sont nées en même temps que vous.

Encore une fois, tu supposes sans aucun élément des choses fausses

[SQLpro]

...En vertu de ce qui précède, au moins avec DB2 c’est logique, puisque c'est « cluster dépendant ».

Il est probable que ce qui vaut pour DB2 vaut pour d’autres SGBD...

Pour SQL Server c'est même pire : cluster + cache dépendant + partition dépendant. Les données sont servies dans l'ordre des partitions délivrées pour chaque élément du cluster et pages en cache en premier...

A +

[Artemus24]

Salut à tous.

Exécutons la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM PSN ;

Il y a un table scan, donc pas d’utilisation d’index.

Oui, tout à fait car c'est le fonctionnement normal en DB2 que je connais ! Je m'attendais à la même chose sous mysql. Or ce n'est pas le cas.
Comme c'est un autre SGBDR, il se peut que les choix sont différents. Et justement, c'est ce que j'essaye d'étudier.

je sais que les lignes seront délivrées dans l’ordre des IDN_PSN, puisqu’elles sont rangées dans le table space selon la séquence définie par l’index cluster.

Comme je l'ai constaté sous MySql (voir mon exemple), il va chercher l'indexe et non la primary key. Sur ce point, ce n'est pas à l'identique de DB2.
Pire encore, quand j'ajoute une troisième colonne, aussi avec indexe, il me donne cette fois-ci l'ordre correcte. C'est un comportement plus qu'étrange de la part de MySql.

Moralité : pour s'assurer de la stabilité des résultats et de la confiance des utilisateurs il fallait dès le départ coder :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM PSN ORDER BY PSN_ID ;

J'en conviens, mais ce n'est pas le "comment faire" qui m'intéresse ici mais plutôt le "pourquoi".
Pourquoi le 'table scan' sous MySql ne fonctionne pas comme sous DB2 ?
Pourquoi MySql fait ce choix étrange de passer par un indexe alors que cela devrait être par 'la primary key' ? Je considère qu'il y a un bug de fonctionnement.

Il y a manifestement un consensus ès matière, et ce n’est pas le sage al1_24 qui nous démentira (cf. « Comment les opérateurs ensemblistes trient-ils ? »).

Merci pour le lien, mais ce n'est pas exactement le même problème.
J'ai surtout l'impression de ne pas avoir été bien compris. Peut-être est-ce dû au titre de mon sujet qui laisse penser que j'ai un problème de tri avec ma table.
Ce n'est pas un problème de tri mais juste un problème de compréhension concernant le choix fait par MySql.

Disons que cela vaut pour les KSDS (VSAM), un index alternatif fait effectivement référence au cluster de base.

Vous avez raison, j'ai dû faire une confusion avec KSDS. Désolé pour cette méprise.

J'ai une question subsidiaire concernant DB2.
Pourquoi en DB2, quand on fait l'usage d'une 'Primary Key' ('PSN_PK'), faut-il toujours créer sur cette 'primary key' un 'unique index' ('PSN_XP') ?

En MySql, il n'est pas nécessaire de faire cette déclaration car cela se fait automatiquement.
Donc la 'primary key' est aussi associé à un 'unique index'. Et c'est cet indexe qui va ordonner les lignes dans le tablespace.

Parcourons le résultat à l’aide d’un curseur : je n’ai pas de DB2 for z/OS sous la main mais je sais que les lignes seront délivrées dans l’ordre des IDN_PSN, puisqu’elles sont rangées dans le table space selon la séquence définie par l’index cluster.

C'est bien ce que j'avais compris, en tout cas sous DB2 et vous me confirmez ce que je savais déjà. Alors pourquoi me faire croire (pas vous 'fsmrel') qu'il en serait autrement, même sous DB2 ?
Ici, je ne parle pas des utilisateurs, mais bien de l'ordre selon l'index cluster. Dois-je comprendre que cela ne fonctionne pas ainsi sous MySql ?
Et qu'il faut chercher la cause ailleurs, due au multiprocessing, aux buffers, aux partitionnements, et je ne sais quoi d'autre.

En tout cas merci 'fmsrel' d'avoir pris le temps de me répondre !

Encore une fois on a répondu à de multiples reprises : il n'y a aucun ordre prédéterminé de restitution des lignes en sortie d'un SELECT, quelque soit le SGBDR, tant que vous ne mettez pas un tri explicite à l'aide de la clause ORDER BY. Comme déjà indiqué, l'ordre de sortie est arbitraire du fait de l'optimisation à l'instant t de la vie des données (indexation, règles sémantiques et statistiques de l'optimiseur, disponibilité des données dans le cache, parallélisme...).

Quand on donne une réponse, il faudrait un tant soi peu qu'elle corresponde à la question posée, non ? Je ne m'intéresse pas à l'ordre de restitution des lignes de mon 'select *' !
Je cherche à comprendre pourquoi MySql préfère utiliser l'index de ma seconde colonne, plutôt que d'utiliser la 'primary key' ?

En gros, vous avez répondu à 'comment il faut faire', et non à 'pourquoi il procède ainsi' !
Sinon, pourquoi aurai-je mis des 'explain' pour indiquer les résultats de MySql ? Juste pour faire joli peut-être ?

@ escartefigue : j'ai fait plusieurs tentative pour vous expliquer mon problème et à chaque fois, vous revenez sur vos idées.
Comme si la solution de mon problème était un vulgaire tri que j'avais oubli de faire. Je le répète encore une fois, mon problème n'est pas comment trier mon résultat avec un "select *".
Mon problème est de comprendre pourquoi MySql utilise l'index sur ma seconde colonne alors qu'il aurait dû utiliser la 'primary key' pour faire un 'full scan' de ma table.

relisez sereinement l'ensemble des réponses sans a priori, oubliez vos préjugés et analysez la documentation, puis, expérimentez ce qui vous a été proposé et analysez les résultats

Et qu'est-ce que vous croyez que je fasse depuis le début ? Vous n'avez rien compris de mon problème. Je le voie fort bien quand je vous remercie et que vous prenez la mouche.
Ce que je cherche à faire par la suite, c'est influencer le choix de MySql pour obtenir le bon résultat. Mais ça, c'est complètement secondaire vis-à-vis de mon problème de compréhension.
Et pour votre gouverne, les tests que j'ai entrepris sur les index sont terminés. Je n'ai aucun autre problème de compréhension à ce sujet, sauf celui qui est à l'origine de ce sujet.

Encore une fois, tu supposes sans aucun élément des choses fausses

Je ne suppose rien. C'est juste une interrogation ! J'ai émis un doute quand 'fmsrel' a donné son exemple. Car cela ne correspondait pas à mes connaissances que j'ai sur DB2. Ensuite 'fmsrel' a confirmé ses propos. Et l'ambiguïté s'est dissipée.

@+

[aieeeuuuuu]

Je cherche à comprendre pourquoi MySql préfère utiliser l'index de ma seconde colonne, plutôt que d'utiliser la 'primary key' ?

C'est bien le moteur qui fait ce choix. Comme vous avez pu le remarquer, le choix n'est pas le même avec InnoDB qu'avec MyISAM.

Cependant, je vous ai déjà expliqué que le moteur avez deux possibilités équivalentes (la clef primaire ou votre index sur col2), il en choisit une. Pour comprendre précisément pourquoi ce choix plutôt qu'un autre, il faudrait connaitre parfaitement le moteur, ce qui est loin d'être mon cas.
Mais ce n'est pas un bogue. Je dirai même que le choix de l'index idx2 est plutôt pertinent.

Et comme vous reclamez des exemples, reprennons votre requete et remplaçons dans votre l'étoile par le nom des deux colonnes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT col1,col2 from test

Comparons le nombre de lectures engendrées par la requêtes dans 4 situation différents (en suivant l'évolution du compteur show session status like 'Innodb_buffer_pool_read_requests';). Avec/sans index sur la colonne2. Avec/Sans colonne supplémentaires contenant de longs textes

reprenons votre jeu de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test`
(
  `col1`  int unsigned NOT NULL,
  `col2`  int unsigned NOT NULL,
 
  PRIMARY KEY (`col1`)
) ENGINE= InnoDB 
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ;
 
insert into `test` (`col1`,`col2`) values
(1, 9),(2, 8),(3, 7),(4, 6),(5, 5),(6, 4),(7, 3),(8, 2),(9, 1);

1/ sans index
-> 7 lectures

2/ avec un index sur la colonne 2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create UNIQUE INDEX `idx2` USING BTREE ON `test` (`col2`);

--> 7 lectures

3/ Avec une colonne supplémentaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
ALTER TABLE test ADD dummy VARCHAR(8000) ;
UPDATE test SET dummy = REPEAT(uuid(),200 ) ;

--> 7 lectures

4/ toujours avec la colonne supplémentaire, mais en supprimant l'index :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

drop index `idx2`  ON `test` ;

--> 12 lectures

Si on synthétise :

	Avec index	Sans index
Sans colonne suppl.	7	7
Avec colonne supplémentaire	7	12

On voit que dans le cas de test initial (sans colonne supplémentaire), le nombre de pages lues est le même, que l'on utilise l'index ou pas.
On voit également qu'avec la colonne supplémentaire, l'utilisation de l'index pour répondre à la requête est préférable. En effet, celui-ci ne contient que les colonnes nécessaires pour la requête et n'est pas surchargé par la troisième colonne.

[escartefigue]

Et cet argumentaire fort bien détaillé par aieeuuuu a déja été présenté par les différents contributeurs, de façon certes moins détaillée mais avec les mêmes arguments

Reprenez par exemple mon poste #9 page 1 (on est déjà a la page 3, comme le temps passe) et vous aurez tout en bas la même explication

Ce principe de l'index only n'est pas une particularité de MySQL mais existe bel et bien pour d'autres bases de données - probablement toutes mais je préfère rester prudent -
et à commencer par DB2 for Z/OS que vous pensez connaitre pour l'avoir l'administré

[Artemus24]

salut aieeeuuuuu.

Merci pour ton test.

C'est bien le moteur qui fait ce choix. Comme vous avez pu le remarquer, le choix n'est pas le même avec InnoDB qu'avec MyISAM.

J'ai bien compris que c'est l'optimiseur qui fait ce choix.
La différence de comportement entre 'InnoDB' et 'MyIsam', je ne me l'explique pas non plus.

Cependant, je vous ai déjà expliqué que le moteur avez deux possibilités équivalentes (la clef primaire ou votre index sur col2), il en choisit une. Pour comprendre précisément pourquoi ce choix plutôt qu'un autre, il faudrait connaitre parfaitement le moteur, ce qui est loin d'être mon cas.

C'est ce que je demande depuis le début de mon sujet. Tant pis si tu ne peux pas me répondre.

Est-ce qu'il y a une autre façon d'influencer ce choix, autrement qu'en faisant un "select * from test order by col1" ?

J'ai refait ton test et je trouve exactement la même chose dans les quatre cas. Est-ce que j'aurai mal fait mon test ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test` (
        `col1`  int UNSIGNED NOT NULL,
        `col2`  int UNSIGNED NOT NULL,
        PRIMARY KEY (`col1`)
)       ENGINE=InnoDB
        DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
        ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`col1`,`col2`) values (1, 9),(2, 8),(3, 7),(4, 6),(5, 5),(6, 4),(7, 3),(8, 2),(9, 1)
--------------
 
--------------
show session status like 'Innodb_buffer_pool_read_requests'
--------------
 
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_read_requests | 68009 |
+----------------------------------+-------+
--------------
select VARIABLE_VALUE into @a1 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+
--------------
select VARIABLE_VALUE into @b1 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b1 - @a1 AS 'nbre' into @z1
--------------
 
--------------
create UNIQUE INDEX `idx2` USING BTREE ON `test` (`col2`)
--------------
 
--------------
select VARIABLE_VALUE into @a2 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+
--------------
select VARIABLE_VALUE into @b2 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b2 - @a2 AS 'nbre' into @z2
--------------
 
--------------
ALTER TABLE test ADD dummy VARCHAR(65524)
--------------
 
--------------
UPDATE `test` SET dummy = REPEAT(uuid(),2000)
--------------
 
--------------
select VARIABLE_VALUE into @a3 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+
--------------
select VARIABLE_VALUE into @b3 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b3 - @a3 AS 'nbre' into @z3
--------------
 
--------------
ALTER TABLE `test` drop index `idx2`
--------------
 
--------------
select VARIABLE_VALUE into @a4 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+
--------------
select VARIABLE_VALUE into @b4 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b4 - @a4 AS 'nbre' into @z4
--------------
 
--------------
select @z1 as 'sans index, sans col',
       @z2 as 'avec index, sans col',
       @z3 as 'avec index, avec col',
       @z4 as 'sans index, avec col'
--------------
 
+----------------------+----------------------+----------------------+----------------------+
| sans index, sans col | avec index, sans col | avec index, avec col | sans index, avec col |
+----------------------+----------------------+----------------------+----------------------+
|                   11 |                   11 |                   11 |                   11 |
+----------------------+----------------------+----------------------+----------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

[aieeeuuuuu]

'ai refait ton test et je trouve exactement la même chose dans les quatre cas. Est-ce que j'aurai mal fait mon test ?

La colonne que vous avez ajoutée contient trop de données... du coup, son contenu est déplacé hors des pages de la table :

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage

baissez la taille pour la colonne "dummy" et relancez le test.

[Artemus24]

Salut aieeeuuuuu.

J'ai mis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
--------------
ALTER TABLE test ADD dummy VARCHAR(8000)
--------------
 
--------------
UPDATE `test` SET dummy = REPEAT(uuid(),200)

Et j'ai refais le test. Voici le nouveau résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
+----------------------+----------------------+----------------------+----------------------+
| sans index, sans col | avec index, sans col | avec index, avec col | sans index, avec col |
+----------------------+----------------------+----------------------+----------------------+
|                   11 |                   11 |                   11 |                   16 |
+----------------------+----------------------+----------------------+----------------------+

Comment se fait-il que vous trouvez '7' alors que dans mon exemple, j'ai '11' ?
Par contre dans nos deux exemples, l'augmentation est la même : 5.

La colonne que vous avez ajoutée contient trop de données... du coup, son contenu est déplacé hors des pages de la table

Et comment puis-je voir ce comportement dans mon environnement ?

@+

[pier.antoine]

Bonjour

De ce que j'ai compris de votre demande, c'est :
"Comment Mysql utilise les index pour afficher l'ordre des données, car selon le moteur utilisé, les résultats sont différents?"

Pour moi on vous a répondu:
Les index ne servent pas à afficher l'odre des données, il ne faut donc pas aller dans cette voie.

Ai-je bien résumé?

Pierre

[Artemus24]

Salut pier.antoine.

Merci de ta participation.

Non, vous aussi, vous n'avez rien compris de mon problème.
Je cherche à comprendre pourquoi MySql utilise un index, alors que je demande juste un vidage de ma table (un full scan), sans autre critère.
Je m'attendais à obtenir les lignes selon l'ordre de la 'primary key', mais en fait MySql utilise un index (et ça, je ne sais pas pourquoi) et me donne un autre ordre.

Je répète encore une fois, je ne cherche pas à faire quelque chose de particulier, je cherche juste à comprendre sur cet exemple.

La seule réponse qui sort du lot est de forcer MySql à sélectionner la 'primary key' par l'ajout de 'order by clef'. J'ai compris cette raison.
SQLPRO me l'a assez répété que l'ordre n'est pas garantie et que c'est la seule façon de procéder pour garantir l'ordre.
Mais je ne me trouve pas dans ce contexte où l'ordre serait arbitraire, si je dois reprendre les propos de SQLPRO.
J'ai un exemple extrêmement simple où l'ordre est toujours le même car la table est petite en volumétrie.
Et rien ne viendrait perturber l'ordre de ma table: partitionnement, thread multiple, ou buffer trop petit ou je ne sais quoi d'autre.

Donc pourquoi MySql utilise un index dans le contexte de cet exemple ?
si je change de contexte, MySql va avoir un autre comportement.
Sauf que les autres comportement, je les comprends, sauf celui-ci !

@+

[SQLpro]

Salut pier.antoine.

Merci de ta participation.

Non, vous aussi, vous n'avez rien compris de mon problème.
Je cherche à comprendre pourquoi MySql utilise un index, alors que je demande juste un vidage de ma table (un full scan), sans autre critère.
Je m'attendais à obtenir les lignes selon l'ordre de la 'primary key', mais en fait MySql utilise un index (et ça, je ne sais pas pourquoi) et me donne un autre ordre.
...

Relisez votre premier post dont j'ai recopié les principaux extraits ici :

Salut à tous.

J'ai un problème de compréhension au sujets des indexes.

J'ai fait le même test, et je n'obtiens pas le même résultat. Voici le test :
...
Rien de bien compliqué ! Et voici les différences :

Avec 'engine=MyIsam' ou 'engine=Memory', j'obtiens le bon résultat. Ma table est tri selon la 'PRIMARY KEY' (col1).
...
Et avec 'engine=InnoDB', j'obtiens le mauvais résultat. Ma table est tri sur l'indexe (col2).
...
L'ordre du tri n'est pas le même ! Tout ce passe comme si l'indexe 'idx2' dans 'engine=InnoDB' venait influencer l'ordre des lignes.

Qu'est-ce qui vient perturber cet ordre ?
Autrement dit, est-ce que j'ai dans 'my.ini' un mauvais paramétrage sur 'engine=InnoDb' ?

Merci.
@+

Vous parlez bien du tri du résultat, de l'ordre des lignes et non pas des index. Si vous aviez voulu parlé de l'utilisation des index il aurait fallu commencer par étudier les plans de requête. L'utilisation d'un index ne restitue pas forcément les données dans l'ordre de tri de l'index, notamment lorsqu'il existe un partitionnement ou que le moteur fait du parallélisme ce qu nous vous avons déjà mentionné !

Bref, quand on pose mal les questions, il est normal que l'on obtienne des réponses vraies qui ne vous satisfassent pas !

A +