Re: Index usage ??

Ralf wrote:
>
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3C61B6C5.699E_at_yahoo.com>...
> > Ralf wrote:
> > >
> > > I have the following statement:
> > >
> > > select max(value1) from table1
> > >
> > > There is an index "index1 on table1(value1)".
> > >
> > > The plan for the above statement shows me that the database uses an
> > > full index scan and an table access by rowid to retrieve the value.
> > > Why does it behave like this ?
> > >
> > > I tought the index should be sorted ? Then i would navigate at the end
> > > of the index, get the value. I wouldn´t do any full index scan and
> > > would not go to the table to get the value, because its in the index
> > > already.
> > >
> > > Can someone explain, please ?
> > >
> > > Ralf
> >
> > version?
> >
> > later versions of oracle will/can/may show a 'INDEX (MIN/MAX)' in the
> > explain plan which does exactly what you were anticipating.
> >
> > hth
> > connor
>
> Oracle 8.1.7 running on Windows NT
> Table has 21000 entries, index has 4000 distinct values
>
> Ralf

Example:

SQL> create table my_tab ( x number, y varchar2(20));

Table created.

SQL> insert into my_tab
  2 select rownum, rownum
  3 from sys.source$
  4 /

4045345 rows created.

SQL> commit;

Commit complete.

SQL> create index my_tab_ix on my_tab ( x ) nologging;

Index created.

SQL> analyze table my_tab estimate statistics;

Table analyzed.

SQL> set autotrace traceonly explain
SQL> select max(x) from my_tab;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=625 Card=1 Bytes=5)    1 0 SORT (AGGREGATE)

   2    1     INDEX (FULL SCAN (MIN/MAX)) OF 'MY_TAB_IX' (NON-UNIQUE)
          (Cost=625 Card=4042024 Bytes=20210120)

SQL> set autotrace on statistics;
SQL> select max(x) from my_tab;

    MAX(X)

   4045345

Statistics

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

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."