Discussion :

[mnitu]

Bien sûr que non!

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
 
Connecté à :
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> set linesize 132
SQL> create index hr.emp_hire_date on hr.employees(hire_date)
/
Index créé.
 
SQL> set autotrace on
SQL> Select
       employee_id,
       first_name,
       salary
  From (
        Select employee_id,
               first_name,
               salary,
               hire_date
          from hr.employees
        order by hire_date desc
)
where rownum = 1
/
 
EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        173 Sundita                    6100
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 3645119196
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    38 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |               |       |       |            |          |
|   2 |   VIEW                        |               |     1 |    38 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   107 |  2461 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN DESCENDING| EMP_HIRE_DATE |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)

C'est Sundita!

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
 
 Select --+ ALL_ROWS
       employee_id,
       first_name,
       salary
  From (
        Select employee_id,
               first_name,
               salary,
               hire_date
          from hr.employees
        order by hire_date desc
       )
where rownum = 1
/
 
EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        167 Amit                       6200
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 3819624196
 
-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    38 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |           |       |       |            |          |
|   2 |   VIEW                  |           |   107 |  4066 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|           |   107 |  2461 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | EMPLOYEES |   107 |  2461 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)

Non c'est Amit!
Non c’est Sundita !
Non c’est Amit !

[Mohamed.Houri]

Bien sûr que non!

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
 
Connecté à :
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> set linesize 132
SQL> create index hr.emp_hire_date on hr.employees(hire_date)
/
Index créé.
 
SQL> set autotrace on
SQL> Select
       employee_id,
       first_name,
       salary
  From (
        Select employee_id,
               first_name,
               salary,
               hire_date
          from hr.employees
        order by hire_date desc
)
where rownum = 1
/
 
EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        173 Sundita                    6100
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 3645119196
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    38 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |               |       |       |            |          |
|   2 |   VIEW                        |               |     1 |    38 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   107 |  2461 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN DESCENDING| EMP_HIRE_DATE |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)

C'est Sundita!

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
 
 Select --+ ALL_ROWS
       employee_id,
       first_name,
       salary
  From (
        Select employee_id,
               first_name,
               salary,
               hire_date
          from hr.employees
        order by hire_date desc
       )
where rownum = 1
/
 
EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        167 Amit                       6200
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 3819624196
 
-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    38 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |           |       |       |            |          |
|   2 |   VIEW                  |           |   107 |  4066 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|           |   107 |  2461 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | EMPLOYEES |   107 |  2461 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)

Non c'est Amit!
Non c’est Sundita !
Non c’est Amit !

je vous l'ai dit : ROWNUM = 1 + ORDER BY = INDEX FULL SCAN = faire attention

ROWNUM is not a fixed attribute of a specific row in a table – if you attempt to use it as such, you will not achieve the results that you are expecting

Using ROWNUM in the Where Clause Causes Problems

[Pomalaix]

La démonstration est intéressante, mais fonctionnellement, demander "sors-moi le dernier embauché" quand les dates d'embauche ne sont pas uniques, c'est déjà bancal.

[Waldar]

je vous l'ai dit : ROWNUM = 1 + ORDER BY = INDEX FULL SCAN = faire attention

Finalement, on est revenu à l'époque du RULE !

[pachot]

Bonjour,

je vous l'ai dit : ROWNUM = 1 + ORDER BY = INDEX FULL SCAN = faire attention

Attention à quoi ? INDEX FAST FULL SCAN est le moyen le plus rapide d'aller chercher la première (ou dernière) entrée d'un index. 'FULL SCAN' est un peu trompeur ici puisqu'on s'arrête à la première ligne.

Sinon, mnitu, le résultat ne dépends pas que du plan d'exécution mais aussi de l'heure où on lance la requête parfois:

Code :

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

select current_timestamp from dual;

Sans parler d'un order by dbms_random.value ... Il y a des tas de raisons d'avoir un résultat différent. Ce qui compte, c'est que chaque résultat réponde à la requête, et c'est le cas dans ton exemple 'sortir un un employé embauché à la date la plus récente'.

Cordialement,
Franck.

[Mohamed.Houri]

Bonjour,


Attention à quoi ? FAST FULL SCAN est le moyen le plus rapide d'aller chercher la première (ou dernière) entrée d'un index. 'FULL SCAN' est un peu trompeur ici puisqu'on s'arrête à la première ligne.

Cordialement,
Franck.

Franck,

Ce n'est pas le INDEX FAST FULL SCAN mais le INDEX FULL SCAN dont je parle ici. Il y a une différence entre les deux.

De plus, je ne demande pas à faire attention à toute utilisation d'un INDEX FULL SCAN; mais uniquement à l'apparition de ce dernier lorsque plusieurs circonstances l'accompagnent. Entre autres, lorsque dans la requête qui l'utilise, il y a (a) un ORDER BY et (b) une clause du genre where ROWNUM <= 1

Dans ce cas, le CBO passe en mode FIRST_ROWS et sous ce mode, le CBO préfère l'utilisation d'un INDEX FULL SCAN afin d'éviter un ORDER BY et ceci quelque soit le coup de l'opération INDEX FULL SCAN.

Au passage, tu as peut-être bien remarqué la disparition de l'opération ORDER BY lorsque le INDEX FULL SCAN a été choisi par le CBO.

J'ai essayé de simuler cet effet ici

Order by and first_rows

[mnitu]

@Mohamed
INDEX FULL SCAN ou INDEX FAST FULL SCAN aucun importance dans ce cas.

@pachot
Je ne connais pas beaucoup de monde qui s’attente à voir le même résultat sur une requête de type select timestamp … et avec dbms_random si on veut on pourrait s’arranger !

@pomalaix
Ce n’est pas demander "sors-moi le dernier embauché" quand les dates d'embauche ne sont pas uniques qui est bancal mais oublier que souvent, plusieurs enregistrements peuvent répondre à ce type de demande. Mais vous avez très bien pointé le vrai problème.

Merci à tous de vos retours.

[McM]

J'ai l'habitude de dire au client : ROWNUM = 1, c'est Oracle qui choisit la ligne

[pachot]

Bonjour Mohamed,

Ce n'est pas le INDEX FAST FULL SCAN mais le INDEX FULL SCAN dont je parle ici.

Oui c'est un labsus. Je voulais dire 'INDEX FULL SCAN'. J'ai corrigé pour ne pas induire en erreur.
Merci,
Franck.

[mnitu]

Bonjour Mohamed,

Oui c'est un labsus. ...

Lapsus