Re: Correlated subqueries slow as hell?

In article <jpanicoDKFAw0.Czw_at_netcom.com>, jpanico_at_netcom.com says...
>
>
>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);
>
>
>2) What are internal mechanics that Oracle uses to resolve correlated
>subqueries? Does it fetch all records from the table(s) in the main query
>and then step through them one -by-one, for each row evaluating the
>subquery, or does it do something more (less?) clever? I'm trying to figure
 out
>why the above query is so slow. When issued against a small, 100 rows,
 table,
>it returns the answer instantaneously. But when the query is used against
>a larger table, 30K rows, it hangs. This, despite the fact that MYTABLE is
 indexed
>on both ID and SSN. Ostensibly the query would return eventually, but I have
>only waited a half hour before giving up.
>
>Any insights appreciated. Thanks.
>--
>
>Joe Panico
>NeXTStep/OpenStep Developer
>BLaCKSMITH Inc.
>
>jpanico_at_netcom.com
>
>/* Please no NeXTMail, I can't read it at this address */
>

Joe - A correlated sub-query would have been my choice, just like the solution you provided. But there are other ways to get the same results. For example, the following would also work:

select * from MYTABLE T where (T.id, T.ssn) in (select max(X.id), X.ssn from MYTABLE X group by X.ssn);

Have you examined the EXPLAIN PLAN to see what execution strategy the Oracle optimizer is choosing? This will tell you if the query has disabled an index and is doing a full-table scan etc. Of course, it could also be something in the data that causes the problem (eg, some queries run faster with full table scans than when an index is used, although this seems unlikely in the situation you described).

Gary Received on Tue Jan 02 1996 - 00:00:00 CET