Re: Checking for Sequential Numbers

From: Buck Turgidson <jcman*NOSPAM*_at_worldnet.att.net>
Date: Thu, 6 Jan 2000 19:03:05 -0800
Message-ID: <853agp$f2q$1@bgtnsc03.worldnet.att.net>

By a stunning upset, your code's performance soundly trounced Mr. Kyte's. His 80,550 consistent gets to your 192. This was after several executions, so it rules out any caching of data.

Thanks again to both you and Mr. Kyte.

SQL> select
  2 'Gap starting at number ' || id || ' detected'   3 from id_tbl a
  4 where not exists ( select

  5                     null
  6                     from id_tbl b
  7                     where b.id = a.id+1);

'GAPSTARTINGATNUMBER'||ID||'DETECTED'

---

Gap starting at number 16011 detected
Gap starting at number 26823 detected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=RULE    1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'ID_TBL'
   3    1     INDEX (RANGE SCAN) OF 'ID_TBL_IDX' (NON-UNIQUE)

Statistics

---

          0  recursive calls
          2  db block gets
      80550  consistent gets
         84  physical reads
          0  redo size
        246  bytes sent via SQL*Net to client
        337  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>
SQL> select --+ choose
  2 'Gap starting at number ' || id || ' detected'   3 from id_tbl a
  4 where not exists ( select

  5                     null
  6                     from id_tbl b
  7                     where b.id = a.id+1);

'GAPSTARTINGATNUMBER'||ID||'DETECTED'

---

Gap starting at number 16011 detected
Gap starting at number 26823 detected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: CHOOSE    1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'ID_TBL'
   3    1     INDEX (RANGE SCAN) OF 'ID_TBL_IDX' (NON-UNIQUE)

Statistics

---

          0  recursive calls
          2  db block gets
      80550  consistent gets
          0  physical reads
          0  redo size
        249  bytes sent via SQL*Net to client
        348  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed







SQL>

SQL>
SQL> select --+ all_rows
  2 a.id
  3 from
  4 id_tbl a, id_tbl b
  5 where a.id >= 2
  6 and a.id + 1 = b.id (+)
  7 and b.id is null;

        ID

---

     16011
     26823

Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=13Card=2000Bytes=130000)

   1 0 FILTER

   2    1     HASH JOIN (OUTER)
   3    2       INDEX (RANGE SCAN) OF 'ID_TBL_IDX' (NON-UNIQUE)
(Cost=2Card=100 Bytes=3900)
   4    2       TABLE ACCESS (FULL) OF 'ID_TBL' (Cost=9 Card=2000

Bytes=52000)

Statistics

---

          0  recursive calls
          2  db block gets
        192  consistent gets
          0  physical reads
          0  redo size
        144  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Received on Thu Jan 06 2000 - 21:03:05 CST