一、问题描述
1、面临的问题
开发人员找到我方，希望我们可以给sql进行优化。
（1）当前开发使用with as语句进行数据查询，查询代码如下：
生产机查询时间大约109s。
(关于with as用法见：https://www.topunix.com/post-10515.html）
with tab as
(select b.scan_barcode, b.datetime_created, a.mo_code
from inv_transfer_detail a
inner join inv_transfer_detail_barcode b
on a.id = b.bill_line_id
where a.bill_no like 'MI%'
and a.mitem_code like '1151%'
and b.mitem_code like '1151%'
and a.to_warehouse_code = 'K009'
and a.datetime_created >= to_date('2023-03-01', 'yyyy/mm/dd')
and a.datetime_created <= to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
and b.datetime_created >= to_date('2023-03-01', 'yyyy/mm/dd')
and b.datetime_created <= to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss')),
dy as
(select p1.param_code, p1.param_alias
from sys_param p1
inner join sys_param_group p2
on p1.param_group_id = p2.id
and p2.param_group_code = 'CORONA_TYPE'),
tab1 as
(select a.SCAN_BARCODE,
a.mo_code,
row_number() over(partition by a.scan_barcode order by a.datetime_created desc) rn
from tab a
where not exists (select 1
from tab b
where a.scan_barcode != b.scan_barcode
and b.scan_barcode like a.scan_barcode || '%')
group by a.SCAN_BARCODE, a.mo_code, a.datetime_created),
tab2 as
(select a.SCAN_BARCODE,
t.label_length,
t.width,
c.thickness,
d.param_alias,
d.param_code,
t.label_weight
from tab1 a
inner join sfc_barcode_wip_serials t
on a.SCAN_BARCODE = t.serial_number
and a.mo_code = t.sub_mo_code
and t.sub_mo_code is not null
and t.stockin_time >= to_date('2023-04-01', 'yyyy/mm/dd')
and t.stockin_time <= to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
inner join sfc_mitem c on t.mitem_code = c.mitem_code
inner join dy d
on c.reforge_class = d.param_code
where a.rn = 1),
tab3 as
(select a.label_length,
a.param_code,
a.thickness,
count(*) JS,
sum(a.label_weight) / 10000 weight,
sum(A.WIDTH) WIDTH,
max(a.param_alias) param_alias,
case
when a.width <= 670 then
'0-670'
when a.width > 670 and a.width <= 1350 then
'670-1350'
when a.width > 1350 then
'1350'
end KDFW
from tab2 a
group by a.label_length,
a.param_code,
a.thickness,
case
when a.width <= 670 then
'0-670'
when a.width > 670 and a.width <= 1350 then
'670-1350'
when a.width > 1350 then
'1350'
end)
select * from tab3
（2）测试机查询时间大约为106s
2、优化思路
（1）索引优化
（2）语句优化
（3）统计信息收集
（4）语句跟踪诊断
二、优化流程
1、执行计划查看分析
（1）执行计划查看
（2）执行计划分析：
从上述执行计划可以看到两个问题：
1是针对inv_transfer_detail_barcode的索引，耗时很大，可能不是最有优索引，我们需要进行索引重建 2是with as产生的临时表：SYSS_ TEIP_ 0FD9D66D4_ C45330E2，走的是全表扫描，没有走索引，可能需要考虑进行索引创建。
2、给上述1创建索引
（1）创建索引
create index INV_TRANSFER_DETAIL_X053 on inv_transfer_detail(to_warehouse_code,datetime_created,bill_no,mitem_code);
（2）在查看查看执行计划
执行计划已经缩减到了264
（3）再次查询
时间并没有太大改善，依然实在106s左右。
3、测试with as 的临时表增加索引问题
（1）with as的技术壁垒
with as只能用于select查询功能，其语法使用过程，不支持创建使用create index。
--模拟sys_temp_***表，增加索引，是否可以快速获取数据
（2）删除可能存在的tab
drop table tab purge; drop table dy purge;
（3）创建表tab（模拟临时表）
注意：Oracle中有关于tab的视图，后面可以考虑其他名称。
create table tab as (select b.scan_barcode, b.datetime_created, a.mo_code
from mesprd.inv_transfer_detail a
inner join mesprd.inv_transfer_detail_barcode b
on a.id = b.bill_line_id
where a.to_warehouse_code = 'K009'
and a.datetime_created >= to_date('2023-03-01', 'yyyy/mm/dd')
and a.datetime_created <= to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
and a.bill_no like 'MI%'
and a.mitem_code like '1151%'
and b.mitem_code like '1151%'
and b.datetime_created >= to_date('2023-03-01', 'yyyy/mm/dd')
and b.datetime_created <= to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss'));
（4）创建表dy（模拟临时表）
create table dy as (select p1.param_code, p1.param_alias from mesprd.sys_param p1 inner join mesprd.sys_param_group p2 on p1.param_group_id = p2.id and p2.param_group_code = 'CORONA_TYPE');
（5）增加针对于表tab列scan_barcode的
create index idx_tab_barcode on tab(scan_barcode);
（6）执行剩余的语句
tab1 as
(select a.SCAN_BARCODE,
a.mo_code,
row_number() over(partition by a.scan_barcode order by a.datetime_created desc) rn
from tab a
where not exists (select 1
from tab b
where a.scan_barcode != b.scan_barcode
and b.scan_barcode like a.scan_barcode || '%')
group by a.SCAN_BARCODE, a.mo_code, a.datetime_created),
tab2 as
(select a.SCAN_BARCODE,
t.label_length,
t.width,
c.thickness,
d.param_alias,
d.param_code,
t.label_weight
from tab1 a
inner join sfc_barcode_wip_serials t
on a.SCAN_BARCODE = t.serial_number
and a.mo_code = t.sub_mo_code
and t.sub_mo_code is not null
and t.stockin_time >= to_date('2023-04-01', 'yyyy/mm/dd')
and t.stockin_time <=
to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
inner join sfc_mitem c
on t.mitem_code = c.mitem_code
inner join dy d
on c.reforge_class = d.param_code
where a.rn = 1),
tab3 as
(select a.label_length,
a.param_code,
a.thickness,
count(*) JS,
sum(a.label_weight) / 10000 weight,
sum(A.WIDTH) WIDTH,
max(a.param_alias) param_alias,
case
when a.width <= 670 then
'0-670'
when a.width > 670 and a.width <= 1350 then
'670-1350'
when a.width > 1350 then
'1350'
end KDFW
from tab2 a
group by a.label_length,
a.param_code,
a.thickness,
case
when a.width <= 670 then
'0-670'
when a.width > 670 and a.width <= 1350 then
'670-1350'
when a.width > 1350 then
'1350'
end)
select * from tab3
（6）执行时间-（0.3秒）
（7）总结
所以此思路是可行的，是因为临时表没有索引，导致查询性能降低，但是因为with as不支持内部创建索引，所以我们考虑全局临时表方式。
三、全局临时表的使用
可参见:https://www.topunix.com/post-10525.html
1、全局临时表的优缺点
（1）临时表分事务级临时表和会话级临时表。
事务级临时表只对当前事务有效，通过语句：ON COMMIT DELETE ROWS 指定。 会话级临时表对当前会话有效，通过语句：ON COMMIT PRESERVE ROWS语句指定。
（2）全局临时表的特点
--隔离性：数据只在会话或者事务级别可见。不同用户可以使用同一个临时表，但是看到的都是各自的数据。
--表上可以创建索引、视图、触发器等对象。
--索引只有在临时表是empty时可创建。
--临时表不产生数据的redo，但是会生成undo的redo。
--临时表目前只支持GLOBAL的，所以创建语句为create global temporary table XXX。
--使用truncate只对当前会话有效。
--不能export/import表上的数据，只能导入导出表定义。
--临时段在第一次insert或CATS时产生。
（3）缺点：
--表定义不能自动drop。
--临时表目前只支持GLOBAL。
--只有无会话时才能DDL，否则可能报错：ORA-14452: attempt to create, alter or drop an index on temporary table already in use
--临时表上默认不收集统计信息，如果需要收集统计信息，首先要确保临时表属性为ON COMMIT PRESERVE ROWS.
2、根据上述的特点，我们进行如下配置
--首先drop两个表tab和dy
--创建两个空的全局临时表，需要使用ON COMMIT DELETE ROWS（因为后续要进行创建索引，如果使用PRESERVE ROWS指定，创建时会报ORA-14452）。
--创建索引
--向临时表中插入数据，不要提交，一旦提交，数据则会被清空
--执行剩余语句，则可快速查询数据。
3、创建语句
drop table tab purge;
drop table dy purge;
--drop index idx_tab_barcode;
create global temporary table tab on commit delete rows
as
select b.scan_barcode, b.datetime_created, a.mo_code
from mesprd.inv_transfer_detail a
inner join mesprd.inv_transfer_detail_barcode b
on a.id = b.bill_line_id
where 1<>1;
create global temporary table dy on commit delete rows
as
select p1.param_code, p1.param_alias
from mesprd.sys_param p1
inner join mesprd.sys_param_group p2
on p1.param_group_id = p2.id
and p2.param_group_code = 'CORONA_TYPE'where 1<>1 ;
create index idx_tab_barcode on tab(scan_barcode);
insert into tab
select b.scan_barcode, b.datetime_created, a.mo_code
from mesprd.inv_transfer_detail a
inner join mesprd.inv_transfer_detail_barcode b
on a.id = b.bill_line_id
where a.to_warehouse_code = 'K009'
and a.datetime_created >= to_date('2023-03-01', 'yyyy/mm/dd')
and a.datetime_created <=
to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
and a.bill_no like 'MI%'
and a.mitem_code like '1151%'
and b.mitem_code like '1151%'
and b.datetime_created >= to_date('2023-03-01', 'yyyy/mm/dd')
and b.datetime_created <=
to_date('2023-04-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss');
--select * from tab;
insert into dy
select p1.param_code, p1.param_alias
from mesprd.sys_param p1
inner join mesprd.sys_param_group p2
on p1.param_group_id = p2.id
and p2.param_group_code = 'CORONA_TYPE' ;
--select * from dy;
with tab1 as
(select a.SCAN_BARCODE,
a.mo_code,
row_number() over(partition by a.scan_barcode order by a.datetime_created desc) rn
from tab a
where not exists (select 1
from tab b
where a.scan_barcode != b.scan_barcode
and b.scan_barcode like a.scan_barcode || '%')
group by a.SCAN_BARCODE, a.mo_code, a.datetime_created),
tab2 as
(select a.SCAN_BARCODE,
t.label_length,
t.width,
c.thickness,
d.param_alias,
d.param_code,
t.label_weight
from tab1 a
inner join mesprd.sfc_barcode_wip_serials t
on a.SCAN_BARCODE = t.serial_number
and a.mo_code = t.sub_mo_code
and t.sub_mo_code is not null
and t.stockin_time >= to_date('2023-03-01', 'yyyy/mm/dd')
and t.stockin_time <=
to_date('2023-03-02 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
inner join mesprd.sfc_mitem c
on t.mitem_code = c.mitem_code
inner join dy d
on c.reforge_class = d.param_code
where a.rn = 1) ,
tab3 as
(select a.label_length,
a.param_code,
a.thickness,
count(*) JS,
sum(a.label_weight) / 10000 weight,
sum(A.WIDTH) WIDTH,
max(a.param_alias) param_alias,
case
when a.width <= 670 then
'0-670'
when a.width > 670 and a.width <= 1350 then
'670-1350'
when a.width > 1350 then
'1350'
end KDFW
from tab2 a
group by a.label_length,
a.param_code,
a.thickness,
case
when a.width <= 670 then
'0-670'
when a.width > 670 and a.width <= 1350 then
'670-1350'
when a.width > 1350 then
'1350'
end)
select * from tab3
4、检查，发现查询只需要0.3-0.4s即可完成。
