Re: Oracle Myths

Nuno Souto <nsouto_at_optushome.com.au.nospam> wrote in message news:<3ceb6445$0$15145$afc38c87_at_news.optusnet.com.au>...
> In article <3ceb5bba$0$8514$ed9e5944_at_reading.news.pipex.net>, you said
> (and I quote):
>
> Interesting. WHich version of Oracle is this? I tried this in V7, V8.0
> and V8.1 and it doesn't pick up the index at all in any of these,
> provided there is a reasonable number of rows in the table with similar
> distributions. Also, does it still pick up the index if you do NOT
> select the rowid? That is kept in the index and would be enough for the
> optimizer to bias itself, no?
>

I believe the optimizer will pick up the index regardless of the order in which the columns appear in the index, since the predicates include both. However, try a select where only cur_amount is in the criteria. I guess it would still use range scan in the first example but FFS or even table scan, depending on the version, in the second one.

> >
> > SQL> CREATE INDEX MOSTSEL ON AGLTRANSACT(CUR_AMOUNT,CLIENT);
> >
> > Index created.
> >
> > SQL> analyze table agltransact estimate statistics;
> >
> > Table analyzed.
> >
> > SQL> set autotrace on explain statistics;
> > SQL> select rowid,cur_amount,status
> > 2 from agltransact
> > 3 where client = 'DE'
> > 4 and cur_amount = 293.75;
> >
> > ROWID CUR_AMOUNT S
> > ------------------ ---------- -
> > AAAGbaAALAAACwQAAh 293.75
> > <snip>
> > AAAGbaAAOAAADf6AAh 293.75
> >
> > 50 rows selected.
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=366 Card=369 Bytes=5
> > 166)
> >
> > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=366 C
> > ard=369 Bytes=5166)
> >
> > 2 1 INDEX (RANGE SCAN) OF 'MOSTSEL' (NON-UNIQUE) (Cost=3 Car
> > d=369)
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 36 consistent gets
> > 0 physical reads
> > 0 redo size
> > 3434 bytes sent via SQL*Net to client
> > 740 bytes received via SQL*Net from client
> > 6 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 50 rows processed
> >
> > SQL> drop index mostsel;
> >
> > Index dropped.
> >
> > SQL> create index leastsel on agltransact(client,cur_amount);
> >
> > Index created.
> >
> > SQL> select rowid,cur_amount,status
> > 2 from agltransact
> > 3 where client = 'DE'
> > 4 and cur_amount = 293.75;
> >
> > ROWID CUR_AMOUNT S
> > ------------------ ---------- -
> > AAAGbaAALAAACwQAAh 293.75
> > <snip>
> > AAAGbaAAOAAADf6AAh 293.75
> >
> > 50 rows selected.
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=369 Bytes=516
> > 6)
> >
> > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=2 Car
> > d=369 Bytes=5166)
> >
> > 2 1 INDEX (RANGE SCAN) OF 'LEASTSEL' (NON-UNIQUE) (Cost=1 Ca
> > rd=369)
> >
> >
> >
> >
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 36 consistent gets
> > 0 physical reads
> > 0 redo size
> > 3434 bytes sent via SQL*Net to client
> > 740 bytes received via SQL*Net from client
> > 6 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 50 rows processed
> >
Received on Wed May 22 2002 - 10:55:19 CDT