Re: Index usage ??

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3C62EEFC.62C4_at_yahoo.com>...
> 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

Hi,

the index is not used as you want, because there's no predicate in the where clause.
BTW, there's no table access.

Regards,
Andreas Received on Fri Feb 08 2002 - 02:49:32 CST