Tuning a Sub Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Tuning a Sub Query

  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