| SQL Query not using Composite Index [message #158946] |
Wed, 15 February 2006 03:27  |
ssankar1975
Messages: 1
Registered: February 2006
Location: Chennai
|
Junior Member |
|
|
Hi,
Please look at the below query:
SELECT pde.participant_uid
,pde.award_code
,pde.award_type
,SUM(decode(pde.distribution_type
,'FORFEITURE'
,pde.forfeited_quantity *
pde.sold_price * cc.rate
,pde.distributed_quantity *
pde.sold_price * cc.rate)) AS gross_Amt_pref_Curr
FROM part_distribution_exec pde
,currency_conversion cc
,currency off_curr
WHERE pde.participant_uid = 4105
AND off_curr.currency_iso_code =
pde.offering_currency_iso_code
AND cc.from_currency_uid = off_curr.currency_uid
AND cc.to_currency_uid = 1
AND cc.latest_flag = 'Y'
GROUP BY pde.participant_uid
,pde.award_code
,pde.award_type
In oracle 9i, i"ve executed this above query, it takes 6 seconds and the cost is 616, this is due to non usage of the composite index, Currency_conversion_idx(From_currency_uid, To_currency_uid, Latest_flag). I wonder why this index is not used while executing the above query. So, I've dropped the index and recreated it. Now, the query is using this index. After inserting many rows or say in 1 days time, if the same query is executed, again the query is not using the index. So everyday, the index should be dropped and recreated.
I don't want this drop and recreation of index daily, I need a permanent solution for this.
Can anyone tell me, Why this index goes stale after a period of time???? Please take some time and Solve this issue.
-Sankar
|
|
|
|
| Re: SQL Query not using Composite Index [message #158953 is a reply to message #158946] |
Wed, 15 February 2006 03:54  |
meguesswho
Messages: 7
Registered: February 2006
|
Junior Member |
|
|
The issue might be due to the following possibilities,
1. That CBO doesnt get latest stats (assuming you dont have optimiser_mode=rule)
AND/OR
2. the selectivity of the index is poor i.e. number of rows per distinct key is high hence sometimes CBO thinks it is better not to use index.
You can address first one by collecting stats using DBMS_STATS (Oracle suggested) or analyse for the table/index involved possibly every night.
You can address the second one partially by passing hint to use the appropriate index if you are sure that selectivity is not really poor and you will benefit using that index. Although not using index is not a bad thing all the time.
Regards
MS
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
