Order of columns in where clause to use index?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Order of columns in where clause to use index?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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.

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    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.

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Index order

    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

    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  4. #4
    Join Date
    Apr 2001
    Posts
    118
    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

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width