Oracle Optmizer does not use the right index (Oracle 9.2.0.6)
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.
alanolya
comp.databases.oracle.server
22978 articles. 
1 followers.
4 Replies
1003 Views
Similar ArticlesOn 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.
hooperc2000Thanks 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
jonathan5683