-
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?
-
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
-
How about replacing your ABC index with a BCA index?
-
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...
-
In case of concatenated index, if you are missing leading column then it'll not use the index.
-
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
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|