Excellent Tip to avoid recursive work

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Thu, 6 May 2004 11:37:49 -0400
Message-ID: <01aa01c43380$18532870$2501a8c0@dazasoftware.com>

Even when I tried it before, when I saw a function called recursively several times I tried to encapsulate as a subquery but I didn't got an important improvement
The trick comes from Connor McDonald all the trick resides in the where rownum=1 in the query to dual

It change from timing 22352 to 411
And from 796015 to 3595 consistent gets

Thanks Connor

  1 select sum(trc_monto_bob/1000) from transac_me   2* where trc_empresagestion = db_utl_me SQL> / SUM(TRC_MONTO_BOB/1000)

---

              103886852

 real: 22352

Execution Plan

---

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

   2    1     TABLE ACCESS (FULL) OF 'TRANSAC_ME' (Cost=342 Card=15819
          6 Bytes=1423764)

Statistics

---

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

SQL> edit
Escrito fichero afiedt.buf

  1 select sum(trc_monto_bob/1000) from transac_me,( select db_utl_me enano fr
m dual where rownum=1)
  2* where trc_empresagestion = enano
SQL> / SUM(TRC_MONTO_BOB/1000)

---

              103886852

 real: 411

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=345 Card=1 Bytes=201
          1)

   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=345 Card=158196 Bytes=318132156)
   3    2       VIEW (Cost=2 Card=1 Bytes=2002)
   4    3         COUNT (STOPKEY)
   5    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)
   6    2       TABLE ACCESS (FULL) OF 'TRANSAC_ME' (Cost=342 Card=158
          196 Bytes=1423764)

Statistics

---

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

SQL> Juan Carlos Reyes Pacheco
OCP
Database 9.2 Standard Edition

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Thu May 06 2004 - 11:34:25 CDT