Re: Indexes and the rule_based optimizer

Werner Adams (adams.werner_at_columbus.co.za) wrote: <snip>
:
: I then issued the following query:
:
: explain plan for
: select *
: from test
: where col1 = 1 and col3 = 1;
:
: explain plan output when index test_ind was created last:
:
: ID P_ID OPERATION OPTIONS OBJECT_NAME
: --- ---- -------------------- --------------- -------------------------
: 0 SELECT STATEMENT
: 1 0 TABLE ACCESS BY ROWID TEST
: 2 1 INDEX RANGE SCAN TEST_IND
:
: I then dropped the primary key, recreated it and issued the same sql code.
:
: explain plan output when primary key was recreated:
:
: ID P_ID OPERATION OPTIONS OBJECT_NAME
: --- ---- -------------------- --------------- -------------------------
: 0 SELECT STATEMENT
: 1 0 TABLE ACCESS BY ROWID TEST
: 2 1 INDEX RANGE SCAN PK_TEST
:
:
:
: The database use the rule-based optimizer. The world according to
: ORACLE7 Server Concepts Manual p13-33, the primary key must always be
: used in scenario explained above.
:
: Does anybody have an idea why this is happening?
:

The rule based optimizer has a "feature". When Oracle determines that there are two indexes that are equal in returning the result set (not by looking at data volumes, mind you, but some other way) it breaks the tie by using the index that was CREATED MOST RECENTLY. Oftentimes, the two indexes are not as equal as Oracle thinks they are. In this situation, using the rule-based optimizer, one way to "solve" the problem is to drop and recreate the index you want it to use.

You can also use hints with the rule-based optimizer, specifically the INDEX hint. i.e. SELECT /*+INDEX (test pk_test)*/ ... (or similar; I don't remember the exact syntax at the moment.)

Joe

-- 
                                   
=-------------------------------------------------------------------------=
Joe Nardone <joe_at_access.digex.net>                  
  "The most beautiful thing we can experience is the mysterious.  It is 
  the source of all true art and all science.  He to whom this emotion
  is a stranger, who can no longer pause to wonder and stand rapt in awe,
  is as good as dead: his eyes are closed." - Einstein