Excellent Tip to avoid recursive work
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 May 2004 11:37:49 -0400
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
