DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    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

  2. #2
    Join Date
    Jan 2001

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


  3. #3
    Join Date
    Jun 2005
    London, UK
    Quote 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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.