| 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
I'm sorry, I've had a really bad day today, and the sight of that pair of execution plans really doesn't fill me with joy and delight.
There are indications that the optimiser is trying a new mechanism - but I can't concentrate well enough to check - especially with the output not being in my personal format. I would look at the point where one of the plans includes a SORT GROUP BY and the other doesn't as a first suspect
-- 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 ____USA__October 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:ll9Fa.1776$6C2.1562_at_news01.roc.ny.frontiernet.net...Received on Tue Jun 10 2003 - 11:11:27 CDT
> 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'
> 18 /
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display);
>
> --------------------------------------------------------------------
-----------------------------------------------
> | Id | Operation
| Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
-----------------------------------------------
> | 0 | SELECT STATEMENT
| | 1 | 26 | 198 |
> | 1 | SORT AGGREGATE
| | 1 | 26 | |
> | 2 | VIEW
| | 1 | 26 | 198 |
> |* 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),3 2,'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)
> 60 - access("IDX"."OBJ#"="I"."OBJ#")
> 61 - filter("IC"."BO#"="C"."OBJ#" AND
"C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC" ."SPA
> 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)
> 88 - access("IDX"."OBJ#"="I"."OBJ#")
> 89 - access("IC"."BO#"="C"."OBJ#" AND
"C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC" ."SPA
> 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'
> 18 /
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display);
>
> --------------------------------------------------------------------
----------------------------------------
> | Id | Operation |
Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
----------------------------------------
> | 0 | SELECT STATEMENT |
| | | |
> | 1 | SORT AGGREGATE |
| | | |
> |* 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,'LO NG',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','C LOB'),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")||')',17 9,'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')='VARCHAR 2' 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),3 2,'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)
> 103 - filter(BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)
> 104 - access("T"."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
> > >
> >
> >
> >
> >
> >
>
>
 |
 |