Home » RDBMS Server » Server Administration » Which is faster Join or subquery
Which is faster Join or subquery [message #370886] Tue, 29 February 2000 11:05 Go to next message
Sunil
Messages: 132
Registered: September 1999
Senior Member
Select * from tableA A, tableB B
Where A.key = B.Key

Or

Select * from tableA where key in
(Select key from tableB)

There are about 4 million rows in table A and about 18 million rows in table B

Which method is efficient.
Thanks
Sunil
Re: Which is faster Join or subquery [message #370913 is a reply to message #370886] Mon, 06 March 2000 08:50 Go to previous message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
Hallo,

The join is faster.
But, see that there are indexes on the key-columns, best is unique keys.
When using cost-based optimiser : see that the two tables and indexes are analyzed.
Also the 'SELECT *' it will select all from table1 and Table2, this will slow down your querry. When you select for example only A.KEY and B.KEY, then Oracle must only read the indexes, when A.* then must read indexes and tableA, when A.* and b.*, then it reads the indexes and tableA and TableB.

Try it out.

Greetings,

Thierry.
Previous Topic: "NOT IN" operator with more than one condidition
Next Topic: Select only one record from query result?
Goto Forum:
  


Current Time: Wed Oct 21 11:36:10 CDT 2020