I've jumped this question to the top of the queue, ahead of your other question,
because you asked about it again.

Oracle thinks that it can do the full table scan in 4 I/O operations, because
your multiblock read size is 64K. The index range scan alternative would require
one read of the index root block, one or more leaf block reads and one read of a
table block. If the selectivity of the index is good, then the index based
access path should win. If not, then the full table scan should win. This is
probably a border line decision, and Oracle has favoured the full scan.

To check the optimizer on questions like this, it is really useful to get an
event 10053 trace. Set the event at level 1 and parse the query, then look at
the trace file. It reports all the parameter settings and the base statistics
that Oracle takes into account when choosing a plan (and for more complex
queries it also enumerates the join orders considered). That information would
help us to see exactly why Oracle has chosen the full table scan for this query.


-----Original Message-----
Sent: Thursday, 17 August 2000 1:02


Another question on the optimizer.

We have a table with a pk index on a synthetic key and another index
(concatenated with two different columns) on the foreign key.  The schema
has been analyzed using DBMS_UTILITY.ANALYZE_SCHEMA(,'COMPUTE')
command.

fk index is on

		INTERNAL_REQUST_ID,PARTITION_ID

The table  has about 4863 rows. Its on Oracle 8.1.5 on Tru64.


The following query, INTERNAL-REQUST_ID, is the FK key. But does not seem to
be using the FK index
I was of the impression that Optimizer required the first column in the
where clause to match the first column of the Index.

SQL> SELECT ALL  INTERNAL_REQITM_ID,
  2    INTERNAL_REQUST_ID,
  3    ITEM_ID
  4  FROM
  5    REQUEST_ITEM WHERE
  6    INTERNAL_REQUST_ID =  31177;

INTERNAL_REQITM_ID INTERNAL_REQUST_ID   ITEM_ID
------------------ ------------------ ---------
                21              31177      1001


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=52)
   1    0   PARTITION RANGE (ALL)
   2    1     TABLE ACCESS (FULL) OF 'REQUEST_ITEM' (Cost=4 Card=1 Bytes=52)