>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 -----------------------------------------------------------------