Yes, the fast full scan wins here because the segment can be read in a single
multiblock read, whereas the unique scan requires two single block reads - one
for the root block and one for the leaf.


-----Original Message-----
Sent: Friday, 1 September 2000 0:50


Is it possible, that fast full scan of primary key index is more effective
than traditional unique scan?

  select /*+ ALL_ROWS */ label
   from tbl
   where id = :x;
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=80)
  INDEX (FAST FULL SCAN) OF TBL__PK (UNIQUE) (Cost=1 Card=2 Bytes=80)

The above shows Explain Plan output for a simple select on a small
index-organized lookup table. Meanwhile FIRST_ROWS hint results in:

SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 Bytes=40)
  INDEX (UNIQUE SCAN) OF PARAM_VALUE_IOT__PK (UNIQUE) (Cost=1 Card=1
Bytes=40)