Order of columns in where clause to use index?

[ssmith]

Should the order of the columns in the where clause be the same order as the index, so the index is used?
e.g.
A table called test, with the following columns:
test_id
user_id
user_name
date_submitted
etc.
There is an index on test_id and user_id, in that order.
If I now do a 'select * from test where user_id=? and test_id=?, will it use the index. I have always been told that when using where clauses the columns order must reflect the index order, but my colleague is now telling me this is not true, and you can now specify any order in the where clause.
I am using Oracle version 8.1.5.

[nealh]

It shouldn't make any difference at all what order you put the items in the where clause. It used to (and may still do on occasions) matter which order you put the tables in the from clause.

I have worked for a company where they used to create indexes on columns (a, b) and (b,a) because they held precisely the same view. But it was complete nonsense and I proved it by running trace against two statements which had the order of where clause items reversed. There was no difference in the plan generated by Oracle.

[padmam]

Hi, 10th May 2001 16:47 hrs chennai

say for a you have created index for a table having columns
a,b,c,d.

If you have created Index on a,b,c i.e composite index and if you use b column in a SQL statement then it wont use indexing.

if you use the first column a alone or as a,b,c in condition as required then the index will be used.

as for as Nealh says i contradict do you meant to say indexing was not used. ?

How mcuh record you had in the table did you analyze the index usage ?

Cheers

Padmam

[Heath]

Doesn't it depend upon whether you are using rule-based or cost-based optimization? Rule-based used to be the way the optimizer worked, so the order in which the columns were listed in the WHERE clause mattered for evaluating the rules? An now, since CBO is the preferred method, it no longer really matters? Or is my memory failing me at the moment.

The above only applies to WHERE clauses containing all of the columns in the composite index. Padmam's comment about how composite indexes will be used if only a subset of the columns in it are in the WHERE clause is right on the money.

Heath

[tamilselvan]

In order to use Index, the order of columns in where clause should be same as Indexed columns.

Otherwise, use Index Name in the HINT clause in the Select statement.

[jmodic]

Originally posted by tamilselvan
In order to use Index, the order of columns in where clause should be same as Indexed columns.

?????

Order of columns in WHERE clause is irrelevant regarding the index usage.

Here is a simple example on schema SCOTT, using rule base optimizer. Note that the index is used in both cases, regardless of the column order in the WHERE clause:

SQL> connect scott/tiger@ab81
Connected.
SQL> CREATE INDEX emp_sal_comm ON emp (sal, comm);

Index created.

SQL> set autotrace traceonly explain
SQL> SELECT * FROM emp
2 WHERE comm > 0 AND sal > 1000;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMP_SAL_COMM' (NON-UNIQUE)



SQL> SELECT * FROM emp
2 WHERE sal > 1000 AND comm > 0;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMP_SAL_COMM' (NON-UNIQUE)