案例背景

在项目中遇到这样的一个需求，从设备信息表和设备使用记录表中统计每个单位有效的设备信息和未被使用过的设备数量，开发商做成了full join方式，下面模拟一下场景，t1表是设备信息表，t2表是设备使用记录表，Bureau_code是单位code。

drop table if exsits t1;
create table t1 (id int primary key,c1 int,bureau_code varchar2(4))
partition by list(bureau_code)(
partition P_1 values('0301'),
partition P_2 values('0302'),
partition P_3 values('0303'),
partition P_4 values('0304'),
partition P_5 values('0305'),
partition P_6 values('0306'),
partition P_7 values('0307'),
partition P_8 values('0308'),
partition P_9 values('0309'),
partition P_10 values('0310')
);

insert into t1 select level,dbms_random.value(1,1000),'0'||to_char(round(dbms_random.value(301,310),0)) from dual connect by level<=2000000;
commit;
drop table if exsits t2;
create table t2 (id int primary key,d1 int,bureau_code varchar2(4))
partition by list(bureau_code)(
partition P_1 values('0301'),
partition P_2 values('0302'),
partition P_3 values('0303'),
partition P_4 values('0304'),
partition P_5 values('0305'),
partition P_6 values('0306'),
partition P_7 values('0307'),
partition P_8 values('0308'),
partition P_9 values('0309'),
partition P_10 values('0310')
);
insert into t2 select level,dbms_random.value(1,1000),'0'||to_char(round(dbms_random.value(301,310),0)) from dual connect by level<=1000000;
commit;
dbms_stats.gather_table_stats(USER,'T1',null,100);
dbms_stats.gather_table_stats(USER,'T2',null,100);
create index IDX_DM_T2 on T2 (d1) global;
语句
with t as
     (select tt1.bureau_code, 
               accountTotal,
               notused_cnt
          from (select count(1) accountTotal,bureau_code from t1 group by bureau_code) tt1
     full join (select count(1) notused_cnt,
                        t1.bureau_code
                   from t1,
                        t2
                  where t1.id=t2.d1(+)
                    and t2.d1 is null
               group by t1.bureau_code) tt2
            on tt1.bureau_code=tt2.bureau_code
     )
select * from t;
计划：
1   #NSET2: [1046, 10, 100] 
2     #PRJT2: [1046, 10, 100]; exp_num(3), is_atom(FALSE) 
3       #HASH FULL JOIN2: [1046, 10, 100]; key_num(1), mix_aagr(0), mix_dist(0)  KEY(TT1.BUREAU_CODE=TT2.BUREAU_CODE)
4         #PRJT2: [368, 10, 48]; exp_num(2), is_atom(FALSE) 
5           #PARALLEL: [368, 10, 48]; scan_type(FULL), key_num(0, 0, 0), simple(0)
6             #HAGR2: [368, 10, 48]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(T1.BUREAU_CODE) 
7               #CSCN2: [229, 2000000, 48]; INDEX33555557(T1); btr_scan(1)
8         #PRJT2: [676, 10, 52]; exp_num(2), is_atom(FALSE) 
9           #HAGR2: [676, 10, 52]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(T1.BUREAU_CODE) 
10            #SLCT2: [672, 50000, 52]; T2.D1 IS NULL
11              #HASH RIGHT JOIN2: [606, 2000000, 52]; key_num(1), ret_null(0), KEY(T2.D1=T1.ID)
12                #PARALLEL: [105, 1000000, 4]; scan_type(FULL), key_num(0, 0, 0), simple(0)
13                  #SSCN: [105, 1000000, 4]; IDX_DM_T2(T2); btr_scan(1)
14                #PARALLEL: [231, 2000000, 52]; scan_type(FULL), key_num(0, 0, 0), simple(0)
15                  #CSCN2: [231, 2000000, 52]; INDEX33555557(T1); btr_scan(1)

分析

语句中可以分析到t2表存储着使用记录，使用一次就记录一次，t1表是有效的设备信息，再根据业务需求分析，这里主要是要从有效的信息中获取未使用的设备信息，t1表总数是有效的，T2表中没有t1表使用记录的设备数据就是未使用的。那么整个需求主要依赖于t1表，t2表作用是获取未使用的记录。所以根据需求描述，可以使用left join去实现。
Left join: 返回左表中所有的记录以及右表中连接字段相等的记录，右表不满足的记录记为null。
根据left join的了解，右表null的数据就是未使用的数据。

优化改写

因此案例改写为：

select bureau_code, 
         count(1) accountTotal, 
         sum(case when tt.d1 is null then 1 else 0 end) as notused_cnt
    from (select t2.d1, 
                   t1.bureau_code 
              from t1 
         left join (select distinct d1 from t2) t2
                on t1.id=t2.d1) tt
group by bureau_code
计划：
1   #NSET2: [683, 10, 56] 
2     #PRJT2: [683, 10, 56]; exp_num(3), is_atom(FALSE) 
3       #HAGR2: [683, 10, 56]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(DMTEMPVIEW_889215128.TMPCOL0) 
4         #PRJT2: [542, 2000000, 56]; exp_num(2), is_atom(FALSE) 
5           #HASH RIGHT JOIN2: [542, 2000000, 56]; key_num(1), ret_null(0), KEY(T2.D1=T1.ID)
6             #PRJT2: [170, 1000, 4]; exp_num(1), is_atom(FALSE) 
7               #DISTINCT: [170, 1000, 4]
8                 #PARALLEL: [105, 1000000, 4]; scan_type(FULL), key_num(0, 0, 0), simple(0)
9                   #SSCN: [105, 1000000, 4]; IDX_DM_T2(T2); btr_scan(1)
10            #PARALLEL: [231, 2000000, 52]; scan_type(FULL), key_num(0, 0, 0), simple(0)
11              #CSCN2: [231, 2000000, 52]; INDEX33555557(T1); btr_scan(1)

这里是省去计算t1表的次数。

总结

这里应用到的优化改写思路：减少计算次数，提高效率，能一次性处理的就要一次性处理。