DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Using Index scan.

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    Using Index scan.

    Hi Friends,
    I am using oracle 8.1.7.4, solaris 2.7

    I have a table which has got 22M records and created a two composite index.One composite index on (customer_id,market_id,place_id)and this is and unique index and the next composite index is on
    (customer_id,status,market_id).

    When i see the execution plan, it shows me that its using the first compsite index(unique index scan) not the second. Leading column in my query is customer_id.Other columns are not used in the same order

    Can anybody explain, is this normal and optimal behaviour of oracle to use a unique index for faster execution of the query.
    Why is not using the second index.

    regards
    anandkl
    Last edited by anandkl; 01-19-2004 at 03:01 AM.
    anandkl

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Why do u want Oracle to use 2nd Composite Index when its going for UNIQUE SCAN as u said... ( PS Unique Scan is good only when its parent Join Table is Driving one (Or Coming from Driving Table) and uses Appropriate Access Path )...
    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"

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How does the size of the two indexes compare? Is one bigger/smaller than the other?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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