RE: How does 9.2i pick a path in rule mode

From: Naveen, Nahata (IE10) <Naveen.Nahata_at_honeywell.com>
Date: Mon, 10 May 2004 04:19:36 -0700
Message-ID: <77ED2BF75D59D1439F90412CC5B109740BB0EDE6@ie10-sahara.hiso.honeywell.com>

>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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon May 10 2004 - 06:10:12 CDT