Re: Oracle Myths

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?

>
> 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
>

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam