| Performance Issue [message #513850] |
Wed, 29 June 2011 12:36  |
 |
a_oracle
Messages: 98
Registered: November 2010
|
Member |
|
|
Firstly I am very sorry for not following the posting guidelines but I am unable to provide the explain plan for the below issue as the same is happening in production database and we dont have access to it create a explain plan  . Anyhow, I have tried hard to explain the issue below and request your expertise on the same.
I have a table T which is partitioned using date column. So for each day, the table has around 22k records being entered for each day. total records in the table nearly 191000
There is an unique key composite index on (col1,col2,col3,col4) columns
Now, I have a query as
select col1,col2...,col4 from T t1 where calendar_dt = sysdate
and <some condition>
and ....<some condition>
and exists (select 1
from T2 t2
where t1.col1 = t2.col2
and t1.col2 = t2.col3
group by t1.col1,t1.col2
having count(1)=1);
Now, this query needs to be tuned as the same is takes around 2 minutes for execution. For the same, i ammended the above to somthing like:
select col1,col2,,.col4 from (
select col1,
col2...,
col4.
count(t1.col1) over(partition by t1.col1,t1.col2) tot_cnt
from T t1,T2 t2 where t1.calendar_dt = sysdate
and <some condition>
and ....<some condition>
and t1.col1 = t2.col2
and t1.col2 = t2.col3) where tot_cnt =1;
The ammended query is amazingly fast and gets executed in les than a second. But, below are the confusions that i have:
The trace shows that there for the original query it considers the unique key index on col1 and col2 and for the ammended query the same table undergoes a FULL TABLE SCAN and yet it is faster.
the information is something like:
Partition Range Single T <rows 1> <some bytes> <cost which is higer than the original query>
Full table scan T <rows 1> <some bytes> <cost which is higher than the original query>
Well the above plan I get is from TOAD yb simply selcting the autotrace tab and dont know if we can trust it (I am sure Michel is not going to like it)
So,
Qstn1: Is full table scan refers the partitioned part only so it is faster?
Qstn2: If the original query is prefixed with a FULL hint then it is also faster and retrives the records in a second with the same plan as of below. So, which one should i opt, the ammended query or the original query with the a simple /*+FULL(<table T>) */?
Or finally, all my approach above is not correct 
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
