Re: Super Newbie question part two

Hi! I have an optimization for your subquery.

> the correct sql statement for you last example is:
>
> select tblA.id, tblB.<any_other_column> <-- not max(tblB.date)
> from tblA,tblB
> where tblA.id = tblB.id
> and tblB.date = (select max(date)
> from tblB
> where tblB.id=tblA.id);
>

The subquery above does a range scan on tblB, sorts this subset and then gets the max date (this is what happens when you use max() function).

The subquery can be optimized by using an optimizer hint:

     select tblA.id, tblB.<any_other_column>  <-- not max(tblB.date)
       from tblA,tblB
      where tblA.id = tblB.id
        and tblB.date = (select /*+ INDEX_DESC (tblB index_by_date_on_tblB)
*/ date
                           from tblB 
                          where tblB.id=tblA.id AND ROWNUM = 1);
 

The hint tells the optimizer to do the range scan on the specified index (index_by_date_on_tblB) for the table in a descending manner. Rownum = 1 tells it to stop when it retrieves the first index entry; since it is looking at it descending this is the index entry you want (the max date). No sort is needed, nor does it need to retrieve the entire subset implied in the subquery. It's a bit quicker.

If you wanted the min(date) you'd just use /*+ INDEX ... */ instead.

Do explain plans on each method and you'll see what I'm getting at.

• Dan Clamage dclamage AT idcomm DOT com (anti-spam format)