108一个SQL, 两种优化方法, 效果都还可以

数据运维 2023-08-26 大树 手机阅读

你们关心功能, 我来提升性能, 这里是, 今天介绍的这个案例来自一个金融行业的生产系统. 

我在对系统做主动性能检查时,发现了一个执行时间长达30多秒的SQL, 3个相关表的记录数都只有几十万条, 可能存在较大的优化空间. 经过分析,并与业务人员进行沟通, 我对SQL进行了改写(两种方法), 改写后的SQL执行效率都有几十倍的提升. 

下面是生成Test Case的脚本(测试环境:数据库版本19.17, Linux虚拟机@笔记本):

    --创建两个表(结构都一样):
    create table T_DEMO_648_QDII_H(i_code varchar2(20)
    ,beg_date date,end_date date,DP_CLOSE number);
    <br>
    create table T_DEMO_648_Wind_W(i_code varchar2(20)
    ,beg_date date,end_date date,DP_CLOSE number);
    <br>
    --为 T_DEMO_648_QDII_H表(以下简称H表)生成 74.4万记录, 
    --大概占用存储空间24M
    declare
    v_beg_date date :=date'2010-01-01';
    v_end_date date :=date'2023-08-01';
    c_date date;
    begin
    for i in 1..150 loop
    c_date:=v_beg_date;
    while v_end_date>c_date loop
    insert into T_DEMO_648_QDII_H values('C'||lpad(i,3,'0'),
    c_date,c_date+1,round(dbms_random.value(1,10)));
    c_date:=c_date+1;
    end loop;
    end loop;
    commit;
    end;
    /
    <br>
    ---为T_DEMO_648_Wind_W表(以下简称W表) 生成34w+记录, 
    --大概占用存储空间12M
    declare
    n number;
    v_beg_date date :=date'2010-01-01';
    v_end_date date :=date'2023-08-01';
    c_date date;
    begin
    for i in 1..150 loop
    c_date:=v_beg_date;
    while v_end_date>c_date loop
    n:=round(dbms_random.value(0,1)*0.6)*7+1;
    insert into T_DEMO_648_Wind_W values('C'||lpad(i,3,'0'),
    c_date,c_date+n,round(dbms_random.value(1,10)));
    c_date:=c_date+n;
    end loop;
    end loop;
    commit;
    end;
    /

    --收集两表统计信息:
    exec dbms_stats.gather_table_stats(user,'T_DEMO_648_QDII_H');
    exec dbms_stats.gather_table_stats(user,'T_DEMO_648_Wind_W');