Originally posted by slimdave
A fast full index scan might be beneficial, but you might have to hint in order to get oracle to use it.
Well any supporting example?

I tried, but result was negative..

Code:
US18_DEV> ed
Wrote file afiedt.buf

  1* Select * from Reporting_Product_Hier RPH where Pin like '%ABHAY%'
US18_DEV> /

no rows selected

Elapsed: 00:00:44.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6212 Card=43169 Bytes=16015699)
   1    0   TABLE ACCESS (FULL) OF 'REPORTING_PRODUCT_HIER' (Cost=6212 Card=43169 Bytes=16015699)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     101917  consistent gets
      85412  physical reads
          0  redo size
       2995  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

US18_DEV> Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ * from Reporting_Product_Hier RPH where Pin like '%ABHAY%';

no rows selected

Elapsed: 00:01:30.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5317 Card=43169 Bytes=16015699)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'REPORTING_PRODUCT_HIER' (Cost=5317 Card=43169 Bytes=16015699)
   2    1     INDEX (RANGE SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=221 Card=43169)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     106795  consistent gets
     104568  physical reads
          0  redo size
       2995  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
Well had it gone for FFS then it would have been pretty good, but i doubt if it would by any chance?

Abhay.