Tuning a Sub Query
I have the following sub query a rather long query :-
and trunc(sysdate) = (select min(trunc(event_dtm)) from costedevent_tab where account_num = cdt.ACCOUNT_NUM)
this does a FULL TABLE SCAN of a 263 million row table.
I have seen some posts where they use RANK and OVER and PARTITION BY. How do I encorporate this as and AND statement as the examples I see do this :-
SELECT MAX (weight) weight
WHERE pet_id = et_id
AND setup_date =
(SELECT MAX (setup_date)
WHERE pet_id = et_id)
SELECT weight from (select weight from t where pet_id = :b1
order by pet_id DESC, setup_date DESC, weight DESC )
where rownum = 1
the thing is that my query is a bit longer than this example and I'm not sure if I can do
and trunc(sysdate) = ((select trunc(event_dtm) from xxx where account_num = cdt.account_num order by trunc(event_dtm) DESC)
and rownum = 1) ??????
Any ideas chaps
Would you be kind enough to show me your query and plan.
What indexed column or partition key were you hoping to make use of?
Originally Posted by rjsha1
Click Here to Expand Forum to Full Width