Discussion :

[ora_home]

Bonjour,
Je suis entrain de jongler avec quelques requêtes pour voir le comportement du noyau ORACLE. (Version 10gR2)

J'en ai 2 tables ( T et TT) les deux ont la même structure que la table system DBA_OBJECTS.
La table T contiens 4044160 enregistrement
La table TT contiens 1011040 enregistrement.

La requete que je fait dans mes tests est la suivante :

Code :

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

1
2
3
4
5
6
 SELECT COUNT(*) FROM tt
 WHERE EXISTS 
        (SELECT 'X' FROM t WHERE t.owner = tt.owner
        AND t.SUBOBJECT_NAME = tt.SUBOBJECT_NAME
        AND t.OBJECT_NAME = tt.OBJECT_NAME    
        AND t.OBJECT_TYPE = tt.OBJECT_TYPE  );

Cas N1 : J'ai crée un l'index suivant sur la table T:

Code :

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

CREATE INDEX idx_t ON T (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE)

Et le plan d'exécution que j'ai eu dans ce cas est le suivant :

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
Plan d'exécution ( avec index CREATE INDEX idx_t ON T (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE))
----------------------------------------------------------
Plan hash value: 194536945
 
----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |   222 |       | 48517   (2)| 00:09:43 |
|   1 |  SORT AGGREGATE      |         |     1 |   222 |       |            |          |
|*  2 |   HASH JOIN SEMI     |         |     1 |   222 |   138M| 48517   (2)| 00:09:43 |
|   3 |    TABLE ACCESS FULL | TT      |  1178K|   124M|       |  3107   (3)| 00:00:38 |
|   4 |    VIEW              | VW_SQ_1 |  4462K|   472M|       | 12371   (3)| 00:02:29 |
|   5 |     TABLE ACCESS FULL| T       |  4462K|   472M|       | 12371   (3)| 00:02:29 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OWNER"="TT"."OWNER" AND "OBJECT_NAME"="TT"."OBJECT_NAME" AND
              "SUBOBJECT_NAME"="TT"."SUBOBJECT_NAME" AND "OBJECT_TYPE"="TT"."OBJECT_TYPE")
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistiques
----------------------------------------------------------
       1037  recursive calls
          0  db block gets
      15247  consistent gets
      15058  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          1  rows processed

Cas N° 2 : J'ai crée le même index sur la table TT

Code :

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

CREATE INDEX idx_tt ON TT (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE)

Et le plan d'exécution que j'ai eu dans ce cas est le suivant :

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
Plan d'exécution ( avec idx_t  et idx_tt)
----------------------------------------------------------
Plan hash value: 1217498393
 
------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |   222 |       | 47016   (2)| 00:09:25 |
|   1 |  SORT AGGREGATE        |         |     1 |   222 |       |            |          |
|*  2 |   HASH JOIN SEMI       |         |     1 |   222 |   138M| 47016   (2)| 00:09:25 |
|   3 |    INDEX FAST FULL SCAN| IDX_TT  |  1178K|   124M|       |  1607   (2)| 00:00:20 |
|   4 |    VIEW                | VW_SQ_1 |  4462K|   472M|       | 12371   (3)| 00:02:29 |
|   5 |     TABLE ACCESS FULL  | T       |  4462K|   472M|       | 12371   (3)| 00:02:29 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OWNER"="TT"."OWNER" AND "OBJECT_NAME"="TT"."OBJECT_NAME" AND
              "SUBOBJECT_NAME"="TT"."SUBOBJECT_NAME" AND "OBJECT_TYPE"="TT"."OBJECT_TYPE")
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistiques
----------------------------------------------------------
        686  recursive calls
          0  db block gets
       8529  consistent gets
       8892  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

Ma question est comment faire pour que le CBO évite un FULL SCAN au niveau de la Table (T) de la sous-requete et passe par son index idx_t ??

[ojo77]

Bonjour

Code :

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

1
2
3
4
5
6
SELECT COUNT(*) FROM tt
 WHERE EXISTS 
        (SELECT t.owner  FROM t WHERE t.owner = tt.owner
        AND t.SUBOBJECT_NAME = tt.SUBOBJECT_NAME
        AND t.OBJECT_NAME = tt.OBJECT_NAME    
        AND t.OBJECT_TYPE = tt.OBJECT_TYPE  );

Ca doit le faire sous les conditions que la table T a un nombre de blocs supérieur à celui lu par le dynamic sampling.

Vous pouvez aussi calculer vos stats, c'est pas mal pour influer positivement sur le CBO