une requete plusieurs accès

**miloon** · 12/04/2009, 16h30

Bonjour;
Est ce que Oracle lors de l'exécution d'une requete peut effectuer plusieurs accès à une table?
Merci

**pachot** · 15/04/2009, 22h04

Bonjour,

Oui, le plan d'exécution suivant va lire 15 fois la table 'B':

Code :

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

1
2
3
4
5
6
7
8
---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   390 |    8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS       |      |    15 |   390 |    8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A      |    15 |   195 |    2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| B      |    1 |    13 |    0   (0)| 00:00:01 |
---------------------------------------------------------------------------

Cordialement,
Franck.

**miloon** · 18/04/2009, 10h19

Bonjour merci pour votre réponse;
oui il la lit plusieurs fois mais il ne lui accede qu'une fois, en dautres termes en ne peut pas retrouver dans le plan d'execution plusieurs fois le nom d'une table dans la colonne object_name????

**fatsora** · 18/04/2009, 11h00

Bonjour,

peut etre c'est comme ca que tu veux ...

Code sql :

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
  2  SELECT a.column_name, b.data_type, a.column_position
  3        FROM sys.dba_ind_columns a, sys.dba_tab_columns b
  4       WHERE a.table_owner = b.owner
  5         and a.table_name = b.table_name
  6         and a.column_name = b.column_name
  7         and a.index_owner = 'BWCM_WS_NDB'
  8         and a.index_name = 'XIE01WS_CRM_MAP'
  9         and a.table_owner = 'BWCM_WS_NDB'
 10         and a.table_name = 'WS_CRM_MAP'
 11     ORDER BY a.column_position;
 
ExplicitÚ.
 
SQL> @?/rdbms/admin/utlxpls
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 3534575215
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                   |     1 |  2197 |    20   (5)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID                 | OBJ$              |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                          | I_OBJ1            |     1 |       |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID                | OBJ$              |     1 |    24 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN                         | I_OBJ1            |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID               | OBJ$              |     1 |    24 |     2   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN                        | I_OBJ1            |     1 |       |     1   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID              | OBJ$              |     1 |    24 |     2   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN                       | I_OBJ1            |     1 |       |     1   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID             | OBJ$              |     1 |    24 |     2   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN                      | I_OBJ1            |     1 |       |     1   (0)| 00:00:01 |
|  11 |  SORT ORDER BY                               |                   |     1 |  2197 |    20   (5)| 00:00:01 |
|* 12 |   FILTER                                     |                   |       |       |            |          |
|  13 |    NESTED LOOPS OUTER                        |                   |     1 |  2197 |    19   (0)| 00:00:01 |
|  14 |     NESTED LOOPS OUTER                       |                   |     1 |  2194 |    18   (0)| 00:00:01 |
|  15 |      NESTED LOOPS OUTER                      |                   |     1 |  2185 |    17   (0)| 00:00:01 |
|  16 |       NESTED LOOPS OUTER                     |                   |     1 |  2157 |    15   (0)| 00:00:01 |
|  17 |        NESTED LOOPS                          |                   |     1 |  2129 |    14   (0)| 00:00:01 |
|  18 |         NESTED LOOPS                         |                   |     1 |  2095 |    13   (0)| 00:00:01 |
|  19 |          NESTED LOOPS                        |                   |     1 |  2065 |    11   (0)| 00:00:01 |
|  20 |           VIEW                               | DBA_IND_COLUMNS   |     1 |  2050 |    10   (0)| 00:00:01 |
|  21 |            NESTED LOOPS OUTER                |                   |     1 |   186 |    10   (0)| 00:00:01 |
|  22 |             NESTED LOOPS                     |                   |     1 |   138 |     9   (0)| 00:00:01 |
|  23 |              NESTED LOOPS                    |                   |     1 |   115 |     8   (0)| 00:00:01 |
|  24 |               NESTED LOOPS                   |                   |     1 |    96 |     7   (0)| 00:00:01 |
|  25 |                NESTED LOOPS                  |                   |     1 |    69 |     5   (0)| 00:00:01 |
|  26 |                 NESTED LOOPS                 |                   |     1 |    57 |     4   (0)| 00:00:01 |
|  27 |                  NESTED LOOPS                |                   |     1 |    30 |     2   (0)| 00:00:01 |
|  28 |                   TABLE ACCESS BY INDEX ROWID| USER$             |     1 |    15 |     1   (0)| 00:00:01 |
|* 29 |                    INDEX UNIQUE SCAN         | I_USER1           |     1 |       |     0   (0)| 00:00:01 |
|  30 |                   TABLE ACCESS BY INDEX ROWID| USER$             |     1 |    15 |     1   (0)| 00:00:01 |
|* 31 |                    INDEX UNIQUE SCAN         | I_USER1           |     1 |       |     0   (0)| 00:00:01 |
|  32 |                  TABLE ACCESS BY INDEX ROWID | OBJ$              |     1 |    27 |     2   (0)| 00:00:01 |
|* 33 |                   INDEX RANGE SCAN           | I_OBJ2            |     1 |       |     1   (0)| 00:00:01 |
|* 34 |                 TABLE ACCESS BY INDEX ROWID  | IND$              |     1 |    12 |     1   (0)| 00:00:01 |
|* 35 |                  INDEX UNIQUE SCAN           | I_IND1            |     1 |       |     0   (0)| 00:00:01 |
|  36 |                TABLE ACCESS BY INDEX ROWID   | OBJ$              |     1 |    27 |     2   (0)| 00:00:01 |
|* 37 |                 INDEX RANGE SCAN             | I_OBJ2            |     1 |       |     1   (0)| 00:00:01 |
|* 38 |               TABLE ACCESS CLUSTER           | ICOL$             |     1 |    19 |     1   (0)| 00:00:01 |
|* 39 |                INDEX UNIQUE SCAN             | I_OBJ#            |     1 |       |     0   (0)| 00:00:01 |
|* 40 |              TABLE ACCESS CLUSTER            | COL$              |     1 |    23 |     1   (0)| 00:00:01 |
|* 41 |             TABLE ACCESS CLUSTER             | ATTRCOL$          |     1 |    48 |     1   (0)| 00:00:01 |
|  42 |           TABLE ACCESS BY INDEX ROWID        | USER$             |     1 |    15 |     1   (0)| 00:00:01 |
|* 43 |            INDEX UNIQUE SCAN                 | I_USER1           |     1 |       |     0   (0)| 00:00:01 |
|  44 |          TABLE ACCESS BY INDEX ROWID         | OBJ$              |     1 |    30 |     2   (0)| 00:00:01 |
|* 45 |           INDEX RANGE SCAN                   | I_OBJ2            |     1 |       |     1   (0)| 00:00:01 |
|* 46 |         TABLE ACCESS BY INDEX ROWID          | COL$              |     1 |    34 |     1   (0)| 00:00:01 |
|* 47 |          INDEX UNIQUE SCAN                   | I_COL1            |     1 |       |     0   (0)| 00:00:01 |
|* 48 |        TABLE ACCESS CLUSTER                  | COLTYPE$          |     1 |    28 |     1   (0)| 00:00:01 |
|* 49 |       TABLE ACCESS BY INDEX ROWID            | OBJ$              |     1 |    28 |     2   (0)| 00:00:01 |
|* 50 |        INDEX RANGE SCAN                      | I_OBJ3            |     1 |       |     1   (0)| 00:00:01 |
|* 51 |      INDEX RANGE SCAN                        | I_HH_OBJ#_INTCOL# |     1 |     9 |     1   (0)| 00:00:01 |
|  52 |     TABLE ACCESS CLUSTER                     | USER$             |     1 |     3 |     1   (0)| 00:00:01 |
|* 53 |      INDEX UNIQUE SCAN                       | I_USER#           |     1 |       |     0   (0)| 00:00:01 |
|* 54 |    TABLE ACCESS CLUSTER                      | TAB$              |     1 |    12 |     2   (0)| 00:00:01 |
|* 55 |     INDEX UNIQUE SCAN                        | I_OBJ#            |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("O"."OBJ#"=:B1)
   4 - access("O"."OBJ#"=:B1)
   6 - access("O"."OBJ#"=:B1)
   8 - access("O"."OBJ#"=:B1)
  10 - access("O"."OBJ#"=:B1)
  12 - filter("O"."TYPE#"=3 OR "O"."TYPE#"=4 OR "O"."TYPE#"=2 AND  NOT EXISTS (SELECT /*+ */ 0 FROM
              "SYS"."TAB$" "T" WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
              BITAND("T"."PROPERTY",8192)=8192)))
  29 - access("BO"."NAME"='BWCM_WS_NDB')
  31 - access("IO"."NAME"='BWCM_WS_NDB')
  33 - access("IO"."USER#"="IDX"."OWNER#" AND "IDX"."NAME"='XIE01WS_CRM_MAP')
  34 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
              "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  35 - access("IDX"."OBJ#"="I"."OBJ#")
  37 - access("BO"."USER#"="BASE"."OWNER#" AND "BASE"."NAME"='WS_CRM_MAP')
  38 - filter("IC"."OBJ#"="IDX"."OBJ#")
  39 - access("IC"."BO#"="BASE"."OBJ#")
  40 - filter("IC"."BO#"="C"."OBJ#" AND "C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#",
              "IC"."SPARE2"))
  41 - filter("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
  43 - access("A"."TABLE_OWNER"="U"."NAME")
  45 - access("O"."OWNER#"="U"."USER#" AND "A"."TABLE_NAME"="O"."NAME")
  46 - filter(DECODE("C"."PROPERTY",0,'NO',DECODE(BITAND("C"."PROPERTY",32),32,'YES','NO'))='NO')
  47 - access("O"."OBJ#"="C"."OBJ#" AND "A"."COLUMN_NAME"="C"."NAME")
  48 - filter("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
  49 - filter("OT"."TYPE#"(+)=13)
  50 - access("AC"."TOID"="OT"."OID$"(+))
  51 - access("C"."OBJ#"="H"."OBJ#"(+) AND "C"."INTCOL#"="H"."INTCOL#"(+))
  53 - access("OT"."OWNER#"="UT"."USER#"(+))
  54 - filter(BITAND("T"."PROPERTY",512)=512 OR BITAND("T"."PROPERTY",8192)=8192)
  55 - access("T"."OBJ#"=:B1)

une requete plusieurs accès

Administration Oracle

Vue hybride

Discussions similaires

Partager

Partager