Re: Yet another "why is my index not used" question

Hi Michael,
I confirm that the optimizer picks a different access path for the modified version you suggest:

09:57:27 SQL> SELECT * FROM t WHERE
10:03:32   2  vmenmag=18 AND vmemois=200810 and
10:03:37   3  vmenmag=18 AND vmemois=200810;

53710 rows selected.

Elapsed: 00:00:32.28

Execution Plan

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=588 Card=55964 Bytes
           =9290024)

    1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T' (Cost=58
           8 Card=55964 Bytes=9290024)

    2    1     INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE) (Cost=35
           7 Card=1142)

Statistics

           0  recursive calls
           0  db block gets
       28025  consistent gets
        1015  physical reads
           0  redo size
     4154910  bytes sent via SQL*Net to client
       40036  bytes received via SQL*Net from client
        3582  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
       53710  rows processed

> In most apps, I would not use this, but, where this has been used
> before, the query has executed anywhere from 50-500% faster. What
> happens is that CBO determines that it **should** use any index that has
> these columns that are unique or the leading edge because of the
> weighting in the "where-clause".
>
> If you really want to get your "geek" fix, try reading the World
> Intellectual Property Organization (WIPO) patent request for at least
> one of the patents associated with the CBO:
> >>http://www.wipo.int/pctdb/en/wo.jsp?wo=1993014464&IA=US1993000775&DISPLAY=STATUS

[...]

I'll check the reference you mentioned.
Thanks!

Before I run the above test, I tried to execute the hinted version before the original one, to avoid the effect of the buffering, this is the result:

09:50:00 SQL> set autot traceo exp stat
09:50:07 SQL> SELECT --+ index(t)
*
from t
where vmenmag=18 AND
vmemois=200810; 09:50:07 2 09:50:07 3 09:50:07 4 09:50:07 5

53710 rows selected.

Elapsed: 00:00:52.34

Execution Plan

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11647 Card=55964 Byt
           es=9290024)

    1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T' (Cost=11
           647 Card=55964 Bytes=9290024)

    2    1     INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE) (Cost=35
           7 Card=55964)

Statistics

        1430  recursive calls
           0  db block gets
       28248  consistent gets
        1681  physical reads
           0  redo size
     4154910  bytes sent via SQL*Net to client
       40036  bytes received via SQL*Net from client
        3582  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
       53710  rows processed

09:51:01 SQL>
09:51:35 SQL> SELECT *
from t
where vmenmag=18 AND
vmemois=200810; 09:51:36 2 09:51:36 3 09:51:36 4

53710 rows selected.

Elapsed: 00:04:06.20

Execution Plan

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5947 Card=55964 Byte
           s=9290024)

    1    0   TABLE ACCESS (FULL) OF 'T' (Cost=5947 Card=55964 By
           tes=9290024)

Statistics

           0  recursive calls
           0  db block gets
       65643  consistent gets
       60890  physical reads
       16272  redo size
     4262557  bytes sent via SQL*Net to client
       40036  bytes received via SQL*Net from client
        3582  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
       53710  rows processed

Best regards
Dimitre Received on Thu Jan 29 2009 - 03:13:11 CST