SQL challenge;-)

-----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


------------------------------------------------------------------------