Re: select max

I'm afraid both your posts have been hit by the Internet time-warp effect - there have been two or three other intervening posts ahead of yours elucidating and
elaborating.

One detail though about the stats - with the available information they do NOT prove irrevocably that the minimum number of data items have been reviewed because:

1. Consistent read stats can actually be incorrect by the odd block or two and
2. The index in question had 1,000 rows with a very short key - with a 16K block size the index could have been contained in 3 blocks

I don't wish to sound snappy, but you might review the argument you have put forward that your test prove anything -

    your 'select max(empno)' claims a full index scan     you 'select empno' claims a full TABLE scan. You cannot therefore use the consistent block get count as a 'proof' that the first query is stopping after the first row. Your argument would of course be valid if your 'select empno' also showed a full INDEX scan.

In passing - one of the posts that has not reached you pointed out that Oracle 8.1.5 enhances its explanation to 'index full scan (min/max)' to indicate explicitly that it is using a special trick.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

fumi wrote in message <7o4m8k$a2j$5_at_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
>---------
> 7369
> 7499
> 7521
> 7566
> 7654
> 7698
> 7782
> 7788
> 7839
> 7844
> 7876
> 7900
> 7902
> 7934
>
>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 - 14:21:49 CDT