Re: Checking if more than 0 rows exist.

From: chao_ping <zhuchao_at_gmail.com>
Date: 10 May 2005 17:01:33 -0700
Message-ID: <1115769693.340391.132350@f14g2000cwb.googlegroups.com>

Are you sure?
Take a look at the sample:

Test table is 20k rows, one row for 1-9999, and id=10000 has 10000 rows.
index on the id column.

--BAD Performance:

SQL> select count(*) from test where id=10000 and rownum=1;

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=3)    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_A' (NON-UNIQUE) (Cost=5
           Card=10133 Bytes=30399)

Statistics

---

          0  recursive calls
          0  db block gets
         26  consistent gets
          0  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

--Good performance:

 SQL> select count(*) from dual where exists (select 1 from test where id=10000);

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7102' (UNIQUE) (Cost
          =1 Card=1)

   4    2       INDEX (RANGE SCAN) OF 'IDX_A' (NON-UNIQUE) (Cost=1 Car
          d=1 Bytes=3)

Statistics

---

          0  recursive calls
          0  db block gets
          3  consistent gets
          0  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

--Good performance

SQL> select /*+first_rows*/ 1 from test where id=10000 and rownum<2;

         1

---

         1

Execution Plan

---

   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=21 Card=1
          Bytes=3)

   1    0   COUNT (STOPKEY)
   2    1     INDEX (RANGE SCAN) OF 'IDX_A' (NON-UNIQUE) (Cost=21 Card
          =10133 Bytes=30399)

Statistics

---

          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        372  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

Received on Tue May 10 2005 - 19:01:33 CDT