Re: select max

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 4 Aug 1999 08:59:14 GMT
Message-ID: <7o8vd2$s49$2@news.seed.net.tw>

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:933622100.10386.0.nnrp-08.9e984b29_at_news.demon.co.uk...
> 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:
>
> a) Consistent read stats can actually be
> incorrect by the odd block or two and
>
> b) 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.

It's my negligence.
Let me show in a different way.
This time, I use a table with 10000 rows, on Persional Oracle 8.0.4. Each statement claims a INDEX SCAN (FULL or RANGE) and a SORT (AGGREGATE).

"select max(id)" just takes 2 reads, as anticipant.

"select count(id) from test where id>0" forces Oracle to scan the whole index. It takes 77 (or more, if there are less cached buffers) reads to complete.

"select /*+ index(test pk_test) */ count(id) from test" uses a hint to force Oracle to FULL SCAN the index.
It takes 77 reads to complete, too.

After analyzing the table, "select count(id) from test" takes 85 reads. Since Oracle needs to get some information. It will takes 77 reads when immediately running again.

The following is the script and result:

create table test (id number, constraint pk_test primary key (id), text varchar2(80)); begin
  for i in 1..10000 loop
    insert into test values (i, rpad('test', 80));     if mod(i, 500)=0 then
      commit;
    end if;
  end loop;
end;
/

I do some test here, cause some block are cached in the Buffer Cache.

SQL> set autotrace on
SQL> select max(id) from test;

MAX(ID)

10000

Execution Plan

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

          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        574  bytes sent via SQL*Net to client
        652  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 count(id) from test where id>0;

COUNT(ID)

10000

Execution Plan

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

          0  recursive calls
          0  db block gets
         77  consistent gets
          0  physical reads
          0  redo size
        577  bytes sent via SQL*Net to client
        665  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 /*+ index(test pk_test) */ count(id) from test;

COUNT(ID)

10000

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1 Bytes=273) 1 0 SORT (AGGREGATE)

   2    1     INDEX (FULL SCAN) OF 'PK_TEST' (UNIQUE) (Cost=26 Card=21
           Bytes=273)

Statistics

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

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select count(id) from test;

COUNT(ID)

10000

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=77 Card=1 Bytes=1300
          00)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN) OF 'PK_TEST' (UNIQUE) (Cost=77 Card=10
          000 Bytes=130000)

Statistics

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

Received on Wed Aug 04 1999 - 03:59:14 CDT