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