query tuning or give alternate solution- plz

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

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.

Check out the explain plan for these:

Code:

---

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

---

And this:

Code:

---

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

---

:D