Tuning a Sub Query

[rjsha1]

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

[hrishy]

Hi

Would you be kind enough to show me your query and plan.

regards
Hrishy

[WilliamR]

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?