Home » RDBMS Server » Performance Tuning » SQL Query not using Composite Index
SQL Query not using Composite Index [message #158946] Wed, 15 February 2006 03:27 Go to next message
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 Go to previous message
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

Previous Topic: Problem on temporary tablespace
Next Topic: share server& dedicated server
Goto Forum:
  


Current Time: Fri Mar 29 02:23:36 CDT 2024