Récupérer min période et id d'un enregistrement

**anisj1m** · 20/07/2012, 13h13

Bonjour,

Table (id, nom, date_debut, date_fin)

Requête : trouver le nom de celui qui a la période (date_fin - date_debut) la plus petit.

idée :

Code :

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

1
2
3
 
select nom,(date_fin - date_debut) from Table where 
(date_fin - date_debut) = (select MIN(date_fin - date_debut) from table);

mais cette requête va prendre un énorme temps d’exécution dans le cas ou la quantité des données est considérable, car pour chaque date_fin il va balayer date_debut.

est ce qu'il n'y a pas un moyen de récupérer l'id de celui qui a une période minimal dans la requête imbriques?

Est ce que vous avez des idées?

Merci

**ojo77** · 20/07/2012, 14h35

Je pense que le Query Rewrite est efficace dans votre cas :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> create table t1 as
  2  select rownum id, sysdate-ABS(MOD(dbms_random.random,3650)) date_debut, sysdate+ABS(MOD(dbms_random.random,365)) date_fin
  3  from dual
  4  connect by rownum <= 1E6;
 
Table créée.
 
Ecoulé : 00 :00 :16.81
SQL> exec dbms_stats.gather_table_stats(user, 'T1') ;
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :00.46

Un petit 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
SQL>  select id
  2        , inter
  3   from ( select id
  4               , date_fin-date_debut INTER, min(date_fin-date_debut) over (partition by '1') min_inter
  5          from t1 )
  6   where inter=min_inter
  7   /
 
        ID      INTER
---------- ----------
    441773          1
    600182          1
    957233          1
    973669          1
 
EcoulΘ : 00 :00 :00.89
 
Plan d'exΘcution
----------------------------------------------------------
Plan hash value: 1732431454
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000K|    30M|   982   (1)| 00:00:12 |
|*  1 |  VIEW               |      |  1000K|    30M|   982   (1)| 00:00:12 |
|   2 |   WINDOW BUFFER     |      |  1000K|    20M|   982   (1)| 00:00:12 |
|   3 |    TABLE ACCESS FULL| T1   |  1000K|    20M|   982   (1)| 00:00:12 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("MIN_INTER"=INTERNAL_FUNCTION("INTER"))
 
 
Statistiques
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3602  consistent gets
          0  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

90 centièmes, pas mal pour 1 million de lignes
Avec votre code ...

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
SQL>  sELECT id,(date_fin - date_debut) FROM T1 WHERE
  2   (date_fin - date_debut) = (SELECT MIN(date_fin - date_debut) FROM T1);
 
        ID (DATE_FIN-DATE_DEBUT)
---------- ---------------------
    441773                     1
    600182                     1
    957233                     1
    973669                     1
 
EcoulΘ : 00 :00 :00.47
 
Plan d'exΘcution
----------------------------------------------------------
Plan hash value: 4058903293
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 10000 |   205K|  1978   (2)| 00:00:24 |
|*  1 |  TABLE ACCESS FULL  | T1   | 10000 |   205K|   996   (3)| 00:00:12 |
|   2 |   SORT AGGREGATE    |      |     1 |    16 |            |          |
|   3 |    TABLE ACCESS FULL| T1   |  1000K|    15M|   982   (1)| 00:00:12 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DATE_FIN"-"DATE_DEBUT"= (SELECT
              MIN("DATE_FIN"-"DATE_DEBUT") FROM "T1" "T1"))
 
 
Statistiques
----------------------------------------------------------
         10  recursive calls
          0  db block gets
       7215  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          4  rows processed

47 centièmes ... pourtant je la trouvais bien ma requête

Pire si je crée un index :

Code :

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

1
2
create index f_t1 on t1(date_fin-date_debut)
/

on a (avec les plans d'exécution) :

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
SQL> sELECT id,(date_fin - date_debut) FROM T1 WHERE
  2  (date_fin - date_debut) = (SELECT MIN(date_fin - date_debut) FROM T1);
 
        ID (DATE_FIN-DATE_DEBUT)
---------- ---------------------
    441773                     1
    600182                     1
    957233                     1
    973669                     1
 
Ecoulé : 00 :00 :00.00
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 3091833076
 
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      | 10000 |   175K|   979   (1)| 00:00:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1   | 10000 |   175K|   976   (1)| 00:00:12 |
|*  2 |   INDEX RANGE SCAN           | F_T1 |  4000 |       |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |      |     1 |    13 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| F_T1 |     1 |    13 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DATE_FIN"-"DATE_DEBUT"= (SELECT MIN("DATE_FIN"-"DATE_DEBUT")
              FROM "T1" "T1"))
 
 
Statistiques
----------------------------------------------------------
          2  recursive calls
          0  db block gets
         13  consistent gets
          2  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

et si je reprends ma requête que je pensais maligne :

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
SQL> select id
  2       , inter
  3  from ( select id
  4              , date_fin-date_debut INTER, min(date_fin-date_debut) over (partition by '1') min_inter
  5         from t1 )
  6  where inter=min_inter
  7  /
 
        ID      INTER
---------- ----------
    441773          1
    600182          1
    957233          1
    973669          1
 
Ecoulé : 00 :00 :00.88
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 1732431454
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000K|    37M|   981   (1)| 00:00:12 |
|*  1 |  VIEW               |      |  1000K|    37M|   981   (1)| 00:00:12 |
|   2 |   WINDOW BUFFER     |      |  1000K|    17M|   981   (1)| 00:00:12 |
|   3 |    TABLE ACCESS FULL| T1   |  1000K|    17M|   981   (1)| 00:00:12 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("INTER"="MIN_INTER")
 
 
Statistiques
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3602  consistent gets
          0  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

Au final, je vous préconise de garder votre requête, elle est bien

**anisj1m** · 22/07/2012, 09h36

Merci infiniment ojo77,

Ta solution est la meilleure, et mieux que la mienne, je ne sais pas pourquoi mais peut être car dans ma requête j'ai une jointure et que l'un des dates est dans une table et l'autre dans une autre table, ce qui a rendu ma requête très lente.

Ta requête est claire et l’idée de sélectionner et comparer la différence et le min et géniale.

merci encore une fois ojo77

Récupérer min période et id d'un enregistrement

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager