Basic Ques. On Using Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Basic Ques. On Using Index

  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    72

    Basic Ques. On Using Index

    Hai,


    Iam having Com.Index on StockMaster Table as
    ItemId,Batchno,Length,Width,LoadFactor,Twist,Prodtype

    I want to know whether My Query should be in same order ???

    Thanks in advance.

  2. #2
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    I assume you're using thhe cost based optimiser these days.

    No, the columns do not need to appear in your WHERE clause in the same order as the columns do in your index for the index to be used. I've certainly found this to be true in 8.1.7.4 at any rate. However, why take my word for it? Do some explain plans for your query, varying the order in which the columns appear in your WHERE clause.

  3. #3
    Join Date
    Nov 2000
    Location
    San Diego
    Posts
    24

    Orader matters ?

    I have a different understanding. It is the query fields/coulmns that matters rather than the order in which these fields are referenced in the where clause, I guess.

    In Sathy's example:

    SQL> select (whatever fields)
    from StockMaster
    where ItemId = 5000 and
    Batchno = 45 and
    Length = 20 ;

    is same as as the following one.

    SQL> select (whatever fields)
    from StockMaster
    where Length = 20 and
    Batchno = 45 and
    ItemId = 5000 ;

    Both the above queries make use of the index, because the first columns of the index are refrenced in the where clause. Order does not matter.

    If the 'ItemId' is not in the where clause and rest of the index columns are refrenced in the where clause, in order, even then the index will not be utilized for querying.

    What you say guys ?

    -Hidayath

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Orader matters ?

    Originally posted by Hidayath
    If the 'ItemId' is not in the where clause and rest of the index columns are refrenced in the where clause, in order, even then the index will not be utilized for querying.
    Depends ... later versions of Oracle can use the index ... see documentation for "index skip scan"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    Originally posted by hacketta1
    I assume you're using thhe cost based optimiser these days.

    No, the columns do not need to appear in your WHERE clause in the same order as the columns do in your index for the index to be used. I've certainly found this to be true in 8.1.7.4 at any rate. However, why take my word for it? Do some explain plans for your query, varying the order in which the columns appear in your WHERE clause.
    Can u explain abt explain plans ..How to do it..

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