Re: Checking if more than 0 rows exist.

From: <BigBoote66_at_hotmail.com>
Date: 10 May 2005 08:53:49 -0700
Message-ID: <1115740429.158347.256300@o13g2000cwo.googlegroups.com>

> >>Do you think this is a better approach than using EXISTS as
suggested
> >>(and proved to be faster) by Phil?
> >>
> > I have not seen any comparism - you'll have to do that.
> > Basically, an exists will do something similar; stop reading
> > after the first match.
>
> Here's the reality check on this discussion:
>
> SELECT COUNT(*)
> FROM bar
> WHERE office = 45
> AND statement = 199901
> AND rownum = 1;
>
> Statistics
> ----------------------------------------------------------
> 27 consistent gets
>
> SELECT 1
> FROM dual
> WHERE EXISTS (
> SELECT *
> FROM bar
> WHERE office = 45
> AND statement = 199901);
>
> Statistics
> ----------------------------------------------------------
> 27 consistent gets

It depends on the data distribution of your table/view. Here's an example that shows EXISTS is 100 times faster:

SQL> SET AUTOTRACE ON SQL> SELECT COUNT(*) FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE';   COUNT(*)

---

       687

1 row selected.

Execution Plan

---

   0	  SELECT STATEMENT Optimizer=CHOOSE
   1	0   SORT (AGGREGATE)
   2	1     FILTER
   3	2	TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   4	3	  NESTED LOOPS
   5	4	    TABLE ACCESS (FULL) OF 'USER$'
   6	4	    INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   7	2	TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
   8	7	  INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
   9	2	TABLE ACCESS (BY INDEX ROWID) OF 'OBJAUTH$'
  10	9	  NESTED LOOPS
  11   10	    FIXED TABLE (FULL) OF 'X$KZSRO'
  12   10	    INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
  13	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  14	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  15	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  16	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  17	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  18	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  19	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  20	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  21	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  22	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  23	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  24	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  25	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  26	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  27	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  28	2	FIXED TABLE (FULL) OF 'X$KZSPR'
  29	2	FIXED TABLE (FULL) OF 'X$KZSPR'

Statistics

---

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

SQL> SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE');          1

---

         1

1 row selected.

Execution Plan

---

   0	  SELECT STATEMENT Optimizer=CHOOSE
   1	0   FILTER
   2	1     TABLE ACCESS (FULL) OF 'DUAL'
   3	1     FILTER
   4	3	TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   5	4	  NESTED LOOPS
   6	5	    TABLE ACCESS (FULL) OF 'USER$'
   7	5	    INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   8	3	TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
   9	8	  INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
  10	3	TABLE ACCESS (BY INDEX ROWID) OF 'OBJAUTH$'
  11   10	  NESTED LOOPS
  12   11	    FIXED TABLE (FULL) OF 'X$KZSRO'
  13   11	    INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
  14	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  15	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  16	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  17	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  18	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  19	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  20	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  21	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  22	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  23	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  24	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  25	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  26	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  27	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  28	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  29	3	FIXED TABLE (FULL) OF 'X$KZSPR'
  30	3	FIXED TABLE (FULL) OF 'X$KZSPR'

Statistics

---

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

Received on Tue May 10 2005 - 10:53:49 CDT