query tuning or give alternate solution- plz

[madhugp]

hi friends,

if i use the following query it hangs.

select * from rm_stkfile where piece_no not in(
select piece_no from rm_purdetl);

here rm_purdetl table consistis of around 74000 rows.
rm_stkfile consists of around 50000 rows.
piece_no is common column in both tables.

what is alternate solution for this.

thanks

[PAVB]

1- You would never ever do a "select *", select just what you need.
2- NOT IN is the worst predicate you can think about
3- I stand corrected, NOT IN of a inline view is the worst predicate you can think about

Please post...
a) Available indexes.
b) Explain plan.

[LKBrwn_DBA]

Check out the explain plan for these:

Select * From Rm_Stkfile 
 Where Piece_No In (
     Select Piece_No From Rm_Stkfile 
     MINUS
     Select Piece_No From Rm_Purdetl);

And this:

Select * From Rm_Stkfile S
 Where NOT EXISTS (
     Select 'True' From Rm_Purdetl P
      Where P.Piece_No = S.Piece_No);