Make query use an index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Make query use an index

  1. #1
    Join Date
    Jan 2001
    Posts
    216

    Make query use an index

    Columns A, B, C of my table are indexed together as one index. One of the queries uses only B and C in the where clause. The query is doing a full table scan on the table and is very slow. If I add column A to the where clause, it executes extremely fast and uses the combined index.

    What can be done in such a scenario? Is creating a separate index just for B and C the only way out? Can I not trick my query into using the super index (A, B, C) without using column A in the query?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    version?
    Yes, you can make your query use the index using the +INDEX hint. While it would use the index, it might actually make your query slower...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about replacing your ABC index with a BCA index?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Jan 2001
    Posts
    216
    Thanks for the responses. I cannot really replace the index as this is a live system and a very huge one, wont know what other queries will be affected if we change the order. This particular query is being run through Crystal Reports. Am testing the index hint through sqlplus and once I get it working and see an improvement, will implement it in Crystal. Worst case, have to create another index...

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    Arrow

    In case of concatenated index, if you are missing leading column then it'll not use the index.
    http://www.perf-engg.com
    A performance engineering forum

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by malay_biswal
    In case of concatenated index, if you are missing leading column then it'll not use the index.
    depends on the version, that's why I was asking...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by chikkodi
    Thanks for the responses. I cannot really replace the index as this is a live system and a very huge one, wont know what other queries will be affected if we change the order. This particular query is being run through Crystal Reports. Am testing the index hint through sqlplus and once I get it working and see an improvement, will implement it in Crystal. Worst case, have to create another index...
    How are you going to create another index on a system that is too busy to replace an index?
    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