生成大量虚拟行的方法

有时候需要使用大量虚拟行进行数据处理，比如，以下sql可以一次插入大量行：
insert /*+ append */ into t
select rownum r from dual connect by level<=1e7;

但在使用中发现该sql需要使用大量内存,可能与connect by的内部实现机制有关.
本文提出使用表函数实现类似功能,所需内存极少.
测试如下:
1,创建表函数
CREATE OR REPLACE PACKAGE f_n_rows IS
  TYPE outrec_typ IS RECORD(
    a INT);
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION get_n_rows(p INT) RETURN outrecset
    PARALLEL_ENABLE
    PIPELINED;
END f_n_rows;
/

CREATE OR REPLACE PACKAGE BODY f_n_rows IS
  FUNCTION get_n_rows(p INT) RETURN outrecset
    PARALLEL_ENABLE
    PIPELINED IS
    out_rec outrec_typ;
  BEGIN
    FOR x IN 1 .. p LOOP
      out_rec.a := x;
      PIPE ROW(out_rec);
    END LOOP;
    RETURN;
  END;
END f_n_rows;
/

select * from table(f_n_rows.get_n_rows(10));

2,比较两种方法所需资源
2.1使用表函数所需资源
SQL> set serveroutput on
SQL> exec session_pkg.get_snap;
 
PL/SQL procedure successfully completed
SQL> select count(*) from table(f_n_rows.get_n_rows(1e7));
 
  COUNT(*)
----------
  10000000
SQL> exec session_pkg.rpt_stat_event(0);
 
Session id:146 Serial#:480 Last sampid:9221487
 
----session stats---
Name                                 Value
bytes received via SQL*Net fro       2,001
bytes sent via SQL*Net to clie       1,417
redo size                            1,268
undo change vector size                908
workarea memory allocated              485
DB time                                422
CPU used when call started             420
CPU used by this session               415
user calls                              18
session logical reads                   11
opened cursors cumulative               10
SQL*Net roundtrips to/from cli          10
db block changes                        10
db block gets                           10
db block gets from cache                10
parse count (total)                     10
execute count                            9
consistent changes                       6
enqueue releases                         5
recursive calls                          5
session cursor cache hits                5
redo entries                             3
calls to get snapshot scn: kcm           3
calls to kcmgas                          2
commit cleanouts                         2
commit cleanout failures: bloc           2
free buffer requested                    2
enqueue requests                         2
opened cursors current                   2
cursor authentications                   2
session cursor cache count               2
redo synch writes                        1
consistent gets from cache               1
consistent gets                          1
user commits                             1
messages sent                            1
redo ordering marks                      1
 
----session stats-pga/uga <<>>----
Name                                    Value1->          Value2
session pga memory                     454,228->       1,764,948
session pga memory max                 454,228->      10,546,772
session uga memory                     156,256->       1,405,824
session uga memory max                 156,256->       9,834,860
 
----session events---
Wait_Class     Event                         Total_waits Time_waited
Commit         log file sync                            1           0
Idle           SQL*Net message from client             10           8
Network        SQL*Net message to client               10           0
 
----v$active_session_history---
Wait_class     Event                         count(*)        min_samp    max_samp   
 
PL/SQL procedure successfully completed

 

2.2使用connect by所需资源
SQL> set serveroutput on
SQL> exec session_pkg.get_snap;
 
PL/SQL procedure successfully completed
SQL> select count(*) from dual connect by level<=1e7;
 
  COUNT(*)
----------
  10000000
SQL> exec session_pkg.rpt_stat_event(0);
 
Session id:147 Serial#:816 Last sampid:9221941
 
----session stats---
Name                                 Value
bytes received via SQL*Net fro       1,996
bytes sent via SQL*Net to clie       1,417
redo size                            1,140
DB time                                919
CPU used when call started             915
CPU used by this session               910
undo change vector size                908
workarea memory allocated              513
user calls                              18
opened cursors cumulative               10
parse count (total)                     10
SQL*Net roundtrips to/from cli          10
db block changes                         9
session logical reads                    9
execute count                            9
db block gets from cache                 8
db block gets                            8
consistent changes                       6
recursive calls                          5
session cursor cache hits                5
enqueue releases                         4
session cursor cache count               3
redo entries                             3
calls to get snapshot scn: kcm           3
opened cursors current                   2
commit cleanout failures: bloc           2
commit cleanouts                         2
sorts (memory)                           1
workarea executions - optimal            1
calls to kcmgas                          1
free buffer requested                    1
sorts (rows)                             1
consistent gets from cache               1
consistent gets                          1
enqueue requests                         1
messages sent                            1
user commits                             1
redo synch writes                        1
 
----session stats-pga/uga <<>>----
Name                                    Value1->          Value2
session pga memory                     454,228->       1,830,484
session pga memory max                 454,228->     301,788,756
session uga memory                     156,256->       1,405,824
session uga memory max                 156,256->     301,159,728
 
----session events---
Wait_Class     Event                         Total_waits Time_waited
Commit         log file sync                            1           0
Idle           SQL*Net message from client             10           7
Network        SQL*Net message to client               10           0
 
----v$active_session_history---
Wait_class     Event                         count(*)        min_samp    max_samp   
 
PL/SQL procedure successfully completed

2.3比较:
                                        表函数                 connect_by
DB time                                 422                    919
session pga memory max                  10,546,772             301,788,756

结论:
使用表函数方式,所需CPU是使用connect_by方法的50%;所需pga也仅为后者的3%.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-714798/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-714798/