| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Query gives incorrect results
Jonathan,
Looking at the humungous explain plan, I'm not sure if index range (min/max) is coming into play. It does appear to be something like that (see filter plans).
The explain plan looks like this (sorry .. its a lot of text .. and it might wrap):
SQL> exec dbms_utility.analyze_schema('SYS',method=>'COMPUTE');
PL/SQL procedure successfully completed.
SQL> explain plan for
2 SELECT COUNT (*)
3 FROM dba_indexes i, dba_ind_columns ic, dba_tab_columns c
4 WHERE i.table_owner = 'SYS' 5 AND ic.index_name = i.index_name 6 AND ic.table_name = i.table_name 7 AND ic.table_owner = i.table_owner 8 AND ic.column_position = 9 (SELECT MIN (ic2.column_position) 10 FROM dba_ind_columns ic2 11 WHERE ic2.index_name = ic.index_name 12 AND ic2.table_name = ic.table_name 13 AND ic2.table_owner = ic.table_owner) 14 AND c.table_name = ic.table_name 15 AND c.owner = ic.table_owner 16 AND c.column_name = ic.column_name 17 AND c.data_type = 'VARCHAR2'
Explained.
SQL> select * from table(dbms_xplan.display);
|* 3 | FILTER | | | | |
| 4 | SORT GROUP BY | | 1 | 684 | 198 |
| 5 | NESTED LOOPS | | 1 | 684 | 192 |
| 6 | NESTED LOOPS OUTER | | 1 | 681 | 191 |
| 7 | NESTED LOOPS | | 1 | 675 | 191 |
| 8 | NESTED LOOPS | | 1 | 668 | 191 |
| 9 | NESTED LOOPS | | 1 | 660 | 190 |
| 10 | NESTED LOOPS | | 1 | 636 | 189 |
| 11 | NESTED LOOPS | | 1 | 622 | 188 |
| 12 | NESTED LOOPS OUTER | | 1 | 598 | 186 |
| 13 | NESTED LOOPS OUTER | | 1 | 578 | 185 |
| 14 | NESTED LOOPS | | 1 | 532 | 184 |
| 15 | NESTED LOOPS OUTER | | 1 | 522 | 183 |
| 16 | NESTED LOOPS | | 1 | 512 | 182 |
| 17 | NESTED LOOPS OUTER | | 1 | 481 | 181 |
| 18 | NESTED LOOPS | | 1 | 467 | 181 |
| 19 | NESTED LOOPS | | 1 | 441 | 180 |
| 20 | NESTED LOOPS | | 1 | 410 | 178 |
|* 21 | FILTER | | | | |
| 22 | NESTED LOOPS OUTER | | | | |
| 23 | NESTED LOOPS | | 1 | 363 | 176 |
| 24 | NESTED LOOPS | | 1 | 339 | 175 |
| 25 | NESTED LOOPS | | 2 | 648 | 173 |
| 26 | NESTED LOOPS | | 2 | 586 | 171 |
| 27 | NESTED LOOPS OUTER | | 3 | 816 | 168 |
| 28 | NESTED LOOPS OUTER | | 3 | 786 | 165 |
| 29 | NESTED LOOPS OUTER | | 3 | 693 | 159 |
| 30 | NESTED LOOPS OUTER | | 3 | 651 | 156 |
| 31 | NESTED LOOPS | | 3 | 525 | 153 |
|* 32 | HASH JOIN | | 84 | 11844 | 69 |
| 33 | NESTED LOOPS | | 814 | 87912 | 32 |
| 34 | NESTED LOOPS | | 1 | 77 | 4 |
| 35 | NESTED LOOPS | | 1 | 63 | 3 |
| 36 | NESTED LOOPS | | 1 | 42 | 2 |
| 37 | TABLE ACCESS BY INDEX RO| USER$ | 1 | 21 | 1 |
|* 38 | INDEX UNIQUE SCAN | I_USER1 | 40 | | |
| 39 | TABLE ACCESS BY INDEX RO| USER$ | 1 | 21 | 1 |
|* 40 | INDEX UNIQUE SCAN | I_USER1 | 40 | | |
| 41 | TABLE ACCESS BY INDEX ROW| USER$ | 1 | 21 | 1 |
|* 42 | INDEX UNIQUE SCAN | I_USER1 | 40 | | |
| 43 | TABLE ACCESS BY INDEX ROWI| USER$ | 1 | 14 | 1 |
|* 44 | INDEX UNIQUE SCAN | I_USER1 | 40 | | | |* 45 | TABLE ACCESS FULL | OBJ$ | 814 | 25234 | 28 |
| 46 | TABLE ACCESS FULL | OBJ$ | 14647 | 472K| 28 |
|* 47 | TABLE ACCESS CLUSTER | COL$ | 1 | 34 | 1 | |* 48 | INDEX UNIQUE SCAN | I_OBJ# | 100 | | | |* 49 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 42 | 1 | |* 50 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 14 | 1 | |* 51 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 31 | 2 | |* 52 | INDEX RANGE SCAN | I_OBJ3 | 23 | | 1 |
| 53 | TABLE ACCESS CLUSTER | USER$ | 1 | 10 | 1 |
|* 54 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 55 | TABLE ACCESS CLUSTER | ICOL$ | 1 | 21 | 1 |
|* 56 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | |
| 57 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 31 | 1 |
|* 58 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | | |* 59 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 15 | 1 | |* 60 | INDEX UNIQUE SCAN | I_IND1 | 1 | | | |* 61 | TABLE ACCESS CLUSTER | COL$ | 1 | 24 | 1 | |* 62 | TABLE ACCESS CLUSTER | ATTRCOL$ | 1 | 37 | 1 |
| 63 | TABLE ACCESS CLUSTER | USER$ | 1 | 10 | 1 |
|* 64 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 65 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 31 | 2 |
|* 66 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 | |* 67 | TABLE ACCESS CLUSTER | IND$ | 1 | 26 | 1 | |* 68 | INDEX UNIQUE SCAN | I_OBJ# | 100 | | |
| 69 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 14 | |
|* 70 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | | |* 71 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 31 | 1 | |* 72 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | |
| 73 | TABLE ACCESS CLUSTER | USER$ | 1 | 10 | 1 |
|* 74 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 75 | TABLE ACCESS CLUSTER | USER$ | 1 | 10 | 1 |
|* 76 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 77 | TABLE ACCESS CLUSTER | SEG$ | 1 | 46 | 1 |
|* 78 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | |
| 79 | TABLE ACCESS CLUSTER | TS$ | 1 | 20 | 1 |
|* 80 | INDEX UNIQUE SCAN | I_TS# | 583 | | |
| 81 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 24 | 2 |
|* 82 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 |
| 83 | TABLE ACCESS CLUSTER | ICOL$ | 1 | 14 | 1 |
|* 84 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | |* 85 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 24 | 1 | |* 86 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | | |* 87 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 1 | |* 88 | INDEX UNIQUE SCAN | I_IND1 | 1 | | | |* 89 | INDEX UNIQUE SCAN | I_COL3 | 1 | 7 | | |* 90 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | 6 | |
| 91 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 |
|* 92 | INDEX UNIQUE SCAN | I_USER# | 1 | | | -------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 - filter("IC"."POS#"=MIN("IC"."POS#"))
21 - filter("C"."NAME"=DECODE("AC"."NAME",NULL,"C"."NAME","AC"."NAME"))
32 - access("SYS_ALIAS_6"."NAME"="BASE"."NAME" AND "SYS_ALIAS_6"."OWNER#"="U"."USER#")
38 - access("U"."NAME"='SYS')
40 - access("BO"."NAME"='SYS')
42 - access("IU"."NAME"='SYS')
44 - access("BO"."NAME"='SYS')
45 - filter("BO"."USER#"="BASE"."OWNER#")
47 - filter(DECODE("C"."PROPERTY",0,'NO',DECODE(BITAND("C"."PROPERTY",32),32,'YES','NO'))='NO')
48 - access("SYS_ALIAS_6"."OBJ#"="C"."OBJ#")
49 - filter("C"."OBJ#"="SYS_ALIAS_5"."OBJ#"(+) AND "C"."INTCOL#"="SYS_ALIAS_5"."INTCOL#"(+))
50 - access("C"."OBJ#"="H"."OBJ#"(+) AND "C"."INTCOL#"="H"."INTCOL#"(+))
51 - filter("OT"."TYPE#"(+)=13)
52 - access("SYS_ALIAS_5"."TOID"="OT"."OID$"(+))
54 - access("OT"."OWNER#"="UT"."USER#"(+))
56 - access("IC"."BO#"="BASE"."OBJ#")
58 - access("IC"."OBJ#"="IDX"."OBJ#")
59 - 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)
RE2"))
62 - filter("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
64 - access("IO"."USER#"="IDX"."OWNER#")
66 - access("IO"."OWNER#"="IU"."USER#" AND "BASE"."NAME"="IO"."NAME")
67 - filter(BITAND("I"."FLAGS",4096)=0)
68 - access("I"."BO#"="IO"."OBJ#")
70 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
71 - filter("IDX"."NAME"="O"."NAME")
72 - access("O"."OBJ#"="I"."OBJ#")
74 - access("ITO"."OWNER#"="ITU"."USER#"(+))
76 - access("U"."USER#"="O"."OWNER#")
78 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+))
80 - access("I"."TS#"="TS"."TS#"(+))
82 - access("BO"."USER#"="BASE"."OWNER#" AND "BASE"."NAME"="BASE"."NAME")
84 - access("IC"."BO#"="BASE"."OBJ#")
85 - filter("IDX"."NAME"="IDX"."NAME")
86 - access("IC"."OBJ#"="IDX"."OBJ#")
87 - 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)
RE2"))
90 - access("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
92 - access("IO"."USER#"="IDX"."OWNER#")
Note: cpu costing is off
149 rows selected.
SQL> SELECT COUNT (*)
2 FROM dba_indexes i, dba_ind_columns ic, dba_tab_columns c
3 WHERE i.table_owner = 'SYS'
4 AND ic.index_name = i.index_name
5 AND ic.table_name = i.table_name
6 AND ic.table_owner = i.table_owner
7 AND ic.column_position =
8 (SELECT MIN (ic2.column_position)
9 FROM dba_ind_columns ic2
10 WHERE ic2.index_name = ic.index_name
11 AND ic2.table_name = ic.table_name
12 AND ic2.table_owner = ic.table_owner)
13 AND c.table_name = ic.table_name
14 AND c.owner = ic.table_owner
15 AND c.column_name = ic.column_name
16 AND c.data_type = 'VARCHAR2'
17 /
313 <==========================================
SQL> exec dbms_stats.delete_schema_stats('SYS');
PL/SQL procedure successfully completed.
SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for
2 SELECT COUNT (*)
3 FROM dba_indexes i, dba_ind_columns ic, dba_tab_columns c
4 WHERE i.table_owner = 'SYS' 5 AND ic.index_name = i.index_name 6 AND ic.table_name = i.table_name 7 AND ic.table_owner = i.table_owner 8 AND ic.column_position = 9 (SELECT MIN (ic2.column_position) 10 FROM dba_ind_columns ic2 11 WHERE ic2.index_name = ic.index_name 12 AND ic2.table_name = ic.table_name 13 AND ic2.table_owner = ic.table_owner) 14 AND c.table_name = ic.table_name 15 AND c.owner = ic.table_owner 16 AND c.column_name = ic.column_name 17 AND c.data_type = 'VARCHAR2'
Explained.
SQL> select * from table(dbms_xplan.display);
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS OUTER | | | | |
| 4 | NESTED LOOPS OUTER | | | | |
| 5 | NESTED LOOPS | | | | |
| 6 | NESTED LOOPS OUTER | | | | |
| 7 | NESTED LOOPS | | | | |
| 8 | NESTED LOOPS OUTER | | | | |
| 9 | NESTED LOOPS | | | | |
| 10 | NESTED LOOPS | | | | |
| 11 | NESTED LOOPS | | | | |
|* 12 | FILTER | | | | |
| 13 | NESTED LOOPS OUTER | | | | |
| 14 | NESTED LOOPS | | | | |
| 15 | NESTED LOOPS | | | | |
| 16 | NESTED LOOPS | | | | |
| 17 | NESTED LOOPS | | | | |
| 18 | NESTED LOOPS | | | | |
| 19 | NESTED LOOPS OUTER | | | | |
| 20 | NESTED LOOPS OUTER | | | | |
| 21 | NESTED LOOPS OUTER | | | | |
| 22 | NESTED LOOPS OUTER | | | | |
| 23 | NESTED LOOPS | | | | |
| 24 | NESTED LOOPS | | | | |
| 25 | NESTED LOOPS | | | | |
| 26 | NESTED LOOPS | | | | |
| 27 | TABLE ACCESS BY INDEX ROWID| USER$ | | | |
|* 28 | INDEX UNIQUE SCAN | I_USER1 | | | |
| 29 | TABLE ACCESS BY INDEX ROWID| USER$ | | | |
|* 30 | INDEX UNIQUE SCAN | I_USER1 | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | USER$ | | | |
|* 32 | INDEX UNIQUE SCAN | I_USER1 | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 34 | INDEX RANGE SCAN | I_OBJ2 | | | | |* 35 | TABLE ACCESS CLUSTER | COL$ | | | | |* 36 | INDEX UNIQUE SCAN | I_OBJ# | | | | |* 37 | TABLE ACCESS CLUSTER | COLTYPE$ | | | | |* 38 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | | | | |* 39 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | | |* 40 | INDEX RANGE SCAN | I_OBJ3 | | | |
| 41 | TABLE ACCESS CLUSTER | USER$ | | | |
|* 42 | INDEX UNIQUE SCAN | I_USER# | | | |
| 43 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 44 | INDEX RANGE SCAN | I_OBJ2 | | | |
| 45 | TABLE ACCESS CLUSTER | ICOL$ | | | |
|* 46 | INDEX UNIQUE SCAN | I_OBJ# | | | |
| 47 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 48 | INDEX UNIQUE SCAN | I_OBJ1 | | | | |* 49 | TABLE ACCESS BY INDEX ROWID | IND$ | | | | |* 50 | INDEX UNIQUE SCAN | I_IND1 | | | | |* 51 | TABLE ACCESS CLUSTER | COL$ | | | | |* 52 | TABLE ACCESS CLUSTER | ATTRCOL$ | | | |
| 53 | TABLE ACCESS CLUSTER | USER$ | | | |
|* 54 | INDEX UNIQUE SCAN | I_USER# | | | |
| 55 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 56 | INDEX RANGE SCAN | I_OBJ2 | | | | |* 57 | TABLE ACCESS CLUSTER | IND$ | | | | |* 58 | INDEX UNIQUE SCAN | I_OBJ# | | | |
| 59 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 60 | INDEX UNIQUE SCAN | I_OBJ1 | | | | |* 61 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | | |* 62 | INDEX UNIQUE SCAN | I_OBJ1 | | | |
| 63 | TABLE ACCESS CLUSTER | USER$ | | | |
|* 64 | INDEX UNIQUE SCAN | I_USER# | | | |
| 65 | TABLE ACCESS CLUSTER | USER$ | | | |
|* 66 | INDEX UNIQUE SCAN | I_USER# | | | |
| 67 | TABLE ACCESS CLUSTER | SEG$ | | | |
|* 68 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | | | |
| 69 | TABLE ACCESS CLUSTER | TS$ | | | |
|* 70 | INDEX UNIQUE SCAN | I_TS# | | | |
| 71 | SORT AGGREGATE | | | | |
| 72 | NESTED LOOPS | | | | |
| 73 | NESTED LOOPS OUTER | | | | |
| 74 | NESTED LOOPS | | | | |
| 75 | NESTED LOOPS | | | | |
| 76 | NESTED LOOPS | | | | |
| 77 | NESTED LOOPS | | | | |
| 78 | NESTED LOOPS | | | | |
| 79 | TABLE ACCESS BY INDEX ROWID | USER$ | | | |
|* 80 | INDEX UNIQUE SCAN | I_USER1 | | | |
| 81 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 82 | INDEX RANGE SCAN | I_OBJ2 | | | |
| 83 | TABLE ACCESS CLUSTER | ICOL$ | | | |
|* 84 | INDEX UNIQUE SCAN | I_OBJ# | | | | |* 85 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | | |* 86 | INDEX UNIQUE SCAN | I_OBJ1 | | | | |* 87 | TABLE ACCESS BY INDEX ROWID | IND$ | | | | |* 88 | INDEX UNIQUE SCAN | I_IND1 | | | | |* 89 | TABLE ACCESS CLUSTER | COL$ | | | | |* 90 | TABLE ACCESS CLUSTER | ATTRCOL$ | | | |
| 91 | TABLE ACCESS CLUSTER | USER$ | | | |
|* 92 | INDEX UNIQUE SCAN | I_USER# | | | |
| 93 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 94 | INDEX UNIQUE SCAN | I_OBJ1 | | | |
| 95 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 96 | INDEX UNIQUE SCAN | I_OBJ1 | | | |
| 97 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 98 | INDEX UNIQUE SCAN | I_OBJ1 | | | |
| 99 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|*100 | INDEX UNIQUE SCAN | I_OBJ1 | | | |
| 101 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|*102 | INDEX UNIQUE SCAN | I_OBJ1 | | | | |*103 | TABLE ACCESS CLUSTER | TAB$ | | | | |*104 | INDEX UNIQUE SCAN | I_OBJ# | | | | ------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("IC"."POS#"= (SELECT /*+ */ MIN("IC"."POS#") FROM "SYS"."ATTRCOL$" "AC","SYS"."IND$"
"I","SYS"."USER$" "BO","SYS"."USER$" "IO","SYS"."ICOL$" "IC","SYS"."OBJ$" "BASE","SYS"."OBJ$"
"IDX","SYS"."COL$" "C" WHERE "IC"."BO#"="C"."OBJ#" AND
"C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2") AND
"IC"."OBJ#"="IDX"."OBJ#" AND "IDX"."NAME"=:B1 AND "BASE"."NAME"=:B2 AND "BO"."USER#"="BASE"."OWNER#" AND
"IC"."BO#"="BASE"."OBJ#" AND "IO"."USER#"="IDX"."OWNER#" AND "BO"."NAME"=:B3 AND "IDX"."OBJ#"="I"."OBJ#"
AND ("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) AND "C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+)) AND
DECODE("C"."TYPE#",1,DECODE("C"."CHARSETFORM",2,'NVARCHAR2','VARCHAR2'),2,DECODE(TO_CHAR("C"."SCALE"),NULL,D
ECODE(TO_CHAR("C"."PRECISION#"),NULL,'NUMBER','FLOAT'),'NUMBER'),8,'LONG',9,DECODE("C"."CHARSETFORM",2,'NCHA
R VARYING','VARCHAR'),12,'DATE',23,'RAW',24,'LONG RAW',58,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME"
FROM "OBJ$" "O" WHERE "O"."OBJ#"=:B4),"OT"."NAME"),69,'ROWID',96,DECODE("C"."CHARSETFORM",2,'NCHAR','CHAR'),
105,'MLSLABEL',106,'MLSLABEL',111,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
"O"."OBJ#"=:B5),"OT"."NAME"),112,DECODE("C"."CHARSETFORM",2,'NCLOB','CLOB'),113,'BLOB',114,'BFILE',115,'CFIL
E',121,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
"O"."OBJ#"=:B6),"OT"."NAME"),122,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
"O"."OBJ#"=:B7),"OT"."NAME"),123,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
"O"."OBJ#"=:B8),"OT"."NAME"),178,'TIME('||TO_CHAR("C"."SCALE")||')',179,'TIME('||TO_CHAR("C"."SCALE")||')'||
' WITH TIME ZONE',180,'TIMESTAMP('||TO_CHAR("C"."SCALE")||')',181,'TIMESTAMP('||TO_CHAR("C"."SCALE")||')'||'
WITH TIME ZONE',231,'TIMESTAMP('||TO_CHAR("C"."SCALE")||')'||' WITH LOCAL TIME ZONE',182,'INTERVAL
YEAR('||TO_CHAR("C"."PRECISION#")||') TO MONTH',183,'INTERVAL DAY('||TO_CHAR("C"."PRECISION#")||') TO
SECOND('||TO_CHAR("C"."SCALE")||')',208,'UROWID','UNDEFINED')='VARCHAR2' AND ("SYS_ALIAS_9"."TYPE#"=3 OR
"SYS_ALIAS_9"."TYPE#"=4 OR "SYS_ALIAS_9"."TYPE#"=2 AND NOT EXISTS (SELECT 0 FROM "SYS"."TAB$" "T" WHERE
"T"."OBJ#"=:B9 AND (BITAND("T"."PROPERTY",512)=512 OR BITAND("T"."PROPERTY",8192)=8192))))
12 - filter("C"."NAME"=DECODE("AC"."NAME",NULL,"C"."NAME","AC"."NAME"))
28 - access("IU"."NAME"='SYS')
30 - access("SYS_ALIAS_3"."NAME"="IU"."NAME")
32 - access("U"."NAME"="SYS_ALIAS_3"."NAME")
34 - access("SYS_ALIAS_9"."OWNER#"="U"."USER#")
35 - filter(DECODE("C"."PROPERTY",0,'NO',DECODE(BITAND("C"."PROPERTY",32),32,'YES','NO'))='NO')
36 - access("SYS_ALIAS_9"."OBJ#"="C"."OBJ#")
37 - filter("C"."INTCOL#"="SYS_ALIAS_8"."INTCOL#"(+))
38 - access("C"."OBJ#"="H"."OBJ#"(+) AND "C"."INTCOL#"="H"."INTCOL#"(+))
39 - filter("OT"."TYPE#"(+)=13)
40 - access("SYS_ALIAS_8"."TOID"="OT"."OID$"(+))
42 - access("OT"."OWNER#"="UT"."USER#"(+))
44 - access("SYS_ALIAS_3"."USER#"="SYS_ALIAS_2"."OWNER#" AND "SYS_ALIAS_9"."NAME"="SYS_ALIAS_2"."NAME")
46 - access("IC"."BO#"="SYS_ALIAS_2"."OBJ#")
48 - access("IC"."OBJ#"="SYS_ALIAS_1"."OBJ#")
49 - 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)
50 - access("SYS_ALIAS_1"."OBJ#"="I"."OBJ#")
51 - filter("C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2"))
52 - filter("C"."INTCOL#"="AC"."INTCOL#"(+))
54 - access("IO"."USER#"="SYS_ALIAS_1"."OWNER#")
56 - access("IO"."OWNER#"="IU"."USER#" AND "SYS_ALIAS_2"."NAME"="IO"."NAME")
57 - filter(BITAND("I"."FLAGS",4096)=0)
58 - access("I"."BO#"="IO"."OBJ#")
60 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
61 - filter("SYS_ALIAS_1"."NAME"="O"."NAME")
62 - access("O"."OBJ#"="I"."OBJ#")
64 - access("ITO"."OWNER#"="ITU"."USER#"(+))
66 - access("U"."USER#"="O"."OWNER#")
68 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+))
70 - access("I"."TS#"="TS"."TS#"(+))
80 - access("BO"."NAME"=:B1)
82 - access("BO"."USER#"="BASE"."OWNER#" AND "BASE"."NAME"=:B1)
84 - access("IC"."BO#"="BASE"."OBJ#")
85 - filter("IDX"."NAME"=:B1)
86 - access("IC"."OBJ#"="IDX"."OBJ#")
87 - 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)
88 - access("IDX"."OBJ#"="I"."OBJ#")
89 - filter("C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2"))
90 - filter("C"."INTCOL#"="AC"."INTCOL#"(+))
92 - access("IO"."USER#"="IDX"."OWNER#")
94 - access("O"."OBJ#"=:B1)
96 - access("O"."OBJ#"=:B1)
98 - access("O"."OBJ#"=:B1)
100 - access("O"."OBJ#"=:B1)
102 - access("O"."OBJ#"=:B1)
Note: rule based optimization
189 rows selected.
SQL> SELECT COUNT (*)
2 FROM dba_indexes i, dba_ind_columns ic, dba_tab_columns c
3 WHERE i.table_owner = 'SYS'
4 AND ic.index_name = i.index_name
5 AND ic.table_name = i.table_name
6 AND ic.table_owner = i.table_owner
7 AND ic.column_position =
8 (SELECT MIN (ic2.column_position)
9 FROM dba_ind_columns ic2
10 WHERE ic2.index_name = ic.index_name
11 AND ic2.table_name = ic.table_name
12 AND ic2.table_owner = ic.table_owner)
13 AND c.table_name = ic.table_name
14 AND c.owner = ic.table_owner
15 AND c.column_name = ic.column_name
16 AND c.data_type = 'VARCHAR2'
17 /
53 <======================
Anurag
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bbvm8m$5v2$1$8302bc10_at_news.demon.co.uk...
> > Would you check the two execution paths, > and let us know which new-looking feature > appears in the plan that goes wrong. > > It might be > index range (min/max) > > This has a known bug in 9.2 > > -- > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The educated person is not the person > who can answer the questions, but the > person who can question the answers -- T. Schick Jr > > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > > ____Sweden___June 9th - 11th > ____Australia__June 27th (Canberra) > ____Finland__September 22nd - 24th > ____Norway___September 25th - 26th > > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > ____Australia_June 18th - 20th (Perth) > ____Australia_June 23rd - 25th (Melbourne) > ____USA_(CA, TX)_August > ____UK___September > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > "Anurag Varma" <avdbi_at_hotmail.com> wrote in message > news:MBQDa.1550$av3.240_at_news02.roc.ny.frontiernet.net... > > eek ... you are correct. This does happen when you analyze the sys > schema. > > > > Anurag > > > > > > >Received on Mon Jun 09 2003 - 19:09:54 CDT
 |
 |