Re: SQL challenge;-)

select ta.a,ta.b from table_a ta
where (ta.a,ta.b) not exists (select tb.a,tb.b from table_b tb where tb.a = ta.a and tb.b = tb.b)

PS - 1. not exists will work a lot faster than not in.

        2. As others have pointed out, set operator minus will be very fast also.

"Norman Dunbar" <ndunbar_at_lynxfinancialsystems.co.uk> wrote in message news:F43E6BAE5BB5D411A44C00805FBE740D842687_at_apps.leeds.lfs.co.uk...
> -----Original Message-----
> From: Frank an der Heiden [mailto:fh_at_energotec.de]
> Posted At: Wednesday, May 02, 2001 11:20 PM
> Posted To: server
> Conversation: SQL challenge;-)
> Subject: SQL challenge;-)
>
>
> hi,
>
> here a little task for all sql-freaks ;-))
>
> <SNIP>
>
> Intersestingly enough, I had a developer come to me this morning with a
> similar problem. He has two tables which *should* both have had the same
> data in but one was out of sync with the other.
> He did this to list the ones to be deleted :
>
> select a,b from table_a where a,b not in (select a,b from table_b);
>
> Looking at the explain plan for this, showed a full index scan of both
> priomary keys - fields a and b are both in the primary key but in
> positions 2 and 3 (more on that later) - and the cost was about 280 or
> similar.
> There are 62,597 rows in table_a and 61,346 in table_b.
> The system ground to a halt when the select was run - stayed that way
> for absolutely hours and checking with TOAD, I could see the buffer hits
> for the statement getting higher and higher for very few rows returned
> (1,500,000 buffer hits for 59 rows).
>
> Q1. Is this because the sub-query (select a.b from table_b) was being
> run 61,597 times ?
>
> I changed the query to :
>
> select a,b from table_a minus select a,b from table_b
>
> and checked the explain plan. In this case the cost was about 11 times
> higher and the cardinality went through the roof (about 8,880,830)
> compared to the earlier script. However, it completed in 6 seconds.
>
> Q2. Why was the cost & cardinality so high for the second query - yet it
> ran through much much quicker.
>
> My developer was very happy anyway, but it turned out that he needed an
> extra field in his query anyway :o)
>
>
> Regards, Norman.
>
> ------------------------------------------------------------------------
> --------
> Norman Dunbar EMail: NDunbar_at_LynxFinancialSystems.co.uk
> Database/Unix administrator Phone: 0113 289 6265
> Lynx Financial Systems Ltd. Fax: 0113 201 7265
> URL: http://www.LynxFinancialSystems.com
> ------------------------------------------------------------------------
> --------
>
Received on Sat May 05 2001 - 22:46:42 CDT