Re: Find Empty Tables

Billy

Glad to see a DBA with the right attitude to developers ;-)

I think exists can be a superb performance tool - when used correctly :-). I agree that correlated updates are often a real nightmare (the UPDATE with a WHERE EXISTS (...) and no other criteria). If there are other criteria in the where clause that allow use of a useful index AND the subquery is optimal (no FTS of large tables & not poorly selective) then the EXISTS is still pretty much okay.

Interestingly the EXISTS is implemented internally within Oracle much like a WHERE ROWNUM = 1, stopping on encountering the first row. I've just for fun and arguments sake and the good name of the EXISTS clause tried the following varaitions

1 select 'x' from dual where not exists(select 'x' from table); 2 select 'x' from dual where not exists(select /*+ FIRST_ROWS */ 'x' from table);
3 select /*+ FIRST_ROWS */ count(*) from tablewhere rownum = 1; 4 select /*+ FIRST_ROWS */ 'x' from table where rownum = 1;

on tables with 1000's rows and those with none. The timings are all too small to register on tkprof (<100msec) - run in TOAD with timing on, they are all 10msec responses (the smallest measure in TOAD I think). Looking at the tkprof output shows almost identical explain plans (the count has a SORT AGGREGATE but it incurs no measurable cost) and identical database engine effort ( 4 is slight more efficient for empty tables in terms of number of data blocks accessed - 1 rather than 4).

Options 2 & 4 do a FFS on the tables primary key. Options 1 & 3 do a FTS - but cuts this short due to the rownum. So the EXISTS was a valid option ;-)) and but is even better with the FIRST_ROWS hint if the db is not set up to be that way or if you bury the code in a PL/SQL block..

Andy "Can you guess I've nothing better to do" S.

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:bb7aiu$kg3$1_at_ctb-nnrp2.saix.net...
> Andy wrote:
>
> > You could try SELECT 'X' FROM DUAL WHERE EXISTS( SELECT 'X' FROM table).
>
> Oh yeah.. forgot too mention.
>
> Andy, I take a lead pipe to any developer that use EXISTS in SQL on any of
> the databases I look after. :-)
>
> Few developers know how to correctly use it. I have witnessed numerous
> performance horrors because of abusing EXIST.. like doing an UPDATE and in
> the WHERE clause use an EXISTS. For every single row matching the short
> circuit boolean evaluation in the WHERE clause, a sub SQL select is run.
> You have a lot of rows to update, or the sub SQL does an index range scan
> or even a join... performance cease to exist.
>
> My opinion - never use EXISTS unless you have a pretty darn good reason
why
> _nothing_ else but an EXISTS will do the job. IMO, your example does not
> necessitate the use of an EXISTS.
>
> --
> Billy
Received on Fri May 30 2003 - 07:52:52 CDT