Rownum : confusion

Bonjour a tous,

Je me lance nouvellement sur orcale et j'ai une confusion au niveau du rownum.

Je comprend bien le principe de l'interet du between, ect.

Mais partout ou j'ai cherché (ce forum, les tutoriaux, google), je constate que le rownum marche soit directement quand <= x et avec un beween quand on ne désire pas les premiers enregistrements.

Pourtant j'ai eu l'occassion de tester une requete :

Code:

---

1 2 3 4 5

SELECT DISTINCT IDCMDE FROM COMMANDE WHERE IDCLIENT = '0429005' AND ROWNUM <= 200 ORDER BY IDCMDE ASC

---

==> 21 résultats

et celle ci

Code:

---

1 2 3 4 5 6 7 8 9

SELECT * FROM (SELECT A.*, ROWNUM AS RNUM FROM (SELECT DISTINCT IDCMDE FROM COMMANDE WHERE IDCLIENT = '0429005' ORDER BY IDCMDE ASC) A WHERE ROWNUM <= 200) WHERE RNUM >= 0

---

==> 24 résultats

Quelqu'un peut-il m'expliquer pourquoi ?
Est ce uniquement à cause du order by

Merci d'avance a qui voudra bien aider cette pauvre ame en perdition

Indentation des requêtes effectuée par :http://www.wangz.net/cgi-bin/pp/gsql.../sqlformat.tpl

Il est FORTEMENT possible que je me trompe mais je dirai que les 3 commandes de la 1ère requête ont un rownum > 200.
Vous pouvez afficher le rownum et regarder ce qu'il en est réellement...

Ah oui interressant le mélange rownum/distinct... j'ai testé chez moi aussi j'ai un comportement tres bizarre aussi.... :aie:

Le problème c'est que lorsqu'on met le rownum dans un select, le distinct ne se comporte pas pareil donc on ne vois pas ce qui se passe :aie:

Code:

---

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

create table TEST (CHAMP VARCHAR2(16)) Table created   insert into TEST values ('DD') 1 row inserted   insert into TEST values ('CC') 1 row inserted   insert into TEST values ('DD') 1 row inserted   insert into TEST values ('DD') 1 row inserted   insert into TEST values ('CC') 1 row inserted   insert into TEST values ('BB') 1 row inserted   insert into TEST values ('BB') 1 row inserted   insert into TEST values ('AA') 1 row inserted   insert into TEST values ('AA') 1 row inserted   insert into TEST values ('UU') 1 row inserted   insert into TEST values ('VV') 1 row inserted   -- j'ai donc 11 valeurs dans ma tabe et 6 valeurs distinctes   select distinct CHAMP from TEST CHAMP ---------------- AA BB CC DD UU VV 6 rows selected     select distinct CHAMP from TEST where rownum <= 4 CHAMP ---------------- CC DD 2 rows selected

---

étonnant non ? ;)

En étditant le plan d'exécution d'une telle requete, on comprend ce qui se passe:

Code:

---

1 2 3 4

SELECT STATEMENT Optimizer Mode=CHOOSE SORT UNIQUE COUNT STOPKEY TABLE ACCESS FULL TEST

---

Le distinct est l'équivalent d'un "group by" c'est à dire qu'il fait d'abord un tri, or le rownum est calculé avant le tri, dans mon exemple, l'acces full s'arrete au 4ieme enregistrement puis seulement est fait le distinct mais il n'y a que 2 enregistrement distinct à ce moment là...

maintenant si je fais:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

SELECT * FROM (SELECT ROWNUM AS rn, t.* FROM (SELECT DISTINCT champ FROM TEST) t) WHERE rn <= 4 RN CHAMP ---------- ---------------- 1 AA 2 BB 3 CC 4 DD 4 rows selected

---

le plan:

Code:

---

1 2 3 4 5 6

SELECT STATEMENT Optimizer Mode=CHOOSE VIEW COUNT VIEW SORT UNIQUE TABLE ACCESS FULL TEST

---

Dans ce cas le tri est fait d'abord sur la totalité de la table (pas de COUNT-STOPKEY, donc toutes les lignes sont prises en compte), puis une numérotation est faite, puis un simple "where" est effectué sur cette numérotation....

Moralité:
ROWNUM = :aie: :aie: :aie: :aie: :aie:

oui je sais, c'est une idée fixe chez moi... ;)

desole je n'avais aps eu de notification par email lol.

Donc on est bien d'accord, le rownum n'est pas fiable !
Bizarre que 99.99% des dev l'utilisent dans ce cas.

Quelqu'un d'autres a des arguments.

Citation:

---

Envoyé par Tchinkatchuk

Donc on est bien d'accord, le rownum n'est pas fiable !
Bizarre que 99.99% des dev l'utilisent dans ce cas.

---

Si, il est fiable, à condition de l'utiliser comme il doit se doit ;)

Faire un select distinct, revient à faire un select group by, or le rownum est calculé avant la clause group by.
=> il te ramène 4 enregistrements
=> parmi ces 4 enregistrements il te fait le distinct
=> c'est donc pour cela que tu n'as pas le nombre d'enregistrement que tu pensais obtenir

Donc si tu veux avoir le résultat attendu, il faut faire :

Code:

---

1 2 3

select champ from (select distinct champ from matable) where rownum <= 4;

---

et là tu auras bien 4 valeurs. ;)

Citation:

---

Le distinct est l'équivalent d'un "group by" c'est à dire qu'il fait d'abord un tri, or le rownum est calculé avant le tri, dans mon exemple, l'acces full s'arrete au 4ieme enregistrement puis seulement est fait le distinct mais il n'y a que 2 enregistrement distinct à ce moment là...

---

Le distinct tout comme le group by est évalué après la sélection des données. Donc le rownum intervient forcement avant le group by, d'ou la présence de 2 résultats au final.

De plus, rien n'indique (dans la doc ou dans la norme) que le distinct ou le group by implique un tri mais plutôt un regroupement ou une distinction. Si l'on veut trier, il faut utiliser ORDER BY.

Un petit exemple (matricule_pk est la primary key, sur id_mat) :

Code:

---

select /*+ INDEX_DESC(matricule matricule_pk) */ distinct id_mat from matricule

---

Code:

---

1 2 3 4 5

VND_SVV888 VND_SVV665 VND_SVV658 VND_SVV640 ...

---

Il y a plein d'autres cas possible pour que le distinct ou le group by ne retourne pas un résultat trié. Par exemple, quand on travaille avec des tables partitionnée ou que le parallélisme est utilisée et qu'il y a une condition unique qui traine (index / primary key / clé de partitionning)

ok, merci pour l'explication.

Comme vous avez pu le remarquer, je suis nouveau sur Oracle et meme si je connais un peu ieux postgre, la compréhension de chose simple comme ceci n'est aps souvent expliqué dans les bouquins.

Merci beaucoup et bonne soirée. Vous l'avez mérité lol

En pratique, le rownum a 3 utilisations :

- soit pour un test rapide (on ne veut voir que les x premieres lignes, par exemple dans un debuggage)

Code:

---

select * from ma_table where rownum<100

---

- soit pour une top-n analysis
la requête doit travailler sur les n plus gros enregistrements. Ca se fait conjointement avec un order by dans une vue imbriquée

Code:

---

1 2 3

select * from (select salarie, salaire from ma_table order by salaire desc) where rownum<3

---

va retourner les 2 plus gros salaires de l'entreprise.
On récupère la liste des salariés, on tri suivant le salaire en descendant
Dans cette liste triée, on ne prend que les 2 premiers enregistrements

- soit en tant que fonction analytique
la les possibilités sont diverses...

Code:

---

select departement, salarie, salaire, rownum over (partition departement order by salaire desc) from ma_table order by departement, salarie

---

va retourner le département, le nom du salarié et pour chaque salarié le numéro d'enregistrement à l'intérieur du département

Code:

---

1 2 3 4 5 6 7 8

Département Salarié Salaire rownum IT A 1000 2 IT B 2000 1 IT C 500 4 IT F 1000 3 XX D 800 1 XX E 600 2

---

Si on avait mis rank à la place de rownum, on aurait (dans le même ordre)
2-1-4-2-1-2 (valeur identique pour A et F car le même salaire - le 3 est "sauté")
et avec dense_rank, comme avec rank mais le 3 n'est pas "sauté" : 2-1-3-2-1-2

Citation:

---

Envoyé par Hugues_78

De plus, rien n'indique (dans la doc ou dans la norme) que le distinct ou le group by implique un tri mais plutôt un regroupement ou une distinction. Si l'on veut trier, il faut utiliser ORDER BY.

---

Quand je parlais de tri, c'était pour expliquer le fonctionnement interne d'oracle. Pour faire un groupement ou une unicité il passe forcément par un tri SORT-GROUP-BY ou SORT-UNIQUE. Cependant tu as raison de préciser que ce n'est en aucun cas une garantie d'affichage.

Citation:

---

Envoyé par Hugues_78

....

Code:

---

select departement, salarie, salaire, rownum over (partition departement order by salaire desc) from ma_table order by departement, salarie

---

....

---

8O

t'es sur que tu confond pas avec row_number() ? ou alors je vois pas en quelle version ça fonctionne.... :koi:

Citation:

---

Envoyé par Tchinkatchuk

...
Donc on est bien d'accord, le rownum n'est pas fiable !
...

---

C'est pas ce que j'ai voulu dire, il est "fiable" si on s'en sert bien, mais par contre il est tres facile de se faire piéger. Son utilisation est dangeureuse car ce n'est pas une instruction SQL à proprement parler mais une instruction système propre à oracle. Il t'autorise à faire une utilisation sans clause de tri, et donc introduit une part d'incertitude.
L'instruction sql fiable, c'est row_number() over ([ partition by machin] order by truc). Là le order-by est obligatoire, donc c'est plus stable

Oui, je comprend bien. Par fibale, je voulais dire qu'il fallait faire tres attention a la facon de l'utiliser.

Je vous remercie tous, vous avez eclairé ma lanterne pour de longues années encore ;-)