Oracle 记一次sql优化（有关子查询和关联查询）

野性酷女 2021-09-28 20:22 500阅读 0赞

最近同事有条查询sql，需要做下优化。其实最后得出的结论，就是关联查询速度快于子查询

写这篇的目的主要是把尝试过的方法给记录下，同时复习下有一段时间没有用过的oracle存储过程、自定义函数、包的写法

一、问题说明

为了说清楚问题的核心，我把跟业务逻辑有关的东西去掉了，简单说下需要查的东西：

表结构：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA5OTk4MDk_size_16_color_FFFFFF_t_70][]

现在需要查询各班级下的所有学生的名称（两个字段：班级id classId，学生名称 studentName），查出来的效果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA5OTk4MDk_size_16_color_FFFFFF_t_70 1][]

二、解决问题

写sql常用的方式有关联查询和子查询

1. 如果关联查询的话，需要先想下对于某个班级（t\_class的l\_id），如何才能通过已有的班级下的所有学生id（用逗号分隔，t\_class的vc\_student\_id）关联到所有相应学生id的行。这个关联条件一开始没想到，所以刚开始这个方案被我排除掉了，后面在回头看以前写过的业务sql时想到了方法。这个效率是最高的

select tc.l_id as classId, wm_concat(ts.vc_student_name) as studentName
      from t_class tc
      left join t_student ts
        on instr(',' || tc.vc_student_id || ',', ',' || ts.l_id || ',') > 0
     group by tc.l_id;

2. 如果是子查询的话，先会尝试这样写

select tc.l_id as classId,
      (select wm_concat(ts.vc_student_name) from t_student ts where ts.l_id in (tc.vc_student_id)) as studentName
    from t_class tc

但很遗憾，oracle不支持把一个字符串直接放到in当中（此时会报“ORA-01722：无效数字”错误）

我们现在需要让in中的东西是动态的，而非

select wm_concat(ts.vc_student_name) from t_student ts where ts.l_id in (1, 2, 3);

我之前写的一篇博客 [\[oracle自定义函数\]pipe row 将一个字符串拆分成多条记录][oracle_pipe row] 就是解决这个问题的

用里面写的row\_split或row\_split2用可以解决这个问题，sql如下：

select tc.l_id as classId,
           (select wm_concat(ts.vc_student_name)
              from t_student ts
             where ts.l_id in
                   (select * from table(row_split(tc.vc_student_id, ',')))) as studentName
      from t_class tc;

select tc.l_id as classId,
           (select wm_concat(ts.vc_student_name)
              from t_student ts
             where ts.l_id in
                   (select * from table(row_split2(tc.vc_student_id, ',')))) as studentName
      from t_class tc;

解决问题的话，看到这里就可以结束了。

下面的内容是我为了复习下oralce存储过程、自定义函数、包的写法，把编写sql时考虑到的其他情况，以及整个操作步骤写到一个包里了，同时也是为了让文件结构显得更清晰

三、探索过程

1. 为避免涉及业务信息，提取出纯技术相关的问题，需要先造数据（initData存储过程）

2. 为了便于多次进行测试，我编写了一些创建表和序列（createTableAndSequence）、以及删除表和序列（deleteTableAndSequence）的存储过程

3. 编写一些测试sql，以下是用能想到的办法写的sql，顺便把在我电脑上的测试时间（在plsql中查询出全部）列出来

在运行以下测试sql之前，需要先运行

begin
      -- 创建表和序列
      generate_data_pkg.createTableAndSequence();
      -- 初始化数据
      generate_data_pkg.initData();
    end;

我的电脑配置：

处理器：i7-7700hq@2.8GHz

内存：8G

--使用函数 8.744s
    select tc.l_id as classId,
           (select wm_concat(ts.vc_student_name)
              from t_student ts
             where ts.l_id in
                   (select * from table(generate_data_pkg.row_split(tc.vc_student_id, ',')))) as studentName
      from t_class tc;
    
    --使用函数 64.045s
    select tc.l_id as classId,
           (select wm_concat(ts.vc_student_name)
              from t_student ts
             where ts.l_id in
                   (select * from table(generate_data_pkg.row_split2(tc.vc_student_id, ',')))) as studentName
      from t_class tc;
    
    --使用函数 5.955s
    select tc.l_id as classId,
           generate_data_pkg.findStudentNames(tc.vc_student_id) as studentName
      from t_class tc;
    
    --使用函数 8.942s
    select tc.l_id as classId,
           generate_data_pkg.findStudentNames2(tc.vc_student_id) as studentName
      from t_class tc;
    
    --连接查询 4.589s
    select tc.l_id as classId, wm_concat(ts.vc_student_name) as studentName
      from t_class tc
      left join t_student ts
        on instr(',' || tc.vc_student_id || ',', ',' || ts.l_id || ',') > 0
     group by tc.l_id;

附上oracle包的代码：

写下面这个包可能遇到的问题：

1. dml（插入、修改、删除）语句是如果动态拼接的，直接写的话编译会报错（在操作的表还未创建的情况下）；需要通过execute immediate来执行

2. ddl直接写在存储过程里的话，编译会直接报错；需要通过execute immediate来执行

3. 包体里需要直接执行ddl的情况下，如果按正常方式写包声明，即使用户本身有建表权限，存储过程执行的时候仍然会报权限不足，解决方法：加authid current\_user（https://blog.csdn.net/jerryitgo/article/details/79220598）

--创建需要返回的类型
    create or replace type t_ret_table
        as table of varchar2(1000);
    
    --创建包声明
    create or replace package generate_data_pkg authid current_user
    as  
      /**
      * 生成逗号分隔字符串
      **/
      --使用示例：select getCommaStr(2, 16) from dual;
      function getCommaStr(var_startnum number, var_endnum number) return varchar2;
    
      /**
      * 初始化表数据
      **/
      --使用示例：
      /**
      begin
        generate_data_pkg.initData();
      end;
      **/
      procedure initData;
    
      /**
      * 创建表和序列
      **/
      --使用示例：
      /**
      begin
        generate_data_pkg.createTableAndSequence();
      end;
      **/
      procedure createTableAndSequence;
    
      /**
      * 删除表和序列
      **/
      --使用示例：
      /**
      begin
        generate_data_pkg.deleteTableAndSequence();
      end;
      **/
      procedure deleteTableAndSequence;
      
      --========================================================================================
      /**
      * 将字符串分割为多条记录
      **/
      --使用示例：select * from table(generate_data_pkg.row_split(',111,222,', ','));
      function row_split(var_str varchar2, var_split in varchar2) return t_ret_table pipelined;
      
      --使用示例：select * from table(generate_data_pkg.row_split2(',111,222,', ','));
      function row_split2(var_str varchar2, var_split in varchar2) return t_ret_table;
      
      /**
      * 批量传入id，查询对应的名称
      **/
      --使用示例：select generate_data_pkg.findStudentNames('1,2,3') from dual;
      function findStudentNames(inStudentId varchar2) return varchar2;
      
      --使用示例：select generate_data_pkg.findStudentNames2('1,2,3') from dual;
      function findStudentNames2(inStudentId varchar2) return varchar2;
      --========================================================================================
    
    end generate_data_pkg;
    
    --创建包体
    create or replace package body generate_data_pkg
    as
      /**
      * 生成逗号分隔字符串
      **/
      function getCommaStr(var_startnum number, var_endnum number) return varchar2
        as
        resultStr varchar2(500); --返回字符串
      begin
        for i in var_startnum..var_endnum loop
          resultStr:=resultStr||i;
          if i!=var_endnum then
             resultStr:=resultStr||',';
          end if;
        end loop;
        --dbms_output.put_line(resultStr);
        return resultStr;
      end getCommaStr;
    
      /**
      * 初始化表数据
      **/
      procedure initData as
        var_startnum number;
        var_endnum number;
        var_commonstr varchar2(500);
      begin
        --生成学生表数据（插入26条数据）
        for i in 97..122 loop
          --dbms_output.put_line(chr(i)||chr(i));
          --insert into t_student(l_id, vc_student_name) values(seq_t_student_id.nextval, chr(i)||chr(i));
          execute immediate 'insert into t_student(l_id, vc_student_name) values(seq_t_student_id.nextval, '''||chr(i)||chr(i)||''')';
        end loop;
    
        --生成班级表数据（插入10000条数据）
        /**
        生成起始随机数（比如生成一个区间为[2,14]，起始都是随机数，
        这里2是从[1,13]范围中生成的一个随机数，14是从[14,26]范围中生成的一个随机数）
        随机数生成参考链接
        生成[1,27)范围，也就是[1,26]的随机整数（http://m.zhizuobiao.com/oracle/oracle-18091000127/）
        select trunc(dbms_random.value(1,27)) from dual;
        **/
        for i in 1..10000 loop
          select trunc(dbms_random.value(1,14)) into var_startnum from dual; --[1,13]的随机数
          select trunc(dbms_random.value(14,27)) into var_endnum from dual; --[14,26]的随机数
          select generate_data_pkg.getCommaStr(var_startnum, var_endnum) into var_commonstr from dual;  
        
          --dbms_output.put_line(i);
          --insert into t_class(l_id, vc_class_name, vc_student_id) values(seq_t_class_id.nextval, '班级'||i, var_commonstr);
          execute immediate 'insert into t_class(l_id, vc_class_name, vc_student_id) values(seq_t_class_id.nextval, '
                  ||'''班级'||i||''', '''||var_commonstr||''')';
        end loop;
        commit;
      end initData;
    
      /**
      * 创建表和序列
      **/
      procedure createTableAndSequence as
      begin
        --创建t_student序列
        execute immediate 'create sequence seq_t_student_id
        minvalue 1
        start with 1
        increment by 1
        cache 20';
    
        --创建t_student表（学生表）
        execute immediate 'create table t_student(
          l_id number,
          vc_student_name varchar2(100)
        )';
    
        --创建t_class序列
        execute immediate 'create sequence seq_t_class_id
        minvalue 1
        start with 1
        increment by 1
        cache 20';
    
        --创建t_class表（班级表）
        execute immediate 'create table t_class(
          l_id number,
          vc_class_name varchar2(100),
          vc_student_id varchar2(500)
        )';
      end createTableAndSequence;
    
      /**
      * 删除表和序列
      **/
      procedure deleteTableAndSequence as
      begin
        --删除t_student序列
        execute immediate 'drop sequence seq_t_student_id';
    
        --删除t_student表（学生表）
        execute immediate 'drop table t_student';
    
        --删除t_class序列
        execute immediate 'drop sequence seq_t_class_id';
    
        --删除t_class表（班级表）
        execute immediate 'drop table t_class';
      end deleteTableAndSequence;
      
      /**
      * 将字符串分割为多条记录
      **/
      function row_split(var_str varchar2, var_split in varchar2)
        return t_ret_table pipelined as
        var_tmp varchar2(1000);
        var_element varchar2(1000);
        n_length number:=length(var_split);
      --将字符串分割为多条记录
      begin
        /*
          对输入的字符串做预处理，去掉两端的分隔符
        （参考https://jingyan.baidu.com/article/3a2f7c2e72324e26afd6119a.html）
        */
        var_tmp := trim(both var_split from var_str);
        --只要字符串中存在分隔符，则继续执行将分隔出来的字符取出的操作
        while instr(var_tmp, var_split) > 0 loop
          var_element := substr(var_tmp, 1, instr(var_tmp, var_split)-1);
          -- 每取完字符串里的分隔的字符，该字符将从原始字符串中剔除
          var_tmp := substr(var_tmp,
                            instr(var_tmp, var_split)+n_length,
                            length(var_tmp));
          pipe row(var_element);
        end loop;
        pipe row(var_tmp);
        return;
      end row_split;
      
      function row_split2(var_str varchar2, var_split in varchar2)
        return t_ret_table as
        v_ret t_ret_table;
        var_split_str varchar2(200);
        --将字符串分割为多条记录
      begin
        var_split_str:='[^'||var_split||']+';
        select regexp_substr(var_str, var_split_str, 1, level) bulk collect into v_ret
            from dual
          connect by regexp_substr(var_str, var_split_str, 1, level) is not null;
        return v_ret;
      end row_split2;
      
      /**
      * 批量传入id，查询对应的名称
      **/
      function findStudentNames(inStudentId varchar2)
        return varchar2 as
        outStudentName varchar2(2000);
      begin
        execute immediate 'select wm_concat(ts.vc_student_name)
          from t_student ts
         where ts.l_id in (select * from table(generate_data_pkg.row_split('''||inStudentId||''', '','')))'
         into outStudentName;
        return outStudentName;
      end findStudentNames;
      
      function findStudentNames2(inStudentId varchar2)
        return varchar2 as
        outStudentName varchar2(2000);
      begin
        execute immediate 'select wm_concat(ts.vc_student_name)
          from t_student ts
         where ts.l_id in
               (select regexp_substr('''||inStudentId||''', ''[^,]+'', 1, level) as column_value 
                  from dual  
                connect by regexp_substr('''||inStudentId||''', ''[^,]+'', 1, level) is not null)'
           into outStudentName;
        return outStudentName;
      end findStudentNames2;
    
    end generate_data_pkg;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA5OTk4MDk_size_16_color_FFFFFF_t_70]: /images/20210923/41358f4724884bfe8af5ff361a656e37.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA5OTk4MDk_size_16_color_FFFFFF_t_70 1]: /images/20210923/b5f45619d61c4f23af611afe8de50447.png
[oracle_pipe row]: https://blog.csdn.net/u010999809/article/details/79825731