Re: How to select the <= date row?

From: Chip Dawes <ACD008_at_email.mot.com>
Date: 1996/06/12
Message-ID: <31BF0285.167E_at_email.mot.com>#1/1

The query that starts with

>2) Selecting the max(specified_date) where event_date <= specified_date

> works fine, but it will take a couple of days to wade through 40
> million rows.

is a step in the right direction, you just need to tell oracle to use the specific index (on the date) and then stop after 1 row. like...

SELECT /* INDEX_DESC(eventA event_date_idx) */ date_col FROM eventA
WHERE event_date <= to_date(:t)   

Check out Chapter 5 of the Application Developer's Guide (Tuning SQL Statements), specificly the part on hints like INDEX_DESC

Chip Dawes
Motorola Manufacturing Systems Received on Wed Jun 12 1996 - 00:00:00 CEST