Make query use an index

[chikkodi]

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?

[marist89]

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...

[slimdave]

How about replacing your ABC index with a BCA index?

[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...

[malay_biswal]

In case of concatenated index, if you are missing leading column then it'll not use the index.

[marist89]

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...

[slimdave]

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?