Probleme select DISTINCT [Résolu]

[waouni]

Bonjour tout le monde,

J’ai cette requête :

Code :

1
2
3
SELECT DISTINCT champs1,champs2 
 FROM TABLE 
ORDER BY champs2 ;

champs2 est le même sur toutes les lignes, mais le DISTINCT modifie le tri, je veux savoir sur la base de quelle logique ce changement de tri?

PS : Dans mon exemple, et vu que j’ai pas de doublon, la requête sans/avec DISTINCT est censée renvoyer le même résultat mais c’est pas le cas.
Avous-vous une idée le DISTINCT fait quoi à part l’élimination des doublons ? d’autres Order by peut être ? Où alors avez-vous une idée sur l'algorithme de "DISTINCT" ?

Merci

[Rei Ichido]

Envoyé par waouni

Bonjour tout le monde,

J’ai cette requête :


select DISTINCT champs1,champs2 from table order by champs2 ;

champs2 est le même sur toutes les lignes, mais le DISTINCT modifie le tri, je veux savoir sur la base de quelle logique ce changement de tri?

Si "champs2" est le même sur toutes les lignes, le "order by champs2" ne sert à rien ; il vient demander du travail en plus sans garantir en rien l'ordre d'arrivée.
Donc oui, avec un DISTINCT il y a des opérations en plus, ce qui peut changer l'ordre.

[waouni]

Justement, mon abjectif c'est que de savoir la logique de DISTINCT qui fait bouger le tri.

[Rei Ichido]

Mais ... il n'y a pas de tri possible entre des valeurs identiques !
Donc la logique, c'est qu'il met les lignes dans l'ordre où elles lui arrivent. Et forcément, si on lui demande de faire des opérations en plus, elles peuvent ne pas arriver dans le même ordre.

Si par contre vous lui demandez order by champs1, le DISTINCT garantissant l'unicité, il y aura un ordre unique, et ce sera alors déterministe.

[mnitu]

Envoyé par waouni

Bonjour tout le monde,

J’ai cette requête :


select DISTINCT champs1,champs2 from table order by champs2 ;

champs2 est le même sur toutes les lignes, mais le DISTINCT modifie le tri, ...

C'est faux! Le tri se fait après le distinct donc peut importe ce que le distinct fait!

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
 
SQL> EXPLAIN plan FOR
  2  
  2  SELECT DISTINCT department_id, first_name
  3    FROM hr.employees
  4  ORDER BY first_name
  5  /
 
Explained
 
SQL> SELECT * FROM TABLE(dbms_xplan.display)
  2  /
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1865327274
--------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   107 |  1070 |     5  (40)| 00:00:01
|   1 |  SORT ORDER BY      |           |   107 |  1070 |     5  (40)| 00:00:01
|   2 |   HASH UNIQUE       |           |   107 |  1070 |     4  (25)| 00:00:01
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  1070 |     3   (0)| 00:00:01
--------------------------------------------------------------------------------
 
10 rows selected

Essayez donc d’exemplifier vos propos avec un jeu d’essai.

[waouni]

C'est clair.
Ce que j'ai voulu savoir en fait, et je pense que c'est pas possible, c'est pourquoi si je rajoute "DISTINCT" dans la meme requete, ça me renvoie exactement le meme resultat mais avec un tri différent.

Merci

[Rei Ichido]

Envoyé par mnitu

C'est faux! Le tri se fait après le distinct donc peut importe ce que le distinct fait!

Attention, ici, le tri ne sert à rien, puisqu'il est opéré sur une colonne à valeur unique ; c'est pour ça que le distinct change l'ordre.

Par exemple :

Code :

1
2
3
4
5
6
7
8
9
 
WITH t AS
     (  SELECT 3 AS a,'A' AS b FROM DUAL
        UNION ALL       
        SELECT 1 AS a,'A' AS b FROM DUAL
        UNION ALL
        SELECT 2 AS a,'A' AS b FROM DUAL
       )       
SELECT a,b FROM t ORDER BY b

Me retourne l'ordre 3,2,1.
Un distinct me sort 1,2,3.
Si j'enlève le order by, j'ai l'ordre naturel de lecture 3,1,2.
Enfin le distinct sans le order by me donne 3,2,1.

Bien sûr, si je faisais un "vrai" tri, utile, sur la colonne a, j'aurais toujours le même ordre 1,2,3.

[waouni]

Très bien! C'est clair

Merci

[mnitu]

Envoyé par Rei Ichido

Attention, ici, le tri ne sert à rien, puisqu'il est opéré sur une colonne à valeur unique ; c'est pour ça que le distinct change l'ordre.

...

Vous est en erreur, le tri c'est la derniére étape donc le distinct n'a rien à voir!

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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> EXPLAIN plan FOR
  2  
  2  WITH t AS
  3       (  SELECT 3 AS a,'A' AS b FROM DUAL
  4          UNION ALL
  5          SELECT 1 AS a,'A' AS b FROM DUAL
  6          UNION ALL
  7          SELECT 2 AS a,'A' AS b FROM DUAL
  8         )
  9  SELECT DISTINCT a,b FROM t ORDER BY b
 10  /
 
Explained
 
SQL> SELECT *
  2    FROM TABLE(dbms_xplan.display)
  3  /
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2975066302
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |    18 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY   |      |     3 |    18 |     8  (25)| 00:00:01 |
|   2 |   HASH UNIQUE    |      |     3 |    18 |     7  (15)| 00:00:01 |
|   3 |    VIEW          |      |     3 |    18 |     6   (0)| 00:00:01 |
|   4 |     UNION-ALL    |      |       |       |            |          |
|   5 |      FAST DUAL   |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL   |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL   |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------
 
14 rows selected

Envoyé par Rei Ichido

...
Me retourne l'ordre 3,2,1.
Un distinct me sort 1,2,3.
Si j'enlève le order by, j'ai l'ordre naturel de lecture 3,1,2.
Enfin le distinct sans le order by me donne 3,2,1.

Bien sûr, si je faisais un "vrai" tri, utile, sur la colonne a, j'aurais toujours le même ordre 1,2,3.

Il n'y pas "d'ordre naturel"! Il y a simplement de gens qui n'ont pas encore compris que sans "order by" les enregistrement arrivent dans un ordre non défini.

[Rei Ichido]

Envoyé par mnitu

Vous est en erreur, le tri c'est la derniére étape donc le distinct n'a rien à voir!

Donc vous affirmez que vous n'obtenez pas les même résultats que moi ? Le test est simple à faire.
Je ne conteste pas que le tri arrive en dernier. Je dis juste - si vous me relisez - que si le tri ne peut pas distinguer les objets, l'ordre n'est plus garanti et donc dépend forcément de ce qui s'est passé avant.

Il n'y pas "d'ordre naturel"! Il y a simplement de gens qui n'ont pas encore compris que sans "order by" les enregistrement arrivent dans un ordre non défini.

Quand je fais "SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL", certes la norme SQL me dit que les résultats peuvent arriver dans n'importe quel ordre, en pratique sur Oracle j'ai toujours eu 1,2. Alors certes, je ne me baserai jamais là dessus (si l'ordre est important, je mettrai un order by), mais on sent bien que le SGBD traite les choses "dans l'ordre".

[Rams7s]

Envoyé par Rei Ichido

Quand je fais "SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL", certes la norme SQL me dit que les résultats peuvent arriver dans n'importe quel ordre, en pratique sur Oracle j'ai toujours eu 1,2. Alors certes, je ne me baserai jamais là dessus (si l'ordre est important, je mettrai un order by), mais on sent bien que le SGBD traite les choses "dans l'ordre".

Avec des si on met paris en bouteille...

Pourquoi se baser sur des choses qu'on sait fausses alors que c'est tellement plus simple et vrai d'ajouter un order by?
D'autant plus que ca ne fonctionne (peut-etre) que pour votre select from dual. A partir du moment ou vous avez une table avec des delete et insert, c'est dans le desordre. C'est juste a proscrire comme solution

Enfin chacun est libre de faire ce qu'il veut

[Rei Ichido]

Envoyé par Rams7s

Pourquoi se baser sur des choses qu'on sait fausses alors que c'est tellement plus simple et vrai d'ajouter un order by?

Je ne me base sur rien, je dis même explicitement le contraire. Relisez le thread, j'explique juste à quelqu'un pourquoi quand il met distinct, l'ordre de retour change alors qu'il a un order by.

[mnitu]

Envoyé par Rei Ichido

...Je dis juste - si vous me relisez - que si le tri ne peut pas distinguer les objets, l'ordre n'est plus garanti et donc dépend forcément de ce qui s'est passé avant.
...

Il n'y a pas de tri sans order by! Et en présence d'"order by" le distinct ne change pas l'ordre. Parler de tri sans order by est un non sens.

Avant Oracle 10 le group by faisait aussi le tri. Quand Oracle a changé l'algorithme de regroupement toutes les requêtes sans order by ont deconnées!

[Rams7s]

@mnitu: Ca sert a rien de lui faire dire ce qu'il n'a pas dit. (J'avais pas compris non plus)

Ce qu'il dit, c'est que dans :

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
57
TMP@minilhc >CREATE TABLE tmp AS SELECT level AS n, mod(level,5) AS n2 FROM dual connect BY level<20;
 
TABLE created.
 
Elapsed: 00:00:00.02
TMP@minilhc >SELECT n, n2 FROM tmp ORDER BY n2;
 
         N         N2
---------- ----------
        10          0
        15          0
         5          0
        16          1
        11          1
         6          1
         1          1
         7          2
         2          2
        17          2
        12          2
        18          3
        13          3
         3          3
         8          3
        14          4
        19          4
         4          4
         9          4
 
19 rows selected.
 
Elapsed: 00:00:00.00
TMP@minilhc >SELECT DISTINCT n, n2 FROM tmp ORDER BY n2;
 
         N         N2
---------- ----------
         5          0
        10          0
        15          0
         1          1
         6          1
        11          1
        16          1
         2          2
         7          2
        12          2
        17          2
         3          3
         8          3
        13          3
        18          3
         4          4
         9          4
        14          4
        19          4
 
19 rows selected.

La table est effectivement triee sur N2, et c'est la seule et unique chose que l'on peut dire.
L'ordre des lignes avec N, lui il depend du bon vouloir de la BdD vu qu'il n'y a pas de order by sur cette colonne. Et en l'occurence si on fait un traitement sur N, cet ordre (qui n'est pas un tri) peut changer.

[Rei Ichido]

Envoyé par mnitu

Il n'y a pas de tri sans order by!

Précisément, je parle bien d'un tri effectué par un order by. J'ai la nette impression que vous ne me lisez pas !

Et en présence d'"order by" le distinct ne change pas l'ordre.

L'ordre entre deux objets non identiques, en effet. Mais pour deux objets identiques, l'ordre n'est plus garanti.
Cependant, l'ordre est dans la grande majorité des cas déterministes tant qu'on n'a rien changé sur les tables (déterministe ne veux pas dire prévisible a priori), et que le plan d'exécution est le même.
En changeant le plan d'exécution, par exemple en rajoutant un distinct, on a donc toutes les chances de changer l'ordre d'arrivée des résultats équivalents du point de vue de l'order by. Et c'était précisément la question de départ du thread.

[mnitu]

Envoyé par Rei Ichido

Précisément, je parle bien d'un tri effectué par un order by. J'ai la nette impression que vous ne me lisez pas !
...

Si, si je vous assure. Mais, ça reviens au même: il n’y pas de tri sans order by!
Dans un panier vous avez 3 bille rouge numérotés 1, 2, 3. Et vous voulez sortir toutes les billes du panier, une par une, par ordre de couleur. Et ensuite vous constatez qu’entre deux essaie consécutives l’ordre des numéros des billes n’est pas la même !? Fait le tri donc aussi sur les numéros des billes et arrêtez de parler d’ « ordre naturel », déterminisme et prévisibilité ! Et si les billes ne sont pas numérotées et donc strictement identique vous ne pouvez plus même parler d’ordre!

[mnitu]

Envoyé par Rams7s

...Et en l'occurence si on fait un traitement sur N, cet ordre (qui n'est pas un tri) peut changer.

Quelle ordre ? Imaginez que la colonne N est 0 par tout ? De quelle ordre parlez vous ?

[Rei Ichido]

Envoyé par mnitu

Si, si je vous assure. Mais, ça reviens au même: il n’y pas de tri sans order by!
Dans un panier vous avez 3 bille rouge numérotés 1, 2, 3. Et vous voulez sortir toutes les billes du panier, une par une, par ordre de couleur.

Sigh. C'est EXACTEMENT ce que je dis. Si la relation d'ordre entre les éléments n'est pas totale (ie, s'il y a des éléments identiques aux yeux de la comparaison), rien ne garantit l'ordre effectif de sortie.
Vous noterez cependant que Oracle sort les éléments équivalents dans un certain ordre (au sens, ordre du curseur de sortie) qui, s'il n'est pas prévisible a priori, reste déterministe parce que déterminé par la façon dont il a récupéré ces valeurs.
Encore une fois : faites l'essai des requêtes simplistes que je donne et constatez l'ordre d'affichage.

Fait le tri donc aussi sur les numéros des billes et arrêtez de parler d’ « ordre naturel », déterminisme et prévisibilité ! Et si les billes ne sont pas numérotées et donc strictement identique vous ne pouvez plus même parler d’ordre!

On peut pourtant parler d'ordre - il ne s'agit simplement pas d'une relation d'ordre total.
Quant à l'ordre "naturel", je l'ai indiqué pour le cas basique où je demande à Oracle des select from dual avec des union all, sans order by et sans clause where. Jusqu'à ce jour, il m'a toujours ressorti les valeurs dans l'ordre où je lui avais donné, c'est ce que j'ai appelé "naturel".

Cela dit, on tourne en rond, ce qui fait que je m'arrêterai là dans l'auto-justification.

[Rei Ichido]

Envoyé par mnitu

Quelle ordre ? Imaginez que la colonne N est 0 par tout ? De quelle ordre parlez vous ?

L'ordre du curseur. Jusqu'à preuve du contraire, Oracle ne met pas exactement au même niveau et de façon parallèle tous les éléments identiques vis à vis de l'ordre demandé.

[pacmann]

Salut,

Je pense que ce que veut dire Mnitu, c'est qu'il est dangereux de parler d'ordre en dehors de l'ORDER BY, parce que ce n'est pas déterministe.
Le plan par exemple, peut changer sans changer la requête (paramètres de l'optimiseur, du système, volmétries et répartition des données, ...).

Et je pense que même avec un plan strictement équivalent, ton séquencement en sorti peut changer (peut être des reorg, rebuilds ?)

Donc, si la question posée ici relève de la pure curiosité pour le fonctionnement Oracle, OK.
Par contre, si cela impacte le traitement des données, il ne faut pas se poser la question dans ce sens et :
- Soit ajouter des critères pour le tri
- Soit modifier le programmes pour qu'il ne soit pas affecté par le séquencement en sortie.