Re: select max

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 2 Aug 1999 17:58:44 GMT
Message-ID: <7o4m8k$a2j$5@news.seed.net.tw>

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:933337782.17446.0.nnrp-13.9e984b29_at_news.demon.co.uk...
>
> The interesting thing about that is that Oracle
> will still do a FULL SCAN on the index, and
> sort all the key values to find the max().
>
> I had hoped for a cunning short-cut by the time
> 8.1 came out. But this means it is still necessary
> to play with (index_desc) hints when data sets
> get large.

Not true.
Take a look on the statistics data.
Although the execution plan shows Oracle will do a FULL SCAN on the index, Oracle actually scans the index, and can stop after fetching the first row.

Look at the following script:

"select empno" takes 8 logical read (db block gets+consistent gets). "select max(empno)" just takes 2 logical read. It shows that Oracle does not scan the completely full index.

SQL> select max(empno) from emp;

MAX(EMPNO)

7934

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'EMP_PRIMARY_KEY' (UNIQUE) Statistics

          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        571  bytes sent via SQL*Net to client
        660  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select empno from emp;

EMPNO

14 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' Statistics

          0  recursive calls
          3  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        693  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

Received on Mon Aug 02 1999 - 12:58:44 CDT