Re: %%How to <efficiently> do: SELECT MAX(Date)...WHERE Date < myParamDate

On Sat, 15 Nov 1997 08:45:28 -0000, "Clint Redwood" <clint_at_kheldar.demon.co.uk> wrote:

>Excuse my ignorance? What is DMAX? I've never seen anything like that in
>oracle.
>
>Out of interest, In my oppinion the first suggestion, using a descending
>index hint, the original poster had left out one important step. The order
>by clause must be set to use the index in question descendingly... see the
>INDEX_DESC hint in the Oracle 7 Performance Tuning guide.
>
>In my experience, this is a very efficidnt way of solving the problem, with
>a single indexed query, requires only one index fetch if the only value you
>want is in the index, or one indexed table access otherwise. The catch is
>that you must check that your explain-plan for your query is using the index
>specified, and is not merge-sorting anything, since that can cause the
>incorrect value to be returned. In this case use a USE_NL hint to ensure
>nested loops.
>
>Clint

DMAX is a Microsoft access function that looks up the maximum value in a column.

Someone else replied with a query to make efficient use of the index with a date literal and I re-read the original question. There are two possible interpretations of the problem.

1. All rdate values are on the first of the month, in which case the question is how to find the value on the first of the month preceding a given date. This can be done by:

select rating
from fred
where rdate = to_date( '01-' || to_char( to_date('15-feb-1997', 'dd-mon-yyyy'), 'mon-yyyy'), 'dd-mon-yyyy' );

The plan for this is
SELECT STATEMENT
  TABLE ACCESS BY INDEX ROWID FRED
    INDEX UNIQUE SCAN FRED_PK 2. rdate values can have any value and we need to find the latest record before a given date.

This can be done with the original statement e.g.

select rating
from fred
where rdate =

   ( select max(rdate)

     from fred fred2
     where fred2.rdate <  to_date('15-feb-1997', 'dd-mon-yyyy') );

The problem is the access path:
SELECT STATEMENT
  TABLE ACCESS BY INDEX ROWID FRED
    INDEX UNIQUE SCAN FRED_PK

      SORT AGGREGATE
        INDEX RANGE SCAN FRED_PK

Two scans are required.

Alternatively use a hint
select /*+ index_desc( fred fred_pk ) */

          rating
from fred
where rdate < to_date('15-feb-1997', 'dd-mon-yyyy') and rownum = 1;

The access path is
SELECT STATEMENT
  COUNT STOPKEY
    TABLE ACCESS BY INDEX ROWID FRED
      INDEX RANGE SCAN DESCENDING FRED_PK No order by is required to invoke this access path. Received on Wed Nov 19 1997 - 00:00:00 CST