Question: Oracle7 query optimizer and indexes

-- 
Hi

I have a question about how the Oracle7 query optimizer decides when
to use an index, when accessing a table.

Consider a table RAB with two indexes, one on A and the other on B.
The values in A are a normal distribution, while B's values are
evenly distibuted, both over [0..999]. Neither index is a unique index.

For the query:

SELECT COUNT(*)
FROM R
WHERE A <> 500 AND B = 105;

Using the cost-based optimizer (having ANALYZEd the table and indexes),
the following execution plan is produced:

EXECUTION PLAN FOR: SELECT COUNT(*) FROM RAB WHERE A <> 500 AND B = 105
OPERATION                                        OPTIONS      OBJECT_NAME
------------------------------------------------ ------------ ---------------
  SORT                                           AGGREGATE
    TABLE ACCESS                                 BY ROWID     RAB

This plan does not exploit any index (which is correct for the index on A,
however, using the index on B would be advantagous). This is simple to force,
using hints as follows (where RAB_B_IDX is the name of the index on B in RAB):

EXECUTION PLAN FOR: SELECT /*+ INDEX(RAB RAB_B_IDX) */ COUNT(*) FROM RAB
 WHERE A <> 500 AND B = 105
OPERATION                                   OPTIONS      OBJECT_NAME
------------------------------------------- ------------ ---------------
  SORT                                      AGGREGATE
    TABLE ACCESS                            BY ROWID     RAB
      INDEX                                 RANGE SCAN   RAB_B_IDX

This execution plan significantly reduces the number of logical reads.
This plan is the same as would be produced by the rule-based optimizer
used in Oracle6 (and Oracle7 if specified). However, the rule-based
optimizer will do some silly things too and just use indexes blindly.

My question is why does Oracle7 not use the index on B in the first
case? Is there a logical explanation? How does one go about finding
explanations for seemingly such trivial queries. A strange thing is
that it does use the index on B if there is no index on A! The concept 
manual and statistics tables don't seem to have any "good" explanation 
for this (unless I am overlooking something).

- Andrew



:--

:Andrew Deacon                       email:  deacon_at_inf.ethz.ch

:Institut fur Informationssysteme    

:Departement Informatik              Phone:  +41 1 632 72 72


:ETH-Zentrum, CH-8092 Zuerich        Fax  :  +41 1 262 39 73