slow join

I have two tables, master and detail table for orders. Master table has 12,000 rows, and detail table has 450,000 rows.

This query returns 30 rows:

1. select * from master where status='REL'

It takes 250 msecs to finish the query.

But this takes 15 - 20 seconds:

2) select * from master M, detl D where M.ord_no=D.ord_no and M.status='REL'

But when I enter

3) select * from detl where ord_no in (13,4325,564, ..... ) - and so 30 order_no values, it takes 250 msecs.

ord_no is primary key in master tabel, and in detail table primary key is (ord_no, line_no).

Why is second query so slow? As I see it, it takes 250 msecs to filter master rows, and another 250 msecs to find detail rows. That's 0.5 seconds, not 15-20. Received on Fri Oct 19 2001 - 15:03:01 CEST