[QUOTE][i]Originally posted by sonaliak [/i]
[B]Here is the link to the Oracle documentation page which says:

Path 4: Single Row by Unique or Primary Key
This access path is available if the statement's WHERE clause uses all columns of a unique or primary key in equality conditions. For composite keys, the equality conditions must be combined with AND operators. To execute the statement, Oracle performs a unique scan on the index on the unique or primary key to retrieve a single rowid, and then accesses the table by that rowid.

So optimizer does not use unique key constraint (index) if all the columns are not in where clause.
Here is the link:
[url]http://technet.oracle.com/docs/products/oracle8i/doc_index.htm[/url]
[/B][/QUOTE]
Sonaliak,

What documentation you quoted says is totaly correct, but you have to read it very precisely! And what it says about unique constraints is valid for unique indexes as well.

This paragraph is talking about one specific access path: * Single Row by Unique or Primary Key* aka "unique scan". It is selfunderstandable that for unique identification of a single row from composite unique index you must provide values for all indexed columns with "AND" ralations and equality operator. However if you reference a leading column of an unique constraint (or of an underlying index) the optimizer can still use index, however it uses a different access path - *index range scan*, since there might be more than a single row that can satisfiy this condition.

Here is a simple proof:

SQL> set autotrace on explain
SQL> CREATE TABLE test1 (c1 NUMBER, c2 NUMBER,
2 CONSTRAINT uk_test1 UNIQUE (c1, c2));

Table created.

SQL> SELECT * FROM test1 WHERE c1=0;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'UK_TEST1' (UNIQUE)