Re: select max

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 30 Jul 1999 11:32:37 GMT
Message-ID: <37b18c21.174513026@newshost.us.oracle.com>

A copy of this was sent to "Marco Toccafondi" <mt_at_multidatagroup.it> (if that email address didn't require changing) On Fri, 30 Jul 1999 09:37:50 +0200, you wrote:

>Hi,
>does anybody know about the select max performance? How it works? It just
>fetch all the records or use index? With how many records it's better not to
>use it? (We're using Oracle 8i..).
>
>thanks ...
>marco
>
>

it uses an index in most cases when available and finds the answer pretty quickly. You can test simple things like this with 'autotrace' in sqlplus. consider:

Indexes on tkyte.empnos

Index                          Is
Name                           Unique Indexed Columns
------------------------------ ------ -----------------------------------
SYS_C0048272                   Yes    EMPNO

SQL> select count(*) from empnos;

COUNT(*)

1000

SQL> set autotrace on
SQL> select max(empno) from empnos;

MAX(EMPNO)

E00001000

Execution Plan

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

          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        581  bytes sent via SQL*Net to client
        663  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 max( substr(empno,1,length(empno)) ) from empnos;

MAX(SUBSTR(EMPNO,1,LENGTH

E00001000

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMPNOS' Statistics

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

the first max uses the index and reads but 2 blocks (consistent gets). it stops right after finding the max value. the second one, due to the function and not having an index on that function, reads every row in the table (334 blocks read) to find the answer...

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 30 1999 - 06:32:37 CDT