Re: full table scan cheaper than index??

Keith Boulton <kboulton_at_ntlworldgoaway.com> wrote in message news:<_iV37.1$lhj.2228252_at_news.randori.com>...
> >
> > select id, timestamp
> > from my_table
> > where timestamp > to_date('20010701','YYYYMMDD');
> >
> > The table "my_table" has an index, let's call it "my_index", on the column
> > "timestamp".
> >
>
> This query may run slowly because oracle assumes that many rows will be
> returned. Try a first_rows hint:
>
> select /*+ first_rows */ id, timestamp
> from my_table
> where timestamp > to_date('20010701','YYYYMMDD');

Keith, The CBO will ignore the FIRST_ROWS hint anytime it is used on a query that requires Oracle to fetch all rows in order to find the first row to be returned such as in a select distinct:

From the SQL manual:
For example, the optimizer uses the cost-based approach to optimize this statement
for best response time:
SELECT /*+ FIRST_ROWS */ empno, ename, sal, job FROM emp
WHERE empno = 7566;
The optimizer ignores this hint in DELETE and UPDATE statement blocks and in
SELECT statement blocks that contain any of the following syntax: in Set operators (UNION, INTERSECT, MINUS, UNION ALL)  GROUP BY clause
 FOR UPDATE clause
 Aggregate functions
 DISTINCT operator
These statements cannot be optimized for best response time, because Oracle must
retrieve all rows accessed by the statement before returning the first row.
<<<

• Mark D Powell --