一、问题描述
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