网站建设
联系我们

PL_SQL关于优化笛卡尔积的测试

发布于:2014-02-12 09:07来源:未知 作者:admin 点击:
(转自http://www.itpub.net/thread-1840767-1-1.html)
写在最前,是对我自己而言收获最大的想法。
oracle的优化,了解CBO很重要,分析执行计划很重要;
但是,优化绝不止于CBO,相比之下优化必谈CBO,我觉得多少有点误区。
我们究竟是否明白一个sql或者一段plsql执行过程中哪步最费时?为什么?
--******************************************************
正题
论坛上有很多网友问过这样的问题:
我的表中有一个字段text,有一个分隔符 ',',需要按照分隔符把数据分为n行,每行按顺序取夹在分隔符中的部分
比如数据是
id                        text
1                        a, b, c, d
2                        e, f, g
我需要的结果是
1        a
1        b
1        c
1        d
2        e
2        f
2        g

这时候,很多网友会用itpub上最经典的sql回复
  1. select        id, regexp_substr(text, '[^'||chr(10)||']+', 1, level) item_txt
  2. from        t1
  3. connect by prior id=id and level<=length(text)-length(replace(text, chr(10))) and prior dbms_random.value>0;
复制代码
当然,也可能有“正则黑”,会用substr套instr的版本来实现。
当然,也会有笛卡尔积的版本。
总之,这已经成了一个套路。

但是,这样做真的好么?
我以前从来没思考过这个问题,直到我的项目中真正接触到了这个问题,我才发现,sql的几个写法,几乎只有理论上的意义。
我们来测测。
  1. create table t1 (id int, text varchar2(4000));
  2. insert into t1
  3. select        1, listagg(lpad('a', 60), chr(10)) within group (order by 1)||chr(10)
  4. from        dual
  5. connect by rownum<=64;
  6.  
  7. insert into t1
  8. select        n+1, text
  9. from        t1, (select rownum n from dual connect by rownum<=199);
复制代码
  1. --sql connect by 写法
  2. bill@ORCL> select       id, regexp_substr(text, '[^'||chr(10)||']+', 1, level) item_txt
  3.   2  from       t1
  4.   3  connect by prior id=id and level<=length(text)-length(replace(text, chr(10))) and prior dbms_random.value>0;
  5.  
  6. 已选择 12800 行。
  7.  
  8. 已用时间:  00: 00: 16.24
  9.  
  10. 执行计划
  11. ----------------------------------------------------------
  12. Plan hash value: 3874795171
  13.  
  14. -------------------------------------------------------------------------------------
  15. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  16. -------------------------------------------------------------------------------------
  17. |   0 | SELECT STATEMENT             |      |   186 |   366K|    68   (0)| 00:00:01 |
  18. |*  1 |  CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
  19. |   2 |   TABLE ACCESS FULL          | T1   |   186 |   366K|    68   (0)| 00:00:01 |
  20. -------------------------------------------------------------------------------------
  21.  
  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------
  24.  
  25.    1 - access("ID"=PRIOR "ID")
  26.        filter(LEVEL<=LENGTH("TEXT")-LENGTH(REPLACE("TEXT",' ')) AND PRIOR
  27.               "DBMS_RANDOM"."VALUE"()>0)
  28.  
  29. Note
  30. -----
  31.    - dynamic statistics used: dynamic sampling (level=2)
  32.  
  33.  
  34. 统计信息
  35. ----------------------------------------------------------
  36.           4  recursive calls
  37.           0  db block gets
  38.         313  consistent gets
  39.           0  physical reads
  40.           0  redo size
  41.      228399  bytes sent via SQL*Net to client
  42.        9927  bytes received via SQL*Net from client
  43.         855  SQL*Net roundtrips to/from client
  44.           1  sorts (memory)
  45.           0  sorts (disk)
  46.       12800  rows processed
复制代码
16秒这个时间有点出乎我的意料。我没想到会这么慢,试试substr套instr的版本
  1. bill@ORCL> select       id, substr(text,
  2.   2                                decode(level, 1, 1, instr(text, chr(10), 1, level-1)+1)),
  3.   3                                instr(text, chr(10), 1, level)-decode(level, 1, 1, instr(text, chr(10), 1, level-1)+1)
  4.   4  from       t1
  5.   5  connect by prior id=id and level<=length(text)-length(replace(text, chr(10))) and prior dbms_random.value>0;
  6.  
  7. 已选择 12800 行。
  8.  
  9. 已用时间:  00: 00: 04.89
  10.  
  11. 执行计划
  12. ----------------------------------------------------------
  13. Plan hash value: 3874795171
  14.  
  15. -------------------------------------------------------------------------------------
  16. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  17. -------------------------------------------------------------------------------------
  18. |   0 | SELECT STATEMENT             |      |   186 |   366K|    68   (0)| 00:00:01 |
  19. |*  1 |  CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
  20. |   2 |   TABLE ACCESS FULL          | T1   |   186 |   366K|    68   (0)| 00:00:01 |
  21. -------------------------------------------------------------------------------------
  22.  
  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------
  25.  
  26.    1 - access("ID"=PRIOR "ID")
  27.        filter(LEVEL<=LENGTH("TEXT")-LENGTH(REPLACE("TEXT",' ')) AND PRIOR
  28.               "DBMS_RANDOM"."VALUE"()>0)
  29.  
  30. Note
  31. -----
  32.    - dynamic statistics used: dynamic sampling (level=2)
  33.  
  34.  
  35. 统计信息
  36. ----------------------------------------------------------
  37.           0  recursive calls
  38.           0  db block gets
  39.         248  consistent gets
  40.           0  physical reads
  41.           0  redo size
  42.    25757310  bytes sent via SQL*Net to client
  43.        9927  bytes received via SQL*Net from client
  44.         855  SQL*Net roundtrips to/from client
  45.           1  sorts (memory)
  46.           0  sorts (disk)
  47.       12800  rows processed
复制代码
强多了,看来oracle的正则最好是能不用尽量不用。
但是仍然很不满意,这才几条数据啊。
试试笛卡尔积的版本

  1. bill@ORCL> select       substr(text,
  2.   2                        decode(lv, 1, 1, instr(text, chr(10), 1, lv-1)+1)),
  3.   3                        instr(text, chr(10), 1, lv)-decode(lv, 1, 1, instr(text, chr(10), 1, lv-1)+1)
  4.   4  from       t1,
  5.   5             (select rownum lv from dual connect by rownum<=64) b;
  6.  
  7. 已选择 12800 行。
  8.  
  9. 已用时间:  00: 00: 03.30
  10.  
  11. 执行计划
  12. ----------------------------------------------------------
  13. Plan hash value: 894562235
  14.  
  15. ----------------------------------------------------------------------------------------
  16. | Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  17. ----------------------------------------------------------------------------------------
  18. |   0 | SELECT STATEMENT                |      |   186 |   366K|    70   (0)| 00:00:01 |
  19. |   1 |  MERGE JOIN CARTESIAN           |      |   186 |   366K|    70   (0)| 00:00:01 |
  20. |   2 |   VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
  21. |   3 |    COUNT                        |      |       |       |            |          |
  22. |*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
  23. |   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
  24. |   6 |   BUFFER SORT                   |      |   186 |   363K|    70   (0)| 00:00:01 |
  25. |   7 |    TABLE ACCESS FULL            | T1   |   186 |   363K|    68   (0)| 00:00:01 |
  26. ----------------------------------------------------------------------------------------
  27.  
  28. Predicate Information (identified by operation id):
  29. ---------------------------------------------------
  30.  
  31.    4 - filter(ROWNUM<=64)
  32.  
  33. Note
  34. -----
  35.    - dynamic statistics used: dynamic sampling (level=2)
  36.  
  37.  
  38. 统计信息
  39. ----------------------------------------------------------
  40.          48  recursive calls
  41.           0  db block gets
  42.         435  consistent gets
  43.           2  physical reads
  44.           0  redo size
  45.      359032  bytes sent via SQL*Net to client
  46.        9927  bytes received via SQL*Net from client
  47.         855  SQL*Net roundtrips to/from client
  48.           8  sorts (memory)
  49.           0  sorts (disk)
  50.       12800  rows processed
复制代码
又快一点,不过仅仅一点而已,数量级是不会变了。
试试物理表
  1. create table t2 (id int, lv int, text varchar2(4000));
  2. insert into t2
  3. select        (n-1)*200+id, n, text
  4. from        t1, (select rownum n from dual connect by rownum<=64);
  5.  
  6. bill@ORCL> select       substr(text,
  7.   2                        decode(lv, 1, 1, instr(text, chr(10), 1, lv-1)+1)),
  8.   3                        instr(text, chr(10), 1, lv)-decode(lv, 1, 1, instr(text, chr(10), 1, lv-1)+1)
  9.   4  from       t2;
  10.  
  11. 已选择 12800 行。
  12.  
  13. 已用时间:  00: 00: 03.53
  14.  
  15. 执行计划
  16. ----------------------------------------------------------
  17. Plan hash value: 1513984157
  18.  
  19. --------------------------------------------------------------------------
  20. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  21. --------------------------------------------------------------------------
  22. |   0 | SELECT STATEMENT  |      | 15663 |    30M|  3567   (1)| 00:00:01 |
  23. |   1 |  TABLE ACCESS FULL| T2   | 15663 |    30M|  3567   (1)| 00:00:01 |
  24. --------------------------------------------------------------------------
  25.  
  26. Note
  27. -----
  28.    - dynamic statistics used: dynamic sampling (level=2)
  29.  
  30.  
  31. 统计信息
  32. ----------------------------------------------------------
  33.           0  recursive calls
  34.           0  db block gets
  35.       13005  consistent gets
  36.       12997  physical reads
  37.           0  redo size
  38.     3526140  bytes sent via SQL*Net to client
  39.        9927  bytes received via SQL*Net from client
  40.         855  SQL*Net roundtrips to/from client
  41.           0  sorts (memory)
  42.           0  sorts (disk)
  43.       12800  rows processed
复制代码
从实验现象来看,似乎oracle不管你select的substr需要多少空间,都把全表复制64份,再取substr。如果是这样,那这里的内存操作实在太低效了。
多做两个实验看看。
  1. bill@ORCL> select       substr(text,
  2.   2                        decode(lv, 1, 1, instr(text, chr(10), 1, lv-1)+1)),
  3.   3                        instr(text, chr(10), 1, lv)-decode(lv, 1, 1, instr(text, chr(10), 1, lv-1)+1)
  4.   4  from       t1,
  5.   5             (
  6.   6             select  id, lv
  7.   7             from    (select id from t1),
  8.   8                             (select rownum lv from dual connect by rownum<=64)
  9.   9             where   rownum>0
  10. 10             ) b
  11. 11  where      t1.id=b.id;
  12.  
  13. 已选择 12800 行。
  14.  
  15. 已用时间:  00: 00: 03.56
  16.  
  17. 执行计划
  18. ----------------------------------------------------------
  19. Plan hash value: 553474792
  20.  
  21. ----------------------------------------------------------------------------------------------------
  22. | Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  23. ----------------------------------------------------------------------------------------------------
  24. |   0 | SELECT STATEMENT                    |              |   187 |   372K|    71   (0)| 00:00:01 |
  25. |*  1 |  HASH JOIN                          |              |   187 |   372K|    71   (0)| 00:00:01 |
  26. |   2 |   VIEW                              |              |   186 |  4836 |     3   (0)| 00:00:01 |
  27. |   3 |    COUNT                            |              |       |       |            |          |
  28. |*  4 |     FILTER                          |              |       |       |            |          |
  29. |   5 |      MERGE JOIN CARTESIAN           |              |   186 |  4836 |     3   (0)| 00:00:01 |
  30. |   6 |       VIEW                          |              |     1 |    13 |     2   (0)| 00:00:01 |
  31. |   7 |        COUNT                        |              |       |       |            |          |
  32. |*  8 |         CONNECT BY WITHOUT FILTERING|              |       |       |            |          |
  33. |   9 |          FAST DUAL                  |              |     1 |       |     2   (0)| 00:00:01 |
  34. |  10 |       BUFFER SORT                   |              |   186 |  2418 |     3   (0)| 00:00:01 |
  35. |  11 |        INDEX FULL SCAN              | SYS_C0010642 |   186 |  2418 |     1   (0)| 00:00:01 |
  36. |  12 |   TABLE ACCESS FULL                 | T1           |   186 |   366K|    68   (0)| 00:00:01 |
  37. ----------------------------------------------------------------------------------------------------
  38.  
  39. Predicate Information (identified by operation id):
  40. ---------------------------------------------------
  41.  
  42.    1 - access("T1"."ID"="B"."ID")
  43.    4 - filter(ROWNUM>0)
  44.    8 - filter(ROWNUM<=64)
  45.  
  46. Note
  47. -----
  48.    - dynamic statistics used: dynamic sampling (level=2)
  49.  
  50.  
  51. 统计信息
  52. ----------------------------------------------------------
  53.          15  recursive calls
  54.           0  db block gets
  55.         515  consistent gets
  56.           9  physical reads
  57.           0  redo size
  58.    25712495  bytes sent via SQL*Net to client
  59.        9927  bytes received via SQL*Net from client
  60.         855  SQL*Net roundtrips to/from client
  61.           2  sorts (memory)
  62.           0  sorts (disk)
  63.       12800  rows processed
复制代码
可以看到,改写的速度也是一样,似乎效率还要更差一点。
再看看我们明确的告诉oracle select的字段内容,它的表现怎么样,
  1. bill@ORCL> select       substr(t1.id, instr(text, chr(10), 1, 58), instr(text, chr(10), 1, 59)-instr(text, chr(10), 1, 58)-1) item_txt
  2.   2  from       t1,
  3.   3             (select rownum lv from dual connect by rownum<=64) b;
  4.  
  5. 已选择 12800 行。
  6.  
  7. 已用时间:  00: 00: 01.26
  8.  
  9. 执行计划
  10. ----------------------------------------------------------
  11. Plan hash value: 894562235
  12.  
  13. ----------------------------------------------------------------------------------------
  14. | Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  15. ----------------------------------------------------------------------------------------
  16. |   0 | SELECT STATEMENT                |      |   186 |   366K|    70   (0)| 00:00:01 |
  17. |   1 |  MERGE JOIN CARTESIAN           |      |   186 |   366K|    70   (0)| 00:00:01 |
  18. |   2 |   VIEW                          |      |     1 |       |     2   (0)| 00:00:01 |
  19. |   3 |    COUNT                        |      |       |       |            |          |
  20. |*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
  21. |   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
  22. |   6 |   BUFFER SORT                   |      |   186 |   366K|    70   (0)| 00:00:01 |
  23. |   7 |    TABLE ACCESS FULL            | T1   |   186 |   366K|    68   (0)| 00:00:01 |
  24. ----------------------------------------------------------------------------------------
  25.  
  26. Predicate Information (identified by operation id):
  27. ---------------------------------------------------
  28.  
  29.    4 - filter(ROWNUM<=64)
  30.  
  31. Note
  32. -----
  33.    - dynamic statistics used: dynamic sampling (level=2)
  34.  
  35.  
  36. 统计信息
  37. ----------------------------------------------------------
  38.           7  recursive calls
  39.           0  db block gets
  40.         380  consistent gets
  41.           2  physical reads
  42.           0  redo size
  43.      227513  bytes sent via SQL*Net to client
  44.        9927  bytes received via SQL*Net from client
  45.         855  SQL*Net roundtrips to/from client
  46.           2  sorts (memory)
  47.           0  sorts (disk)
  48.       12800  rows processed
复制代码
又快了一些。
那经过这些测试,似乎可以得出这样的结论,cpu运算和内存操作现在是需要考虑的重要因素(本来也是,只不过我们似乎总是做不了什么来优化这些)。
很显然的结论就是,长的字符串,我们对它进行的substr和instr操作,自然会更慢,变量的赋值,效率也会更低。
思考到这里,似乎需要转向plsql了。
因为我的需求中,打散之后的结果是要和其他表继续关联,所以我理所当然想要用管道表函数来试试。
第一个版本的代码并不难写,除了plsql中这些复杂的语法,当然复杂是相对sql而言,其他高级编程语言请呵呵。
  1. create or replace package refcur_pkg_v1
  2. authid current_user
  3. as
  4.     type inrec is record (
  5.         id                        number(38),
  6.         text                varchar2(4000));
  7.     type refcur_t is ref cursor return inrec;
  8.     type outrec_typ is record (
  9.         id                        number(38),
  10.         item_txt        varchar2(4000));
  11.     type outrecset is table of outrec_typ;
  12.     function f_cartesian (p refcur_t) return outrecset pipelined
  13.     parallel_enable (partition p by any);
  14. end;
  15. /
  16.  
  17. create or replace PACKAGE BODY refcur_pkg_v1 IS
  18.     FUNCTION f_cartesian (p refcur_t) RETURN outrecset PIPELINED
  19.     parallel_enable (partition p by any)
  20.     IS
  21.         in_rec      p%ROWTYPE;
  22.         out_rec     outrec_typ;
  23.         C_NL_TERM   varchar2(2) := chr(10); --unix style
  24.         v_nl_pos    int:=0;
  25.         v_tmp_pos        int:=0;
  26.     BEGIN
  27.         LOOP
  28.             FETCH p INTO in_rec;  -- input row
  29.             EXIT WHEN p%NOTFOUND;
  30.  
  31.                 v_nl_pos        :=0;
  32.                 v_tmp_pos        :=0;
  33.             out_rec.id        :=in_rec.id;
  34.             FOR i IN 1..100000 LOOP
  35.                 v_tmp_pos:=instr(in_rec.text, C_NL_TERM, v_nl_pos+1);
  36.                                 exit when v_tmp_pos=0;
  37.                 out_rec.item_txt        :=substr(in_rec.text, v_nl_pos+1, v_tmp_pos-v_nl_pos-1);
  38.                 v_nl_pos                        :=v_tmp_pos;
  39.                 PIPE ROW(out_rec);
  40.             END LOOP;
  41.  
  42.         END LOOP;
  43.         CLOSE p;
  44.         RETURN;
  45.     END f_cartesian;
  46. END refcur_pkg_v1;
  47. /
复制代码
  1. bill@ORCL> select       id, item_txt
  2.   2  from       table(refcur_pkg_v1.f_cartesian(cursor(
  3.   3                     select  id, text
  4.   4                     from    t1
  5.   5                     )));
  6.  
  7. 已选择 12800 行。
  8.  
  9. 已用时间:  00: 00: 00.70
  10.  
  11. 执行计划
  12. ----------------------------------------------------------
  13. Plan hash value: 4049074522
  14.  
  15. --------------------------------------------------------------------------------------------------
  16. | Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  17. --------------------------------------------------------------------------------------------------
  18. |   0 | SELECT STATEMENT                   |             |  8168 |   271K|    29   (0)| 00:00:01 |
  19. |   1 |  VIEW                              |             |  8168 |   271K|    29   (0)| 00:00:01 |
  20. |   2 |   COLLECTION ITERATOR PICKLER FETCH| F_CARTESIAN |  8168 |       |    29   (0)| 00:00:01 |
  21. |   3 |    TABLE ACCESS FULL               | T1          |   186 |   366K|    68   (0)| 00:00:01 |
  22. --------------------------------------------------------------------------------------------------
  23.  
  24. Note
  25. -----
  26.    - dynamic statistics used: dynamic sampling (level=2)
  27.  
  28.  
  29. 统计信息
  30. ----------------------------------------------------------
  31.         217  recursive calls
  32.           0  db block gets
  33.         264  consistent gets
  34.           0  physical reads
  35.           0  redo size
  36.      228399  bytes sent via SQL*Net to client
  37.        9927  bytes received via SQL*Net from client
  38.         855  SQL*Net roundtrips to/from client
  39.           0  sorts (memory)
  40.           0  sorts (disk)
  41.       12800  rows processed
复制代码
0.7秒!看来方向是正确的。
但是我的需求中数据量很庞大,最好还能继续优化一下。
现在的版本是带着整个text的内容去循环,那很自然的会想用二分法去试一试。
  1. create or replace package refcur_pkg
  2. authid current_user
  3. as
  4.     type inrec is record (
  5.         id                        number(38),
  6.         lines                number(38),
  7.         text                varchar2(4000));
  8.     type refcur_t is ref cursor return inrec;
  9.     type outrec_typ is record (
  10.         id                        number(38),
  11.         item_txt        varchar2(4000));
  12.     type outrecset is table of outrec_typ;
  13.     function f_cartesian (p refcur_t) return outrecset pipelined
  14.     parallel_enable (partition p by any);
  15.     function f_cartesian2 (p refcur_t) return outrecset pipelined
  16.     parallel_enable (partition p by any);
  17.     function f_cartesian3 (p refcur_t) return outrecset pipelined
  18.     parallel_enable (partition p by any);
  19. end;
  20. /
  21.  
  22. create or replace package body refcur_pkg IS
  23.     function f_cartesian (p refcur_t) return outrecset pipelined
  24.     parallel_enable (partition p by any)
  25.     is
  26.         in_rec      p%ROWTYPE;
  27.         out_rec     outrec_typ;
  28.         C_NL_TERM   varchar2(2) := chr(10); --unix style
  29.         C_NL_LENG   int := 1;                           --unix new line length
  30.         C_SIZE                int:=64;                                --split clob to varchar by every C_SIZE-th newline character
  31.         C_MAX_LEN        INT:=1024;                                --max length for item_txt; if change, also change item_txt varchar2(1024)
  32.         v_div_pos        int;                                        --end postion of each sub clob
  33.         v1_div_pos        int;                                        --end postion of each sub clob
  34.         v2_div_pos        int;                                        --end postion of each sub clob
  35.         v3_div_pos        int;                                        --end postion of each sub clob
  36.         v4_div_pos        int;                                        --end postion of each sub clob
  37.         v5_div_pos        int;                                        --end postion of each sub clob
  38.         v1_substr        varchar2(4000);
  39.         v2_substr        varchar2(4000);
  40.         v3_substr        varchar2(4000);
  41.         v4_substr        varchar2(4000);
  42.         v5_substr        varchar2(4000);
  43.     begin
  44.         loop
  45.             fetch p into in_rec;  -- input row
  46.             exit when p%NOTFOUND;
  47.             out_rec.id                :=in_rec.id;
  48.  
  49.                         --if lines=C_SIZE, then dichotomy
  50.                         if in_rec.lines=C_SIZE then
  51.                         v_div_pos        :=instr(in_rec.text, C_NL_TERM, 1, C_SIZE/2);
  52.                         for a in 1..2 loop
  53.                                 v1_substr        :=substr(in_rec.text, 1+v_div_pos*(a-1), v_div_pos*(2-a)+1e6*(a-1));
  54.                                 v1_div_pos        :=instr(v1_substr, C_NL_TERM, 1, C_SIZE/4);
  55.                                 for b in 1..2 loop
  56.                                         v2_substr        :=substr(v1_substr, 1+v1_div_pos*(b-1), v1_div_pos*(2-b)+1e6*(b-1));
  57.                                         v2_div_pos        :=instr(v2_substr, C_NL_TERM, 1, C_SIZE/8);
  58.                                         for c in 1..2 loop
  59.                                                 v3_substr        :=substr(v2_substr, 1+v2_div_pos*(c-1), v2_div_pos*(2-c)+1e6*(c-1));
  60.                                                 v3_div_pos        :=instr(v3_substr, C_NL_TERM, 1, C_SIZE/16);
  61.                                                 for d in 1..2 loop
  62.                                                         v4_substr        :=substr(v3_substr, 1+v3_div_pos*(d-1), v3_div_pos*(2-d)+1e6*(d-1));
  63.                                                         v4_div_pos        :=instr(v4_substr, C_NL_TERM, 1, C_SIZE/32);
  64.                                                         for e in 1..2 loop
  65.                                                                 v5_substr        :=substr(v4_substr, 1+v4_div_pos*(e-1), v4_div_pos*(2-e)+1e6*(e-1));
  66.                                                                 v5_div_pos        :=instr(v5_substr, C_NL_TERM, 1, 1);
  67.                                                                 for f in 1..2 loop
  68.                                                                         out_rec.item_txt        :=substr(v5_substr, 1+v5_div_pos*(f-1), (v5_div_pos-1)*(2-f)+(v4_div_pos-v5_div_pos-1)*(f-1));
  69.                                                                         exit when out_rec.item_txt is null;
  70.                                                                         pipe row(out_rec);
  71.                                                                 end loop;
  72.                                                         end loop;
  73.                                                 end loop;
  74.                                         end loop;
  75.                                 end loop;
  76.                         end loop;
  77.                         --if lines=C_SIZE, then ordinary loop method,
  78.                         else
  79.                                 v_div_pos:=0;
  80.                                 v1_div_pos:=0;
  81.                                 for i in 1..1000000000 loop
  82.                         v1_div_pos:=instr(in_rec.text, C_NL_TERM, v_div_pos+1);
  83.                                         exit when v1_div_pos=0;
  84.                         out_rec.item_txt        :=substr(in_rec.text, v_div_pos+1, v1_div_pos-v_div_pos-1);
  85.                         v_div_pos                        :=v1_div_pos;
  86.                         pipe row(out_rec);
  87.                     end loop;
  88.                         end if;
  89.         end loop;
  90.         close p;
  91.         return;
  92.     end f_cartesian;
  93.  
  94.     function f_cartesian2 (p refcur_t) return outrecset pipelined
  95.     parallel_enable (partition p by any)
  96.     is
  97.         in_rec      p%ROWTYPE;
  98.         out_rec     outrec_typ;
  99.     begin
  100.         loop
  101.             fetch p into in_rec;  -- input row
  102.             exit when p%NOTFOUND;
  103.             out_rec.id                        :=in_rec.id;
  104.                         out_rec.item_txt        :=in_rec.text;
  105.                         for i in 1..in_rec.lines loop
  106.                                 pipe row(out_rec);
  107.                         end loop;
  108.         end loop;
  109.         close p;
  110.         return;
  111.     end f_cartesian2;
  112.  
  113.     function f_cartesian3 (p refcur_t) return outrecset pipelined
  114.     parallel_enable (partition p by any)
  115.     is
  116.         in_rec      p%ROWTYPE;
  117.         out_rec     outrec_typ;
  118.     begin
  119.         loop
  120.             fetch p into in_rec;  -- input row
  121.             exit when p%NOTFOUND;
  122.             out_rec.id                        :=in_rec.id;
  123.                         out_rec.item_txt        :=substr(in_rec.text, 1, 64);
  124.                         for i in 1..in_rec.lines loop
  125.                                 pipe row(out_rec);
  126.                         end loop;
  127.         end loop;
  128.         close p;
  129.         return;
  130.     end f_cartesian3;
  131.  
  132. end refcur_pkg;
  133. /
复制代码
  1. bill@ORCL> select       id, item_txt
  2.   2  from       table(refcur_pkg.f_cartesian(cursor(
  3.   3                     select  id, length(text)-length(replace(text, chr(10))), text
  4.   4                     from    t1
  5.   5                     )));
  6.  
  7. 已选择 12800 行。
  8.  
  9. 已用时间:  00: 00: 00.40
  10.  
  11. 执行计划
  12. ----------------------------------------------------------
  13. Plan hash value: 4049074522
  14.  
  15. --------------------------------------------------------------------------------------------------
  16. | Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  17. --------------------------------------------------------------------------------------------------
  18. |   0 | SELECT STATEMENT                   |             |  8168 |   271K|    29   (0)| 00:00:01 |
  19. |   1 |  VIEW                              |             |  8168 |   271K|    29   (0)| 00:00:01 |
  20. |   2 |   COLLECTION ITERATOR PICKLER FETCH| F_CARTESIAN |  8168 |       |    29   (0)| 00:00:01 |
  21. |   3 |    TABLE ACCESS FULL               | T1          |   186 |   366K|    68   (0)| 00:00:01 |
  22. --------------------------------------------------------------------------------------------------
  23.  
  24. Note
  25. -----
  26.    - dynamic statistics used: dynamic sampling (level=2)
  27.  
  28.  
  29. 统计信息
  30. ----------------------------------------------------------
  31.         258  recursive calls
  32.           0  db block gets
  33.         469  consistent gets
  34.           1  physical reads
  35.           0  redo size
  36.      228399  bytes sent via SQL*Net to client
  37.        9927  bytes received via SQL*Net from client
  38.         855  SQL*Net roundtrips to/from client
  39.           0  sorts (memory)
  40.           0  sorts (disk)
  41.       12800  rows processed
复制代码
我不太想详细解释二分法的代码,因为它太丑了,而代码本身的逻辑其实很简单。
因为在我的处理中,是要从clob进行打散,所以我先把之前的处理结果分成64行为一个大的varchar,到这里再用硬性的6层二分法,
如果你的需求中行数不定,可以略微更改一下if条件,以及二分法的层数,这样可以不必在二分法的层数上太过伤神。
总之,我们看到了性能的提升,是显著的,而且,在我的测试当中,我发现先把instr的变量存起来,
也就是类似substr(in_rec.text, v_nl_pos+1, v_tmp_pos-v_nl_pos-1)这种写法,要更高效,上面的代码其实还可以再改。

纯粹为了测试,我还做了f_cartesian2和f_cartesian3,
前者是简单的实现数据的复制,后者是每行取指定的substr,在这两种情况下,其实plsql就没有什么优势了。
  1. --sql connect by
  2. bill@ORCL> select       id, text
  3.   2  from       t1
  4.   3  connect by prior id=id and level<=length(text)-length(replace(text, chr(10))) and prior dbms_random.value>0;
  5.  
  6. 已选择 12800 行。
  7.  
  8. 已用时间:  00: 00: 06.81
  9.  
  10. 执行计划
  11. ----------------------------------------------------------
  12. Plan hash value: 3874795171
  13.  
  14. -------------------------------------------------------------------------------------
  15. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  16. -------------------------------------------------------------------------------------
  17. |   0 | SELECT STATEMENT             |      |   186 |   366K|    68   (0)| 00:00:01 |
  18. |*  1 |  CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
  19. |   2 |   TABLE ACCESS FULL          | T1   |   186 |   366K|    68   (0)| 00:00:01 |
  20. -------------------------------------------------------------------------------------
  21.  
  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------
  24.  
  25.    1 - access("ID"=PRIOR "ID")
  26.        filter(LEVEL<=LENGTH("TEXT")-LENGTH(REPLACE("TEXT",' ')) AND PRIOR
  27.               "DBMS_RANDOM"."VALUE"()>0)
  28.  
  29. Note
  30. -----
  31.    - dynamic statistics used: dynamic sampling (level=2)
  32.  
  33.  
  34. 统计信息
  35. ----------------------------------------------------------
  36.           4  recursive calls
  37.           0  db block gets
  38.         313  consistent gets
  39.           0  physical reads
  40.           0  redo size
  41.      236099  bytes sent via SQL*Net to client
  42.        9927  bytes received via SQL*Net from client
  43.         855  SQL*Net roundtrips to/from client
  44.           1  sorts (memory)
  45.           0  sorts (disk)
  46.       12800  rows processed
  47.  
  48. --sql笛卡尔
  49. bill@ORCL> select       id, text
  50.   2  from       t1,
  51.   3  (select rownum n from dual connect by rownum<=64) b;
  52.  
  53. 已选择 12800 行。
  54.  
  55. 已用时间:  00: 00: 05.00
  56.  
  57. 执行计划
  58. ----------------------------------------------------------
  59. Plan hash value: 894562235
  60.  
  61. ----------------------------------------------------------------------------------------
  62. | Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  63. ----------------------------------------------------------------------------------------
  64. |   0 | SELECT STATEMENT                |      |   186 |   366K|    70   (0)| 00:00:01 |
  65. |   1 |  MERGE JOIN CARTESIAN           |      |   186 |   366K|    70   (0)| 00:00:01 |
  66. |   2 |   VIEW                          |      |     1 |       |     2   (0)| 00:00:01 |
  67. |   3 |    COUNT                        |      |       |       |            |          |
  68. |*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
  69. |   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
  70. |   6 |   BUFFER SORT                   |      |   186 |   366K|    70   (0)| 00:00:01 |
  71. |   7 |    TABLE ACCESS FULL            | T1   |   186 |   366K|    68   (0)| 00:00:01 |
  72. ----------------------------------------------------------------------------------------
  73.  
  74. Predicate Information (identified by operation id):
  75. ---------------------------------------------------
  76.  
  77.    4 - filter(ROWNUM<=64)
  78.  
  79. Note
  80. -----
  81.    - dynamic statistics used: dynamic sampling (level=2)
  82.  
  83.  
  84. 统计信息
  85. ----------------------------------------------------------
  86.           7  recursive calls
  87.           0  db block gets
  88.         378  consistent gets
  89.           0  physical reads
  90.           0  redo size
  91.      280133  bytes sent via SQL*Net to client
  92.        9927  bytes received via SQL*Net from client
  93.         855  SQL*Net roundtrips to/from client
  94.           2  sorts (memory)
  95.           0  sorts (disk)
  96.       12800  rows processed
  97.  
  98. --管道表函数
  99. bill@ORCL> select       id, item_txt
  100.   2  from       table(refcur_pkg.f_cartesian2(cursor(
  101.   3                     select  id, length(text)-length(replace(text, chr(10))), text
  102.   4                     from    t1
  103.   5                     )));
  104.  
  105. 已选择 12800 行。
  106.  
  107. 已用时间:  00: 00: 05.16
  108.  
  109. 执行计划
  110. ----------------------------------------------------------
  111. Plan hash value: 2991304848
  112.  
  113. ---------------------------------------------------------------------------------------------------
  114. | Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  115. ---------------------------------------------------------------------------------------------------
  116. |   0 | SELECT STATEMENT                   |              |  8168 |   271K|    29   (0)| 00:00:01 |
  117. |   1 |  VIEW                              |              |  8168 |   271K|    29   (0)| 00:00:01 |
  118. |   2 |   COLLECTION ITERATOR PICKLER FETCH| F_CARTESIAN2 |  8168 |       |    29   (0)| 00:00:01 |
  119. |   3 |    TABLE ACCESS FULL               | T1           |   186 |   366K|    68   (0)| 00:00:01 |
  120. ---------------------------------------------------------------------------------------------------
  121.  
  122. Note
  123. -----
  124.    - dynamic statistics used: dynamic sampling (level=2)
  125.  
  126.  
  127. 统计信息
  128. ----------------------------------------------------------
  129.         266  recursive calls
  130.           0  db block gets
  131.         529  consistent gets
  132.           0  physical reads
  133.           0  redo size
  134.      236103  bytes sent via SQL*Net to client
  135.        9927  bytes received via SQL*Net from client
  136.         855  SQL*Net roundtrips to/from client
  137.           0  sorts (memory)
  138.           0  sorts (disk)
  139.       12800  rows processed
  140.  
  141.  
  142. bill@ORCL> select       id, item_txt
  143.   2  from       table(refcur_pkg.f_cartesian3(cursor(
  144.   3                     select  id, length(text)-length(replace(text, chr(10))), text
  145.   4                     from    t1
  146.   5                     )));
  147.  
  148. 已选择 12800 行。
  149.  
  150. 已用时间:  00: 00: 00.29
  151.  
  152. 执行计划
  153. ----------------------------------------------------------
  154. Plan hash value: 2831580648
  155.  
  156. ---------------------------------------------------------------------------------------------------
  157. | Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  158. ---------------------------------------------------------------------------------------------------
  159. |   0 | SELECT STATEMENT                   |              |  8168 |   271K|    29   (0)| 00:00:01 |
  160. |   1 |  VIEW                              |              |  8168 |   271K|    29   (0)| 00:00:01 |
  161. |   2 |   COLLECTION ITERATOR PICKLER FETCH| F_CARTESIAN3 |  8168 |       |    29   (0)| 00:00:01 |
  162. |   3 |    TABLE ACCESS FULL               | T1           |   186 |   366K|    68   (0)| 00:00:01 |
  163. ---------------------------------------------------------------------------------------------------
  164.  
  165. Note
  166. -----
  167.    - dynamic statistics used: dynamic sampling (level=2)
  168.  
  169.  
  170. 统计信息
  171. ----------------------------------------------------------
  172.         278  recursive calls
  173.           0  db block gets
  174.         555  consistent gets
  175.           0  physical reads
  176.         124  redo size
  177.      228407  bytes sent via SQL*Net to client
  178.        9927  bytes received via SQL*Net from client
  179.         855  SQL*Net roundtrips to/from client
  180.           0  sorts (memory)
  181.           0  sorts (disk)
  182.       12800  rows processed
复制代码
整个这些测试,比较,对我震撼是比较大的。用sql来实现笛卡尔然后取不同的数据,似乎是个天经地义适合sql来处理的事情,可惜到最后我都不知道这究竟是不适合sql,还是oracle没把这个实现好?大家不妨用别的数据库来测测?
不过,从此以后,oracle里面,再有这种需求,大家不妨参考这里plsql的思路来做。
tag标签:
------分隔线----------------------------
------分隔线----------------------------