RE: How does 9.2i pick a path in rule mode
- From: "Naveen, Nahata (IE10)" <Naveen.Nahata@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 10 May 2004 04:19:36 -0700
>references to column from the index, the better. When you write
> a = value
>you refer to 50% of index2 columns, and 33[.33333...] % of columns of
>index1. Index2 therefore looks better.
>When you refer to both a and b, you still refer to 50% of columns of
>index2, but to 67% of columns of index1, which suddenly becomes very
>tempting.
But in one of my small tests, the index with the higher object_id was
considered ir-respective of whether the query was referencing 50% of the
columns of an index or 33.33%
Here is a simple test:
SQL> SELECT * FROM x;
A B C
---------- ---------- ----------
1 2 3
1 2 3
1 2 3
SQL> CREATE INDEX composite_ac ON x(a,c);
Index created.
SQL> CREATE INDEX composite_abc ON x(a,b,c);
Index created.
SQL> SELECT object_name, object_id, object_type FROM all_objects WHERE
object_name LIKE 'COMPOSITE%';
OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------------------ ---------- ------------------
COMPOSITE_AC 282685 INDEX
COMPOSITE_ABC 282686 INDEX
SQL> SET AUTOTRACE TRACEONLY explain
SQL>
SQL> SELECT /*+ RULE */ b FROM x WHERE a = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 INDEX (RANGE SCAN) OF 'COMPOSITE_ABC' (NON-UNIQUE)
SQL> DROP INDEX composite_abc;
Index dropped.
SQL> DROP INDEX composite_ac;
Index dropped.
SQL> CREATE INDEX composite_abc ON x(a,b,c);
Index created.
SQL> CREATE INDEX composite_ac ON x(a,c);
Index created.
SQL> set autotrace off
SQL> SELECT object_name, object_id, object_type FROM all_objects WHERE
object_name LIKE 'COMPOSITE%';
OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------------------ ---------- ------------------
COMPOSITE_AC 282688 INDEX
COMPOSITE_ABC 282687 INDEX
SQL> SET AUTOTRACE TRACEONLY explain
SQL>
SQL> SELECT /*+ RULE */ b FROM x WHERE a = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X'
2 1 INDEX (RANGE SCAN) OF 'COMPOSITE_AC' (NON-UNIQUE)
In the first case, index COMPOSITE_ABC had a higher object_id so it was
used, whereas in the second case COMPOSITE_AC had a higher object_id and so
it was used.
Regards
Naveen
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
