Ok, I wud say it even worses the case..PS.
Abhay.Code:US18_DEV> Select * from Reporting_Product_Hier
2 Where Rowid in
3 (
4 Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ rowid from Reporting_Product_Hier RPH where Pin like '%006309-011%'
5 );
PIN PARENT_PIN FORDATE PH_ITEM_NAME PH_ITEM_DESC P P PH_LEVEL_NAME PH_LEVEL_01_CD
------------------ ------------------ --------- ------------------ ---------------------------------------- - - ---------------------------------------- --------------
006309-011 M3854 29-SEP-03 006309-011 PL1500 6/200 Proc Bd ALL S 8 SKU ZU
Elapsed: 00:00:44.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10843 Card=43169 Bytes=17008586)
1 0 HASH JOIN (Cost=10843 Card=43169 Bytes=17008586)
2 1 INDEX (FAST FULL SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=268 Card=43169 Bytes=690704)
3 1 TABLE ACCESS (FULL) OF 'REPORTING_PRODUCT_HIER' (Cost=6212 Card=863389 Bytes=326361042)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
106823 consistent gets
89707 physical reads
0 redo size
3594 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
US18_DEV> Select * from Reporting_Product_Hier where Pin like '%006309-011%';
PIN PARENT_PIN FORDATE PH_ITEM_NAME PH_ITEM_DESC P P PH_LEVEL_NAME PH_LEVEL_01_CD
------------------ ------------------ --------- ------------------ ---------------------------------------- - - ---------------------------------------- --------------
006309-011 M3854 29-SEP-03 006309-011 PL1500 6/200 Proc Bd ALL S 8 SKU ZU
Elapsed: 00:00:37.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6212 Card=43169 Bytes=16015699)
1 0 TABLE ACCESS (FULL) OF 'REPORTING_PRODUCT_HIER' (Cost=6212Card=43169 Bytes=16015699)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
101918 consistent gets
89710 physical reads
0 redo size
3594 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
US18_DEV> Select * from Reporting_Product_Hier
2 Where pin in
3 (
4 Select /*+ INDEX_FFS(RPH PK_Reporting_Product_Hier) */ pin from Reporting_Product_Hier RPH where Pin like '%006309-011%'
5 );
PIN PARENT_PIN FORDATE PH_ITEM_NAME PH_ITEM_DESC P P PH_LEVEL_NAME PH_LEVEL_01_CD
------------------ ------------------ --------- ------------------ ---------------------------------------- - - ---------------------------------------- --------------
006309-011 M3854 29-SEP-03 006309-011 PL1500 6/200 Proc Bd ALL S 8 SKU ZU
Elapsed: 00:00:44.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6654 Card=43169 Bytes=16404220)
1 0 HASH JOIN (Cost=6654 Card=43169 Bytes=16404220)
2 1 INDEX (FAST FULL SCAN) OF 'PK_REPORTING_PRODUCT_HIER' (UNIQUE) (Cost=268 Card=43169 Bytes=388521)
3 1 TABLE ACCESS (FULL) OF 'REPORTING_PRODUCT_HIER' (Cost=6212 Card=43169 Bytes=16015699)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
106823 consistent gets
89717 physical reads
0 redo size
3594 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed