Re: Find Empty Tables

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 30 May 2003 14:51:40 +0100
Message-ID: <3ed761ec$0$29711$ed9e5944@reading.news.pipex.net>

"andrewst" <member14183_at_dbforums.com> wrote in message news:2939983.1054298314_at_dbforums.com...
> >
> > 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
> Maybe it doesn't "necessitate" using EXISTS, but in this case EXISTS
> happens to be just as good a solution as yours. Seems a bit rough to
> criticise Andy for using EXISTS properly just because you have known
> others to use it improperly! I have also known people to use COUNT(*)
> improperly (like WITHOUT your ROWNUM=1 restriction to check for
> existence): shall I take a lead pipe to you ;-)

Especially if you consider the following with just one small variation on Billy's suggestion - that is not using an anonymous PL/SQL Block.

SQL> set timing on autotrace on explain stat SQL> select 'x' from dual
  2 where exists (select 'x' from agltransact);

'X'

---

x

Elapsed: 00:00:00.02

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=16360)    1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=16360)
   3    1     INDEX (FULL SCAN) OF 'AIAGLTRANSACT2' (NON-UNIQUE) (Cos
          =26 Card=7165654)

Statistics

---

          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Elapsed: 00:00:00.01
SQL> ed
Wrote file afiedt.buf

  1 SELECT

  2      /*+ FIRST_ROWS */
  3      count(*)

  4 FROM agltransact
  5* WHERE rownum = 1
SQL> /   COUNT(*)

---

         1

Elapsed: 00:00:10.01

Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=1)    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       INDEX (FAST FULL SCAN) OF 'AIAGLTRANSACT2' (NON-UNIQU
          ) (Cost=4 Card=7165654)

Statistics

---

          0  recursive calls
          0  db block gets
       9785  consistent gets
       9536  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

I'd prefer 6 LIO to 10k LIO every time.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Fri May 30 2003 - 08:51:40 CDT