| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Optmizer does not use the right index (Oracle 9.2.0.6)
On Mar 16, 2:28 pm, alano..._at_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 shortcircuited before checking the cost of the index access paths.
Tests with DBMS Xplan
| 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.
Received on Fri Mar 16 2007 - 14:49:04 CDT
 |
 |