Re: Correlated subqueries slow as hell?

jpanico_at_netcom.com (Joe Panico) wrote:

>My question has two parts:
 

>1) MYTABLE has an SSN and an ID column, among others. ID is unique,
>SSN is not. If I want to see all the attributes for each row that
>contains a *unique* SSN, and for those SSNs which have multiple rows
>I don't care which row I see, how should I form the query? Is it possible
>to form this query without using a correlated subquery, or must I
>use something like:
 

>select * from MYTABLE T where T.ID = (select max(ID) from MYTABLE X
> where X.SSN = T.SSN);

Is the table indexed by SSN? If not, that would account for the long execution time. It'll be doing a full table scan on the sub-query for every row in the main table. If this is the case then you might speed things up with the following query.

select * from mytable
where id in (select max(id) from mytable group by ssn);

The subquery will only get executed once.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!