SQL优化-不一样的行转列

  最近在客户现场，遇到开发人员说有一个场景在进行数据迁移的时候sql执行不动，大致有点列转行，然后再去关联其他的表，再进行转列的处理，OB版本是3.2.3，租户是oracle模式。我们先看一下表的数据形式，假设表名字CREDIT_INFO;

  表中的CUSTOMER_ID是用逗号分开的多个，我们需要CREDIT_INFO中的多个CUSTOMER_ID转换成CUSTOMER_NAME，如下表展示。

  开发人员的实现方式是创建了一个函数，在函数中对字段按照逗号进行拆分，然后关联客户表（CUST_INFO）获取客户姓名,函数的大致实现方式如下：

create or replace function getsplitname(customer_id_set varchar2,split_str varchar2)
       return varchar2
       is
           source_str varchar2(32767);
           return_str varchar2(32767);
           return_temp varchar2(32767);
           temp_str varchar2(32767);
           point_pos number;
       begin
           source_str:=customer_id_set;
           point_pos:=instr(source_str,split_str );
           while source_str>=1 loop
               temp_str := substr(source_str,1,point_pos-1);
               source_str:=substr(source_str,point_pos+1);
               point_pos:=instr(source_str,split_str);
               select customer_name into return_temp  from cust_info where customer_id=temp_str ;
               return_str:=return_str||','return_temp; 
           end loop;
           select customer_name into return_temp  from cust_info where customer_id=source_str;
           return_str:=return_str||','return_temp; 
           return return_str;
       end;

复制

sql代码实现方式如下：

insert /*+ENABLE_PARALLEL_DML PARALLEL(8)*/ into table_target 
select getsplitname(CUSTOMER_ID) from CREDIT_INFO;
复制

  我这里对sql语句做了简化，原sql还关联了很多其他的表，CREDIT_INFO表中够用60万条记录，总之这个sql非常慢，因为调用了函数，每一行数据都要通过这个函数进行子查询，于是我尝试对这个进行了改写,不使用函数，还是使用子查询的方式。改写的sql如下：

select 
select to_char(wm_concat(customer_name)) from cust_info where  cucstomer_id in(
                select  regexp_substr(ci.customer_id,'[^,]+',1,ROWNUM) id from dual connect by 
                rownum<=length(regexp_replace(ci.customer_id,'[^,]+'))+1) as customer_name 
from credit_info ci;  
复制

  尝试后效果仍然没有改善，而且这个子查询的查询计划比较奇怪，虽然customer_id是cust_info表的主键，但是查询计划还是全表扫描，只能怀疑ob对这个写法不是特别支持。于是我打算使用最简单的方法：
  创建一个存储过程，在存储过程中使用游标，对每一行的客户号进行处理。每一个客户号插入一条记录到临时表，以下是我的临时表。

CREATE TABLE CREDIT_MID_USER_TAB
(
  creditno        VARCHAR2(40) ,  --源表的管理字段
  USER_ID_SET    VARCHAR2(60),  --客户编号
  order_no  number   --排序字段，因为转换完以后需要与客户号的展示顺序一致
);

复制

存储过程脚本如下：

create or replace PROCEDURE CREDIT_USER_MID_FUN
is
    iPointNow NUMBER;
    PCREDITNO varchar2(32);
    pInputString VARCHAR2(32767);  
    sSource VARCHAR2(32767);  
    sTemp VARCHAR2(32767);
    sTemp_1 VARCHAR2(32767);
    iCount NUMBER;
 --定义游标，只处理存在多个客户号的情况，区分开是为了提升速度，单个客户的不需要再
循环中处理
 CURSOR cus_credit_cor is 
         select credit_no,CUSTOMER_ID from CREDIT_INFO CI where instr(CI.CUSTOMER_ID,',')>0 ;                
Begin   
    delete from CREDIT_MID_USER_TAB;
    commit;
    iCount:=0;
    open cus_credit_cor;
    LOOP
    fetch cus_credit_cor into pCreditNo,pInputString;
        exit when cus_credit_cor%NOTFOUND;
        iCount:=0;
        sSource := pInputString;
       
        iPointNow :=instr(sSource,',');

        while iPointNow>=1  loop
            sTemp:=substr(sSource,1,iPointNow-1);
            sSource:=substr(sSource,iPointNow+1);
            iPointNow :=instr(sSource,',');


            insert into CREDIT_MID_USER_TAB(creditno,USER_ID_SET,order_no) values(pCreditNo,sTemp,iCount);
            iCount:=iCount+1;
        END LOOP;

        sTemp:=sSource;

        insert into CREDIT_MID_USER_TAB(creditno,USER_ID_SET,order_no) values(pCreditNo,sTemp,iCount);

    END LOOP;
    close cus_credit_cor;   

    commit;
    
    --处理只有单个客户号的情况，
    iCount:=0;
    insert into CREDIT_MID_USER_TAB(creditno,USER_ID_SET)
    select 
    credit_no,
    CUSTOMER_ID
    from CREDIT_INFO CI where  instr(CI.CUSTOMER_ID,',')<1  ;
    COMMIT;
END;
复制

  最后的sql实现需要关联临时表，如下

select 
LISTAGG(CIT.MFCUSTOMERID,',') WITHIN GROUP(ORDER by CIT.ORDER_NO) NEW_USER_SET
from CREDIT_INFO Ci
left join CREDIT_MID_USER_TAB CIT
group by ci.creditno;
复制

  通过临时表的方式实现行转列，然后用通过LISTAGG函数实现了列转行，速度由原来的执行不动，到加上处理临时表的时间总共2分钟就可以执行完成。
  之前也发布了几篇关于OB的使用笔记，越复杂高大上的sql写法，可能对优化器本身都是一个考验，尽量简化sql语言，让优化器少一些负担，在编写sql脚本时就减少一点访问了路径，可能会事半功倍。避免一些因执行计划总是来回变动带来的效率不一致问题。