Discussion :

[Tchinkatchuk]

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 :

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

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 :

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

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

[Magnus]

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...

[remi4444]

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

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

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
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 :

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

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 :

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

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 :

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

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 =

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

[Tchinkatchuk]

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.

[plaineR]

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 :

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

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

et là tu auras bien 4 valeurs.

[Hugues_78]

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 :

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

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

Code :

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

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)

[Tchinkatchuk]

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

[Hugues_78]

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 :

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

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 :

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

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 :

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

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 :

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

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

[remi4444]

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.

....

Code :

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

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

....

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

[remi4444]

...
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

[Tchinkatchuk]

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 ;-)