Hello, Not sure whether this problem has been discussed earlier in this forum. Can someone suggest a workaround to the following problem which can be replicated by following the sequence of detailed steps I have given below. In summary, if you have a table with two composite indexes, both with the same fields, but one of them has a field in descending order, then the Optimizer loses track of the correct index to use for a specific query. Thanks. A.J.Andrews See details below: ---------------- SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production SQL> create table tab1 as select * from all_objects; Table created. SQL> set autotrace traceonly explain; SQL> create index tab1ind1 on tab1(owner, object_name); Index created. SQL> analyze table tab1 compute statistics; Table analyzed. SQL> alter session set optimizer_mode=first_rows; Session altered. SQL> select * from tab1 order by owner, object_name; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 Bytes=372324) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 Card=4899 Bytes=372324) 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 Card=4899) ===> Note here that the correct index tab1ind1 is being used for the query. SQL> create index tab1ind2 on tab1(owner, object_name desc); Index created. SQL> analyze table tab1 compute statistics; Table analyzed. SQL> select * from tab1 order by owner, object_name desc; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 Bytes=372324) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 Card=4899 Bytes=372324) 2 1 INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48 Card=4899) ===> Again, the correct index tab1ind2 is being used. SQL> select * from tab1 order by owner, object_name; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899 Bytes=372324) 1 0 SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324) 2 1 TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899 Bytes=372324) ===> But, at this time the Optimizer has forgotten about the existence of tab1ind1 and is going for a full table scan and a sort! Wrong choice! Should be using index tab1ind1. SQL> drop index tab1ind2; Index dropped. SQL> select * from tab1 order by owner, object_name; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 Bytes=372324) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 Card=4899 Bytes=372324) 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 Card=4899) ===> Once tab1ind2 is dropped, the Optimizer remembers the existence of tab1ind1 and chooses the index scan for the very same query for which it used a table scan earlier. This problem is easily reproducible with any two indexes on a table, with both indexes being on the same set of columns, but with one of the indexes having a "desc" requirement on at least one of the columns.
On Mar 15, 3:45 pm, alano...@invera.com wrote: > Hello, > > Not sure whether this problem has been discussed earlier in this > forum. Can someone suggest a workaround to the following problem which > can be replicated by following the sequence of detailed steps I have > given below. In summary, if you have a table with two composite > indexes, both with the same fields, but one of them has a field in > descending order, then the Optimizer loses track of the correct index > to use for a specific query. > > Thanks. > > A.J.Andrews > > See details below: > ---------------- > > SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006 > > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. > > Connected to: > Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production > With the Partitioning, OLAP and Oracle Data Mining options > JServer Release 9.2.0.6.0 - Production > > SQL> create table tab1 as select * from all_objects; > > Table created. > > SQL> set autotrace traceonly explain; > SQL> create index tab1ind1 on tab1(owner, object_name); > > Index created. > > SQL> analyze table tab1 compute statistics; > > Table analyzed. > > SQL> alter session set optimizer_mode=first_rows; > Session altered. > > SQL> select * from tab1 order by owner, object_name; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 > Bytes=372324) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 > Card=4899 Bytes=372324) > > 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 > Card=4899) > > ===> Note here that the correct index tab1ind1 is being used for the > query. > > SQL> create index tab1ind2 on tab1(owner, object_name desc); > > Index created. > > SQL> analyze table tab1 compute statistics; > > Table analyzed. > > SQL> select * from tab1 order by owner, object_name desc; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 > Bytes=372324) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 > Card=4899 Bytes=372324) > > 2 1 INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48 > Card=4899) > > ===> Again, the correct index tab1ind2 is being used. > > SQL> select * from tab1 order by owner, object_name; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899 > Bytes=372324) > > 1 0 SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324) > 2 1 TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899 > Bytes=372324) > > ===> But, at this time the Optimizer has forgotten about the existence > of tab1ind1 and is going for a full table scan and a sort! Wrong > choice! Should be using index tab1ind1. > > SQL> drop index tab1ind2; > > Index dropped. > > SQL> select * from tab1 order by owner, object_name; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 > Bytes=372324) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 > Card=4899 Bytes=372324) > > 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 > Card=4899) > > ===> Once tab1ind2 is dropped, the Optimizer remembers the existence > of tab1ind1 and chooses the index scan for the very same query for > which it used a table scan earlier. > > This problem is easily reproducible with any two indexes on a table, > with both indexes being on the same set of columns, but with one of > the indexes having a "desc" requirement on at least one of the columns. FIRST_ROWS has a tendency to prefer index access, even if the index access that is selected is less than ideal. You should be using DBMS_STATS. With that said, a test on Oracle 10.2.0.2: CREATE TABLE T2 AS SELECT * FROM ALL_OBJECTS; Table created. COMMIT; Commit complete. CREATE INDEX T2_IND1 ON T2(TABLE_OWNER_HERE, OBJECT_NAME); Index created. EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T2',CASCADE=>TRUE); PL/SQL procedure successfully completed. ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; Session altered. SELECT * FROM T2 ORDER BY OWNER, OBJECT_NAME; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); SQL_ID 9jf1t5g0sgzz4, child number 0 ------------------------------------- SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME Plan hash value: 1199450076 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------- | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 13945 | 27890 |00:00:01.12 | 12286 | 210 | | 2 | INDEX FULL SCAN | T2_IND1 | 2 | 13945 | 27890 |00:00:00.55 | 410 | 136 | ---------------------------------------------------------------------------------------------------------- CREATE INDEX T2_IND2 ON T2(TABLE_OWNER_HERE, OBJECT_NAME DESC); Index created. EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T2',CASCADE=>TRUE); PL/SQL procedure successfully completed. SELECT * FROM T2 ORDER BY OWNER, OBJECT_NAME DESC; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); -------------------------------------------------------- | Id | Operation | Name | E-Rows | -------------------------------------------------------- | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 13945 | | 2 | INDEX FULL SCAN | T2_IND2 | 13945 | SELECT * FROM T2 ORDER BY OWNER, OBJECT_NAME; SQL_ID 9jf1t5g0sgzz4, child number 0 ------------------------------------- SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME Plan hash value: 2552596561 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers | Reads | OMem | 1Mem | O/1/M | ------------------------------------------------------------------------------------------------------------------------- | 1 | SORT ORDER BY | | 1 | 13945 | 13945 | 00:00:00.15 | 182 | 37 | 1824K| 650K| 1/0/0| | 2 | TABLE ACCESS FULL| T2 | 1 | 13945 | 13945 | 00:00:00.11 | 182 | 37 | | | | ------------------------------------------------------------------------------------------------------------------------- So, we are consistent so far. ALTER SYSTEM FLUSH SHARED_POOL; DROP INDEX T2_IND2; Let's try a test: SELECT /*+ ALL_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME; SQL_ID b3myw3j5ufp1h, child number 0 ------------------------------------- SELECT /*+ ALL_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME Plan hash value: 2552596561 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers | Reads | OMem | 1Mem | O/1/M | ------------------------------------------------------------------------------------------------------------------------- | 1 | SORT ORDER BY | | 1 | 13945 | 13945 | 00:00:00.25 | 182 | 37 | 1824K| 650K| 1/0/0| | 2 | TABLE ACCESS FULL| T2 | 1 | 13945 | 13945 | 00:00:00.21 | 182 | 37 | | | | ------------------------------------------------------------------------------------------------------------------------- That plan and statistics look to be very similar to the one that we received when both indexes were available to be used by the cost based optimizer. Note how the execution time is 0.25 seconds (or 0.15 seconds when there were two indexes) compared to the 1.12 seconds required when the T2_IND1 index was used. Is it possible that the cost based optimizer recognized that the time required to continue analyzing the various access methods would exceed the time required to return the rows, so the optimizer decided to run with the best plan that it found so far? Let's look at a 10053 trace for the case when there were two indexes available: BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T2 Alias: T2 #Rows: 13945 #Blks: 190 AvgRowLen: 105.00 Index Stats:: Index: T2_IND1 Col#: 1 2 LVLS: 1 #LB: 67 #DK: 12870 LB/K: 1.00 DB/K: 1.00 CLUF: 5859.00 Index: T2_IND2 Col#: 1 14 LVLS: 1 #LB: 69 #DK: 12870 LB/K: 1.00 DB/K: 1.00 CLUF: 5870.00 *************************************** SINGLE TABLE ACCESS PATH Table: T2 Alias: T2 Card: Original: 13945 Rounded: 13945 Computed: 13945.00 Non Adjusted: 13945.00 Access Path: TableScan Cost: 38.54 Resp: 38.54 Degree: 0 Cost_io: 38.00 Cost_cpu: 6791624 Resp_io: 38.00 Resp_cpu: 6791624 Best:: AccessPath: TableScan Cost: 38.54 Degree: 1 Resp: 38.54 Card: 13945.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: T2[T2]#0 ORDER BY sort SORT resource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 187 Row size: 109 Total Rows: 13945 Initial runs: 2 Merge passes: 1 IO Cost / pass: 102 Total IO sort cost: 289 Total CPU sort cost: 25948637 Total Temp space used: 3384000 *********************** Best so far: Table#: 0 cost: 329.5793 card: 13945.0000 bytes: 1255050 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* SORT resource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 187 Row size: 109 Total Rows: 13945 Initial runs: 2 Merge passes: 1 IO Cost / pass: 102 Total IO sort cost: 289 Total CPU sort cost: 25948637 Total Temp space used: 3384000 Final - First Rows Plan: Best join order: 1 Cost: 329.5793 Degree: 1 Card: 13945.0000 Bytes: 1255050 Resc: 329.5793 Resc_io: 327.0000 Resc_cpu: 32740260 Resp: 329.5793 Resp_io: 327.0000 Resc_cpu: 32740260 The total expected cost was 329 - 289 of which was caused by the sort. Now, let's look at the case where only the first index exists (FIRST_ROWS): BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T2 Alias: T2 #Rows: 13945 #Blks: 190 AvgRowLen: 105.00 Index Stats:: Index: T2_IND1 Col#: 1 2 LVLS: 1 #LB: 67 #DK: 12870 LB/K: 1.00 DB/K: 1.00 CLUF: 5859.00 *************************************** SINGLE TABLE ACCESS PATH Table: T2 Alias: T2 Card: Original: 13945 Rounded: 13945 Computed: 13945.00 Non Adjusted: 13945.00 Access Path: TableScan Cost: 38.54 Resp: 38.54 Degree: 0 Cost_io: 38.00 Cost_cpu: 6791624 Resp_io: 38.00 Resp_cpu: 6791624 Best:: AccessPath: TableScan Cost: 38.54 Degree: 1 Resp: 38.54 Card: 13945.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: T2[T2]#0 ORDER BY sort SORT resource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 187 Row size: 109 Total Rows: 13945 Initial runs: 2 Merge passes: 1 IO Cost / pass: 102 Total IO sort cost: 289 Total CPU sort cost: 25948637 Total Temp space used: 3384000 *********************** Best so far: Table#: 0 cost: 329.5793 card: 13945.0000 bytes: 1255050 ****** Recost for ORDER BY (using index) ************ *************************************** SINGLE TABLE ACCESS PATH Table: T2 Alias: T2 Card: Original: 13945 Rounded: 13945 Computed: 13945.00 Non Adjusted: 13945.00 Access Path: TableScan Cost: 38.54 Resp: 38.54 Degree: 0 Cost_io: 38.00 Cost_cpu: 6791624 Resp_io: 38.00 Resp_cpu: 6791624 Access Path: index (FullScan) Index: T2_IND1 resc_io: 5927.00 resc_cpu: 50436325 ix_sel: 1 ix_sel_with_filters: 1 Cost: 5930.97 Resp: 5930.97 Degree: 1 Best:: AccessPath: IndexRange Index: T2_IND1 Cost: 5930.97 Degree: 1 Resp: 5930.97 Card: 13945.00 Bytes: 90 *********************** Join order[1]: T2[T2]#0 *********************** Best so far: Table#: 0 cost: 5930.9733 card: 13945.0000 bytes: 1255050 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* SORT resource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 187 Row size: 109 Total Rows: 13945 Initial runs: 2 Merge passes: 1 IO Cost / pass: 102 Total IO sort cost: 289 Total CPU sort cost: 25948637 Total Temp space used: 3384000 Final - First Rows Plan: Best join order: 1 Cost: 5930.9733 Degree: 1 Card: 13945.0000 Bytes: 1255050 Resc: 5930.9733 Resc_io: 5927.0000 Resc_cpu: 50436325 Resp: 5930.9733 Resp_io: 5927.0000 Resc_cpu: 50436325 The cost in this case is 5930, which is a bit higher than the cost of 329 that was predicted for the table access with sort operation. It appeared that when there were two indexes on the table, Oracle did not even bother checking the index access paths, as it expected the time to calculate the best path would be more than the time to return the data. Paraphrased from "Cost-Based Oracle Fundamentals" (very likely the best book to read and re-read when trying to determine what is happening): "The optimizer may decide after testing a few join orders that the cost of executing the query is so small that it should discontinue checking join orders - as soon as the time spent checking join orders exceeds the predicted run time, Oracle runs with the best plan obtained so far. This means that if Oracle happens to pick an unsuitable starting table because it calculated the cardinality wrong, the standard permutation cycle of joining tables can cause problems." It does not look like a bug to me, but instead an optimization. Charles Hooper PC Support Specialist K&M Machine-Fabricating, Inc.
Thanks for the reply. The fact remains that in the situation I have described, the Oracle Optimizer loses track of the first of the indexes (depending on the order of creation). My earlier example has a table of about 14,000 rows, where your argument on the possible lower cost of a table scan (as opposed to an index scan) might hold water. But we have a production machine where the same problem occurs on a table of about 300,000 rows. There is no way the Optimizer can argue that a table scan and a subsequent sort would be better. In fact, I also tried to force the use of the index on the table, by using the Index hint. With both indexes in place, for a query requiring the use of the "forgotten index", the optimizer goes for the wrong index, retrieves the rows through the index and then does a sort to get the rows in the required order! See below: SQL> select /*+ index(tab1) */ * from tab1 order by owner, object_name desc; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DFIRST_ROWS (Cost=3D2442 Card=3D4137 B ytes=3D310275) 1 0 SORT (ORDER BY) (Cost=3D2442 Card=3D4137 Bytes=3D310275) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (TABLE) (Cost=3D23 16 Card=3D4137 Bytes=3D310275) 3 2 INDEX (FULL SCAN) OF 'TAB1IND1' (INDEX) (Cost=3D43 Card=3D 4137) On Mar 15, 6:15 pm, "Charles Hooper" <hooperc2...@yahoo.com> wrote: > FIRST_ROWS has a tendency to prefer index access, even if the index > access that is selected is less than ideal. You should be using > DBMS_STATS. With that said, a test on Oracle 10.2.0.2: > > CREATE TABLE T2 AS > SELECT > * > FROM > ALL_OBJECTS; > > Table created. > > COMMIT; > > Commit complete. > > CREATE INDEX T2_IND1 ON T2(TABLE_OWNER_HERE, OBJECT_NAME); > > Index created. > > EXEC > DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>'TABLE_OWNER_HERE',TABNAME=3D>'T= 2',CAS=ADCADE=3D>TRUE); > > PL/SQL procedure successfully completed. > > ALTER SESSION SET OPTIMIZER_MODE=3DFIRST_ROWS; > > Session altered. > > SELECT > * > FROM > T2 > ORDER BY > OWNER, > OBJECT_NAME; > > SELECT > * > FROM > TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); > > SQL_ID 9jf1t5g0sgzz4, child number 0 > ------------------------------------- > SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME > > Plan hash value: 1199450076 > > -------------------------------------------------------------------------= --=AD------------------------------- > | Id | Operation | Name | Starts | E-Rows | A- > Rows | A-Time | Buffers | Reads | > -------------------------------------------------------------------------= --=AD------------------------------- > | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 13945 | > 27890 |00:00:01.12 | 12286 | 210 | > | 2 | INDEX FULL SCAN | T2_IND1 | 2 | 13945 | > 27890 |00:00:00.55 | 410 | 136 | > -------------------------------------------------------------------------= --=AD------------------------------- > > CREATE INDEX T2_IND2 ON T2(TABLE_OWNER_HERE, OBJECT_NAME DESC); > > Index created. > > EXEC > DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>'TABLE_OWNER_HERE',TABNAME=3D>'T= 2',CAS=ADCADE=3D>TRUE); > > PL/SQL procedure successfully completed. > > SELECT > * > FROM > T2 > ORDER BY > OWNER, > OBJECT_NAME DESC; > > SELECT > * > FROM > TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); > > -------------------------------------------------------- > | Id | Operation | Name | E-Rows | > -------------------------------------------------------- > | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 13945 | > | 2 | INDEX FULL SCAN | T2_IND2 | 13945 | > > SELECT > * > FROM > T2 > ORDER BY > OWNER, > OBJECT_NAME; > > SQL_ID 9jf1t5g0sgzz4, child number 0 > ------------------------------------- > SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME > > Plan hash value: 2552596561 > > -------------------------------------------------------------------------= --=AD---------------------------------------------- > | Id | Operation | Name | Starts | E-Rows | A-Rows | A- > Time | Buffers | Reads | OMem | 1Mem | O/1/M | > -------------------------------------------------------------------------= --=AD---------------------------------------------- > | 1 | SORT ORDER BY | | 1 | 13945 | 13945 | > 00:00:00.15 | 182 | 37 | 1824K| 650K| 1/0/0| > | 2 | TABLE ACCESS FULL| T2 | 1 | 13945 | 13945 | > 00:00:00.11 | 182 | 37 | | | | > -------------------------------------------------------------------------= --=AD---------------------------------------------- > So, we are consistent so far. > > ALTER SYSTEM FLUSH SHARED_POOL; > > DROP INDEX T2_IND2; > > Let's try a test: > SELECT /*+ ALL_ROWS */ > * > FROM > T2 > ORDER BY > OWNER, > OBJECT_NAME; > > SQL_ID b3myw3j5ufp1h, child number 0 > ------------------------------------- > SELECT /*+ ALL_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME > > Plan hash value: 2552596561 > > -------------------------------------------------------------------------= --=AD---------------------------------------------- > | Id | Operation | Name | Starts | E-Rows | A-Rows | A- > Time | Buffers | Reads | OMem | 1Mem | O/1/M | > -------------------------------------------------------------------------= --=AD---------------------------------------------- > | 1 | SORT ORDER BY | | 1 | 13945 | 13945 | > 00:00:00.25 | 182 | 37 | 1824K| 650K| 1/0/0| > | 2 | TABLE ACCESS FULL| T2 | 1 | 13945 | 13945 | > 00:00:00.21 | 182 | 37 | | | | > -------------------------------------------------------------------------= --=AD---------------------------------------------- > > That plan and statistics look to be very similar to the one that we > received when both indexes were available to be used by the cost based > optimizer. Note how the execution time is 0.25 seconds (or 0.15 > seconds when there were two indexes) compared to the 1.12 seconds > required when the T2_IND1 index was used. Is it possible that the > cost based optimizer recognized that the time required to continue > analyzing the various access methods would exceed the time required to > return the rows, so the optimizer decided to run with the best plan > that it found so far? > > Let's look at a 10053 trace for the case when there were two indexes > available: > BASE STATISTICAL INFORMATION > *********************** > Table Stats:: > Table: T2 Alias: T2 > #Rows: 13945 #Blks: 190 AvgRowLen: 105.00 > Index Stats:: > Index: T2_IND1 Col#: 1 2 > LVLS: 1 #LB: 67 #DK: 12870 LB/K: 1.00 DB/K: 1.00 CLUF: > 5859.00 > Index: T2_IND2 Col#: 1 14 > LVLS: 1 #LB: 69 #DK: 12870 LB/K: 1.00 DB/K: 1.00 CLUF: > 5870.00 > *************************************** > SINGLE TABLE ACCESS PATH > Table: T2 Alias: T2 > Card: Original: 13945 Rounded: 13945 Computed: 13945.00 Non > Adjusted: 13945.00 > Access Path: TableScan > Cost: 38.54 Resp: 38.54 Degree: 0 > Cost_io: 38.00 Cost_cpu: 6791624 > Resp_io: 38.00 Resp_cpu: 6791624 > Best:: AccessPath: TableScan > Cost: 38.54 Degree: 1 Resp: 38.54 Card: 13945.00 Bytes: 0 > *************************************** > OPTIMIZER STATISTICS AND COMPUTATIONS > *************************************** > GENERAL PLANS > *************************************** > Considering cardinality-based initial join order. > Permutations for Starting Table :0 > *********************** > Join order[1]: T2[T2]#0 > ORDER BY sort > SORT resource Sort statistics > Sort width: 238 Area size: 208896 Max Area size: > 41943040 > Degree: 1 > Blocks to Sort: 187 Row size: 109 Total > Rows: 13945 > Initial runs: 2 Merge passes: 1 IO Cost / > pass: 102 > Total > ... > > read more =BB- Hide quoted text - > > - Show quoted text -
On Mar 16, 2:28 pm, alano...@invera.com wrote: > Thanks for the reply. > The fact remains that in the situation I have described, the Oracle > Optimizer loses track of the first of the indexes (depending on the > order of creation). My earlier example has a table of about 14,000 > rows, where your argument on the possible lower cost of a table scan > (as opposed to an index scan) might hold water. But we have a > production machine where the same problem occurs on a table of about > 300,000 rows. There is no way the Optimizer can argue that a table > scan and a subsequent sort would be better. > > In fact, I also tried to force the use of the index on the table, by > using the Index hint. With both indexes in place, for a query > requiring the use of the "forgotten index", the optimizer goes for the > wrong index, retrieves the rows through the index and then does a sort > to get the rows in the required order! See below: > > SQL> select /*+ index(tab1) */ * from tab1 order by owner, object_name > desc; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2442 Card=4137 B > ytes=310275) > > 1 0 SORT (ORDER BY) (Cost=2442 Card=4137 Bytes=310275) > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (TABLE) (Cost=23 > 16 Card=4137 Bytes=310275) > > 3 2 INDEX (FULL SCAN) OF 'TAB1IND1' (INDEX) (Cost=43 Card= > 4137) The hint that you are looking for is likely this one: /*+ INDEX(TAB1 TAB1IND2) */ I believe what I was suggesting is this: "Is it possible that the cost based optimizer recognized that the _time_required_to_continue_analyzing_the_various_access_methods_ would exceed the {predicted} time required to return the rows, so the optimizer decided to run with the best plan that it found so far?" This feature of the optimizer may have over-ruled the usual approach of using an indexed access path, if available, when the optimizer is set to FIRST_ROWS. If you look carefully at the two partial 10053 traces that I previously supplied, it appears that Oracle short- circuited before checking the cost of the index access paths. Tests with DBMS Xplan -------------------- Unhinted, both indexes in place: SELECT T2.* FROM T2 ORDER BY OWNER, OBJECT_NAME DESC ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers | Reads | OMem | 1Mem | O/1/M | ------------------------------------------------------------------------------------------------------------------------- | 1 | SORT ORDER BY | | 1 | 13945 | 13945 | 00:00:00.04 | 182 | 178 | 1895K| 658K| 1/0/0| | 2 | TABLE ACCESS FULL| T2 | 1 | 13945 | 13945 | 00:00:00.01 | 182 | 178 | | | | ------------------------------------------------------------------------------------------------------------------------- Hinted, with only the table name/alias, both indexes in place, T2_IND1 created before T2_IND2: SELECT /*+ INDEX(T2) */ T2.* FROM T2 ORDER BY OWNER, OBJECT_NAME DESC ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------- | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 13945 | 13945 |00:00:00.83 | 6161 | 248 | | 2 | INDEX FULL SCAN | T2_IND2 | 1 | 13945 | 13945 |00:00:00.21 | 209 | 70 | ---------------------------------------------------------------------------------------------------------- Hinted, with only the table name/alias, both indexes in place, T2_IND1 created before T2_IND2: SELECT /*+ INDEX(T2 T2_IND1) */ T2.* FROM T2 ORDER BY OWNER, OBJECT_NAME DESC -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M | -------------------------------------------------------------------------------------------------------------------------------------- | 1 | SORT ORDER BY | | 1 | 13945 | 13945 |00:00:01.15 | 5927 | 246 | 1895K| 658K| 1/0/0| | 2 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 13945 | 13945 |00:00:01.11 | 5927 | 246 | | | | | 3 | INDEX FULL SCAN | T2_IND1 | 1 | 13945 | 13945 |00:00:00.33 | 68 | 68 | | | | Hinted, with only the table name/alias, both indexes in place, T2_IND1 created before T2_IND2, drop index T2_IND1 and recreate that index: SELECT /*+ INDEX(T2) */ T2.* FROM T2 ORDER BY OWNER, OBJECT_NAME DESC -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M | -------------------------------------------------------------------------------------------------------------------------------------- | 1 | SORT ORDER BY | | 1 | 13945 | 13945 |00:00:00.36 | 5927 | 68 | 1895K| 658K| 1/0/0| | 2 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 13945 | 13945 |00:00:00.31 | 5927 | 68 | | | | | 3 | INDEX FULL SCAN | T2_IND1 | 1 | 13945 | 13945 |00:00:00.25 | 68 | 68 | | | | -------------------------------------------------------------------------------------------------------------------------------------- If you know more about the execute plan than Oracle, provide hints to Oracle to guide the execution plan so that it does not prematurely short-circuit - just watch out during the next upgrade/patch to Oracle for unexpected behavior changes. Charles Hooper PC Support Specialist K&M Machine-Fabricating, Inc.
<alanolya@invera.com> wrote in message news:1173987941.871534.19430@l75g2000hse.googlegroups.com... > Hello, > > Not sure whether this problem has been discussed earlier in this > forum. Can someone suggest a workaround to the following problem which > can be replicated by following the sequence of detailed steps I have > given below. In summary, if you have a table with two composite > indexes, both with the same fields, but one of them has a field in > descending order, then the Optimizer loses track of the correct index > to use for a specific query. > > Thanks. > > A.J.Andrews > > See details below: > ---------------- > > SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006 > > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. > > > Connected to: > Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production > With the Partitioning, OLAP and Oracle Data Mining options > JServer Release 9.2.0.6.0 - Production > > SQL> create table tab1 as select * from all_objects; > > Table created. > > SQL> set autotrace traceonly explain; > SQL> create index tab1ind1 on tab1(owner, object_name); > > Index created. > > SQL> analyze table tab1 compute statistics; > > Table analyzed. > > SQL> alter session set optimizer_mode=first_rows; > Session altered. > > SQL> select * from tab1 order by owner, object_name; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 > Bytes=372324) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 > Card=4899 Bytes=372324) > > 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 > Card=4899) > > ===> Note here that the correct index tab1ind1 is being used for the > query. > > > SQL> create index tab1ind2 on tab1(owner, object_name desc); > > Index created. > > SQL> analyze table tab1 compute statistics; > > Table analyzed. > > SQL> select * from tab1 order by owner, object_name desc; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 > Bytes=372324) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 > Card=4899 Bytes=372324) > > 2 1 INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48 > Card=4899) > > ===> Again, the correct index tab1ind2 is being used. > > > SQL> select * from tab1 order by owner, object_name; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899 > Bytes=372324) > > 1 0 SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324) > 2 1 TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899 > Bytes=372324) > > ===> But, at this time the Optimizer has forgotten about the existence > of tab1ind1 and is going for a full table scan and a sort! Wrong > choice! Should be using index tab1ind1. > > > SQL> drop index tab1ind2; > > Index dropped. > > SQL> select * from tab1 order by owner, object_name; > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899 > Bytes=372324) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172 > Card=4899 Bytes=372324) > > 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46 > Card=4899) > > ===> Once tab1ind2 is dropped, the Optimizer remembers the existence > of tab1ind1 and chooses the index scan for the very same query for > which it used a table scan earlier. > > > This problem is easily reproducible with any two indexes on a table, > with both indexes being on the same set of columns, but with one of > the indexes having a "desc" requirement on at least one of the columns. > Having run a couple of tests on 9.2.0.8 and 10.2.0.1 it looks like a bug, If you have two indexes as described, then Oracle "loses" the first one you created when working out the 'single table access path'. You have a perfect test case - raise an SR with Oracle, and send them the test case. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html