-
Tuning a Sub Query
Hi there,
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 :-
old query
SELECT MAX (weight) weight
FROM client.pet_weight
WHERE pet_id = et_id
AND setup_date =
(SELECT MAX (setup_date)
FROM client.pet_weight
WHERE pet_id = et_id)
new query
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
-
Hi
Would you be kind enough to show me your query and plan.
regards
Hrishy
-
Originally Posted by rjsha1
this does a FULL TABLE SCAN of a 263 million row table.
What indexed column or partition key were you hoping to make use of?
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
|