composite index....when used?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: composite index....when used?

  1. #1
    Join Date
    Mar 2002
    Posts
    301

    composite index....when used?

    Hi,

    I create a composite index IDX on (col1,col2,col3,...).
    Witch queries will use this index?


    For example following queries will use IDX?
    select..... where col1=val1; or
    select..... where col2=val2 and col3=val3;
    ....


    Oracle version V8i/9i
    Thank's
    Bensmail

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    The first query would use index becuase col1 is the leading column in the composite index.

    regards
    anandkl
    anandkl

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Just curious,

    I thought the 9i optimizer would use the 2nd or 3rd key alone in a composite index.

    Ken

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    I read in oracle-base.com the query use the index in all cases (if it can) in Oracle V 9i (INDEX SKIPPING)
    I hope i understand the contents.


    Thank's
    Bensmail

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Yup, an index skip scan can be used where the query isn't selective on the leading column(s) of the index, but you'd have to make sure that there are few distinct values for those leading columns. A fast full index scan could also be used, I guess.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by bensmail
    I read in oracle-base.com the query use the index in all cases (if it can) in Oracle V 9i (INDEX SKIPPING)
    I hope i understand the contents.


    Thank's
    Bensmail
    indeed you can sometimes merily skip scan your way through a query
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    This may help me with a problem I'm having rebuilding an index online (key too big error).

    I have INDEX123
    TABLE1
    COLUMN1
    COLUMN2
    COLUMN3

    IF I create INDEX321 (9i, so online)
    TABLE1
    COLUMN3
    COLUMN2
    COLUMN1

    Then drop INDEX123

    IF performance holds up with a "SKIP SCAN", I have effectively rebuild online, right?

    Am I missing something?

    I'd test this right now, but my test database has been importing for a 2 weeks and should finish in a few days (clobs take foreeeveeerr to import).

    Ken

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    A fast full index scan could also be used, I guess.
    Only when select list dosent have columns other than indexed..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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