Re: Find Empty Tables

Andy wrote:

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

Hey, how about you and me swapping developers? I'm sure you have yours better trained. :-)

> 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).
<snipped>

Thanks. Interesting. I think if one plays around with the hints, simply to read the first available random row from a table...

As for EXISTS - I still bristle when I see that in any SQL statement. Once bitten... :-)

--
Billy








> 

> 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

-- 
--
Billy