Querying table with no Primary Key

[slimdave]

yes, fast full index scan is a bit tricky somtimes. You might try re-writing as ...

Select * from Reporting_Product_Hier
Where Rowid in
(
Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ rowid from Reporting_Product_Hier RPH where Pin like '%ABHAY%'
);