SQL优化案例 - REGEXP_LIKE 非等值关联优化案例 - 《SQL优化入门》

本案例为好友南京越烟（QQ: 843999405）分享。

一个存储过程从周五晚上执行了到了周一还没有执行完，存储过程代码如下。

declare
  isMatch   Boolean := false；
  dealPnCnt number(10) := 0；
begin
  for c_no_data in （select nbn.no, 69 as partition_id
                      from TMP_NBR_NO_XXXX nbn
                     where nbn.level_id = 1
                       and length(nbn.no) = 8） loop
    dealPnCnt := dealPnCnt + 1；
    for c_data in （select nli.*, nl.nbr_level_id
                     from tmp_xxx_item nli,
                          a_level_item nl2i,
                          b_level_item nl,
                          c_level_item ns2l
                    where nli.nbr_level_item_id = nl2i.nbr_level_item_id
                      and nl2i.nbr_level_id = nl.nbr_level_id
                      and nl.nbr_level_id = ns2l.nbr_level_id
                      and ns2l.area_id = c_no_data.partition_id
                      and ns2l.res_spec_id = 6039
                      and ns2l.nbr_level_id between 201 and 208
                    order by nl2i.priority） loop
      if （regexp_like(c_no_data.no, c_data.expression)） then
        update TMP_NBR_NO_XXXX n
           set n.level_id = c_data.nbr_level_id
         where n.no = c_no_data.no；
        exit；
      end if；
    end loop；
    if mod（dealPnCnt, 5000） = 0 then
      commit；
    end if；
  end loop；
end；
TMP_NBR_NO_XXXX 共有 400w 行数据，180MB。
select nli.*， nl.nbr_level_id
  from tmp_xxx_item nli，
       a_level_item nl2i，
       b_level_item nl，
       c_level_item ns2l
 where nli.nbr_level_item_id = nl2i.nbr_level_item_id
   and nl2i.nbr_level_id = nl.nbr_level_id
   and nl.nbr_level_id = ns2l.nbr_level_id
   and ns2l.area_id = c_no_data.partition_id
   and ns2l.res_spec_id = 6039
   and ns2l.nbr_level_id between 201 and 208
 order by nl2i.priority；

上面 SQL 查询返回 43 行数据。

在 5.1 节提到过，嵌套循环就是一个 LOOP 循环，LOOP 套 LOOP 相当于笛卡儿积。该 PLSQL 代码中有 LOOP 套 LOOP 的情况，这就导致 UPDATE TMP_NBR_NO_XXXX 要执行（400 万*43）次，TMP_NBR_NO_XXXX.no 列没有索引，TMP_NBR_NO_XXXX 每次更新都要进行全表扫描。这就是为什么存储过程从周五执行到周一还没执行完。

大家可能会问，为什么不用 MERGE INTO 对 PLSQL 代码进行改写呢？PLSQL 代码中是用 regexp_like（c_no_data.no, c_data.expression）进行关联的，使用 like，regexp_like 关联，无法走 HASH 连接，也无法走排序合并连接，两表只能走嵌套循环并且被驱动表无法走索引。如果强行使用 MERGE INTO 进行改写，因为该 SQL 执行时间很长，会导致 UNDO 不释放，所以，我们没有采用 MERGE INTO 对代码进行改写。

大家可能也会问，为什么不对 TMP_NBR_NO_XXXX.no 建立索引呢？这是因为关联更新可以采用 ROWID 批量更新，所以没有采用建立索引方法优化。

下面我们采用 ROWID 批量更新方法改写上面 PLSQL，为了方便大家阅读 PLSQL 代码，先创建一个临时表用于存储 43 记录。

create table TMP_DATE_TEST
(
  expression   VARCHAR2(255) not null,
  nbr_level_id NUMBER(9) not null,
  priority     NUMBER(8) not null
);
insert into  TMP_DATE_TEST 
  select  nli.expression, nl.nbr_level_id, priority   from tmp_xxx_item   nli,
                               a_level_item nl2i,
                               b_level_item        nl,
                               c_level_item ns2l
                    where nli.nbr_level_item_id = nl2i.nbr_level_item_id
                      and nl2i.nbr_level_id = nl.nbr_level_id
                      and nl.nbr_level_id = ns2l.nbr_level_id
                      and ns2l.area_id = 69
                      and ns2l.res_spec_id = 6039
                      and ns2l.nbr_level_id between 201 and 208;

我们创建另外一个临时表，用于存储要被更新的表的 ROWID 以及过滤条件字段。

create table TMP_NBR_NO_XXXX_TEXT
(
  rid  ROWID,
  no   VARCHAR2(255),
);  
 insert into   TMP_NBR_NO_XXXX_TEXT 
   select rowid rid,  nbn.no, from   TMP_NBR_NO_XXXX nbn where  nbn.level_id=1 and length(nbn.no)= 8 ;

改写之后的 PLSQL 代码如下。

declare
  type rowid_table_type is table of rowid index by pls_integer;
  updateCur sys_refcursor;
  v_rowid   rowid_table_type;
  v_rowid2  rowid_table_type;
begin
  for c_no_data in (select t.expression, t.nbr_level_id, t.priority
                      from TMP_DATE_TEST t
                     order by 3) loop
    open updateCur for
      select rid
        from TMP_NBR_NO_XXXX_TEXT nbn
       where regexp_like(nbn.no, c_no_data.expression);
    loop
      fetch updateCur bulk collect
        into v_rowid LIMIT 20000;
      forall i in v_rowid.FIRST .. v_rowid.LAST
        update TMP_NBR_NO_XXXX
           set level_id = c_no_data.nbr_level_id
         where rowid = v_rowid(i);
      commit;
      exit when updateCur%notfound;
    end loop;
    CLOSE updateCur;
  end loop;
end;

改写后的 PLSQL 能在 4 小时左右执行完。有没有什么办法进一步优化呢？单个进程能在 4 小时左右执行完，如果开启 8 个并行进程，那应该能在 30 分钟左右执行完。但是 PLSQL 怎么开启并行呢？正常情况下 PLSQL 是无法开启并行的，如果我们直接在多个窗口中执行同一个 PLSQL 代码，会遇到锁争用，如果能解决锁争用，在多个窗口中执行同一个 PLSQL 代码，这样就变相实现了 PLSQL 开并行功能。在第 8 章提到过，可以利用 ROWID 切片变相实现并行。

select DBMS_ROWID.ROWID_CREATE(1, c.oid, e.RELATIVE_FNO, e.BLOCK_ID, 0) minrid,
       DBMS_ROWID.ROWID_CREATE(1,
                               c.oid,
                               e.RELATIVE_FNO,
                               e.BLOCK_ID + e.BLOCKS - 1,
                               10000) maxrid
  from dba_extents e,
       (select max(data_object_id) oid
          from dba_objects
         where object_name = upper('TMP_NBR_NO_XXXX_TEXT')
           and owner = upper('RESCZ2')
           and data_object_id is not null) c
 where e.segment_name = 'TMP_NBR_NO_XXXX_TEXT'
   and e.owner = 'RESCZ2';

但是这时我们发现，切割出来的数据分布严重不均衡，这是因为创建表空间的时候没有指定 uniform size 的 Extent。于是我们新建一个表空间，指定采用 uniform size 方式管理 Extent。

create tablespace TBS_BSS_FIXED  datafile  
            '/oradata/osstest2/tbs_bss_fixed_500.dbf' 
       size 500M extent management local uniform size 128k;

我们重建一个表用来存储要被更新的 ROWID。

create table RID_TABLE
(
  rowno  NUMBER,
  minrid VARCHAR2(18),
  maxrid VARCHAR2(18)
) ;

我们将 ROWID 插入到新表中。

insert into rid_table
  select rownum rowno,
         DBMS_ROWID.ROWID_CREATE(1, c.oid, e.RELATIVE_FNO, e.BLOCK_ID, 0) minrid,
         DBMS_ROWID.ROWID_CREATE(1,
                                 c.oid,
                                 e.RELATIVE_FNO,
                                 e.BLOCK_ID + e.BLOCKS - 1,
                                 10000) maxrid
    from dba_extents e,
         (select max(data_object_id) oid
            from dba_objects
           where object_name = upper('TMP_NBR_NO_XXXX_TEXT')
             and owner = upper('RESCZ2')
             and data_object_id is not null) c
   where e.segment_name = 'TMP_NBR_NO_XXXX_TEXT'
     and e.owner = 'RESCZ2';

这样 RID_TABLE 中每行指定的数据都很均衡，大概 4 035 条数据。最终更改的 PLSQL 代码如下。

create or replace  procedure  pro_phone_grade(flag_num in number)
as 
 type rowid_table_type is table of  rowid index  by  pls_integer;  
  updateCur  sys_refcursor;
 v_rowid  rowid_table_type;
 v_rowid2  rowid_table_type;
begin
for  rowid_cur in (select  *  from  rid_table  where mod(rowno, 8)=flag_num
 loop
    for c_no_data in (select t.expression, t.nbr_level_id, t.priority  from TMP_DATE_TEST t order by 3 ) 
       loop
         open  updateCur  for  select rid,rowid  from TMP_NBR_NO_XXXX_TEXT  nbn
           where rowid between rowid_cur.minrid and rowid_cur.maxrid  
          and regexp_like(nbn.no, c_no_data.expression);
          loop
            fetch updateCur  bulk collect  into  v_rowid, v_rowid2  LIMIT 20000;
              forall i in v_rowid.FIRST ..v_rowid.LAST
              update TMP_NBR_NO_XXXX  set  level_id = c_no_data.nbr_level_id  where rowid = v_rowid(i); 
               commit;
            exit when  updateCur%notfound;
         end loop; 
         CLOSE updateCur; 
       end loop;
   end loop;
end;

然后我们在 8 个窗口中同时运行以上 PLSQL 代码。

begin
pro_phone_grade(0);
end;
begin
pro_phone_grade(1);
end;
begin
pro_phone_grade(2);
end;
.....
begin
pro_phone_grade(7);
end;

最终我们能在 29 分钟左右执行完所有存储过程。本案例经典之处就在于 ROWID 切片实现并行，同时考虑到了数据分布对并行的影响，其次还使用了 ROWID 关联更新技巧。