昨天在和论坛上一位朋友讨论一条update SQL的性能优化时，聊到了Oracle的TEMPORARY TABLE，感觉在某些情况下还是有些用的，这里备忘一下。
那位朋友的问题：
--------------------------------------
這段SQL能否能優化，實在太慢了
UPDATE HG_JHPC t
SET (WGSJ) = (select sum(t_qty)
from (SELECT MSI.SEGMENT1 ITEM,
to_char(MT.TRANSACTION_DATE, 'yyyy-mm-dd') T_date,
MT.TRANSACTION_QUANTITY T_QTY
FROM INV.MTL_MATERIAL_TRANSACTIONS MT,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE MT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
and MT.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
and mt.transaction_type_id in (17, 44)
AND MT.SUBINVENTORY_CODE = 'F010'
UNION ALL
select MSI.SEGMENT1 ITEM,
TO_CHAR(T.TRANSACTION_DATE, 'YYYY-MM-DD') T_date,
T.TRANSACTION_QUANTITY T_QTY
from inv.mtl_material_transactions t,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE T.INVENTORY_ITEM_ID(+) =
MSI.INVENTORY_ITEM_ID
and T.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
AND T.TRANSFER_SUBINVENTORY IN ('R010', 'R041')
AND T.TRANSACTION_TYPE_ID = 2
AND T.SUBINVENTORY_CODE = 'F010')
WHERE substr(ITEM, 3, 6) = substr(t.ITEM, 3, 6)
and T_DATE >= '2011-11-01'
AND T_DATE <= '2011-11-05'
and TO_CHAR(t.PC_DATE, 'YYYY-MM-DD') = '2011-11-06' --TO_CHAR(SYSDATE+1,'YYYY-MM-DD')
and SUBSTR(t.ZB_CLASS, 1, 2) = 'HX'
group by substr(item, 3, 6))
where exists
(select sum(t_qty)
from (SELECT MSI.SEGMENT1 ITEM,
to_char(MT.TRANSACTION_DATE, 'yyyy-mm-dd') T_date,
MT.TRANSACTION_QUANTITY T_QTY
FROM INV.MTL_MATERIAL_TRANSACTIONS MT,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE MT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
and MT.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
and mt.transaction_type_id in (17, 44)
AND MT.SUBINVENTORY_CODE = 'F010'
UNION ALL
select MSI.SEGMENT1 ITEM,
TO_CHAR(T.TRANSACTION_DATE, 'YYYY-MM-DD') T_date,
T.TRANSACTION_QUANTITY T_QTY
from inv.mtl_material_transactions t,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE T.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
and T.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
AND T.TRANSFER_SUBINVENTORY IN ('R010', 'R041')
AND T.TRANSACTION_TYPE_ID = 2
AND T.SUBINVENTORY_CODE = 'F010')
WHERE substr(ITEM, 3, 6) = substr(t.ITEM, 3, 6)
and T_DATE >= '2011-11-01'
AND T_DATE <= '2011-11-05'
and TO_CHAR(t.PC_DATE, 'YYYY-MM-DD') = '2011-11-06'--TO_CHAR(SYSDATE+1,'YYYY-MM-DD')
and SUBSTR(t.ZB_CLASS, 1, 2) = 'HX'
group by substr(item, 3, 6));
----------------------------------------
经询问后得知：
---------------------------------------
(select sum(t_qty)
from (SELECT MSI.SEGMENT1 ITEM,
to_char(MT.TRANSACTION_DATE, 'yyyy-mm-dd') T_date,
MT.TRANSACTION_QUANTITY T_QTY
FROM INV.MTL_MATERIAL_TRANSACTIONS MT,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE MT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
and MT.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
and mt.transaction_type_id in (17, 44)
AND MT.SUBINVENTORY_CODE = 'F010'
UNION ALL
select MSI.SEGMENT1 ITEM,
TO_CHAR(T.TRANSACTION_DATE, 'YYYY-MM-DD') T_date,
T.TRANSACTION_QUANTITY T_QTY
from inv.mtl_material_transactions t,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE T.INVENTORY_ITEM_ID(+) =
MSI.INVENTORY_ITEM_ID
and T.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
AND T.TRANSFER_SUBINVENTORY IN ('R010', 'R041')
AND T.TRANSACTION_TYPE_ID = 2
AND T.SUBINVENTORY_CODE = 'F010')
這段和 where exists
後面的括號裏面的是一樣的，
(SELECT MSI.SEGMENT1 ITEM,
to_char(MT.TRANSACTION_DATE, 'yyyy-mm-dd') T_date,
MT.TRANSACTION_QUANTITY T_QTY
FROM INV.MTL_MATERIAL_TRANSACTIONS MT,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE MT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
and MT.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
and mt.transaction_type_id in (17, 44)
AND MT.SUBINVENTORY_CODE = 'F010'
UNION ALL
select MSI.SEGMENT1 ITEM,
TO_CHAR(T.TRANSACTION_DATE, 'YYYY-MM-DD') T_date,
T.TRANSACTION_QUANTITY T_QTY
from inv.mtl_material_transactions t,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE T.INVENTORY_ITEM_ID(+) =
MSI.INVENTORY_ITEM_ID
and T.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
AND T.TRANSFER_SUBINVENTORY IN ('R010', 'R041')
AND T.TRANSACTION_TYPE_ID = 2
AND T.SUBINVENTORY_CODE = 'F010')
這段查出來的結果當做一張表
---------------------------------------
由于这位朋友发帖时没有提供相关几个表的表定义SQL文，尤其是索引和主键的定义信息,也没有提供复杂查询现在的执行计划，所以调优一时无法下手。
昨天当时又赶上加班没太多时间考虑，所以简单分析下后，给这位朋友两个提案。
-----------------------------------------
先提供两个提案，楼主可以先试一下
1、做一个临时表（※1），并给这个临时表上加上合适的索引。 执行update前，先将共通子查询那部分数据先灌入临时表，然后再执行update。
※1：我指的是创建Oracle中的TEMPORARY TABLE对象（语法:CREATE TEMPORARY TABLE tblname。。。。），语法可以到网上查一下。
(SELECT MSI.SEGMENT1 ITEM,
to_char(MT.TRANSACTION_DATE, 'yyyy-mm-dd') T_date,
MT.TRANSACTION_QUANTITY T_QTY
FROM INV.MTL_MATERIAL_TRANSACTIONS MT,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE MT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
and MT.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
and mt.transaction_type_id in (17, 44)
AND MT.SUBINVENTORY_CODE = 'F010'
UNION ALL
select MSI.SEGMENT1 ITEM,
TO_CHAR(T.TRANSACTION_DATE, 'YYYY-MM-DD') T_date,
T.TRANSACTION_QUANTITY T_QTY
from inv.mtl_material_transactions t,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE T.INVENTORY_ITEM_ID(+) =
MSI.INVENTORY_ITEM_ID
and T.TRANSACTION_DATE >= sysdate - 31
and msi.segment1 not like 'FA%'
AND T.TRANSFER_SUBINVENTORY IN ('R010', 'R041')
AND T.TRANSACTION_TYPE_ID = 2
AND T.SUBINVENTORY_CODE = 'F010')
2、分析上面SQL文的执行计划，然后在合适的地方 使用Oracle Hint，人为地改变 SQL文的执行计划。
关于获取SQL文执行计划的方法 和 Oracle Hint的语法，网上很多，楼主可以试着百度或谷歌一下。
以上两种方案相结合，相信会有一定帮助。
------------------------------------------
后来，又将这个问题中如何创建和使用Temporary table给这位朋友简单写了个例子。
--------------------------------
是像下面这样来做。
■1、创建一个辅助的临时表tt1，以得到 要建的临时表 的各个字段的类型和长度信息。
SQL> create table tt1 as
2 select * from dept;
Table created
SQL> desc tt1
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SQL>
■2、删除掉辅助表tt1。
SQL> drop table tt1 purge;
Table dropped
SQL>
■3、创建临时表。
SQL> CREATE GLOBAL TEMPORARY TABLE GBL_TMP_TBL
2 (
3 DEPTNO NUMBER(2),
4 DNAME VARCHAR2(14),
5 LOC VARCHAR2(13)
6 ) ON COMMIT PRESERVE ROWS;
Table created
这里有个地方需要注意一下，请根据应用需求选择使用。
既然是临时表，顾名思义，存放在该表中的数据是临时性的。ORACLE根据你创建临时表时指定的参数（On Commit Delete Rows / On Commit Preserve Rows），自动将数据TRUNCATE掉。
（1）On Commit Delete Rows ： 数据在 Transaction 期间有效，一旦COMMIT后，数据就被自动 TRUNCATE 掉了；
（2）On Commit Preserve Rows ：数据在 Session 期间有效，一旦关闭了Session 或 Log Off 后，数据就被 ORACLE 自动 Truncate 掉。
这里我随便选了一个。
■4、根据需要给临时表追加索引。
SQL> create index idx_GBL_TMP_TBL_1 on GBL_TMP_TBL(DEPTNO);
Index created
至此临时表就建好了。
下面,是向临时表中插入数据的方法，加在你的update语句前面即可。
■5、向临时表中插入数据。
SQL> insert into GBL_TMP_TBL
2 select * from dept;
4 rows inserted
SQL> select * from GBL_TMP_TBL;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
--------------------------------
最后，和另一位发帖的朋友简单分享了一下对这条SQL文调优的一些看法。
-------------------------
其实楼主的这个SQL文的调优困难的最大问题是这条SQL文太复杂了，
而且从各块SQL文来说，可调优的地方有限，最多加个Hint。
这条SQL文想要提高执行效率，恐怕只有改写，
1、要么向上面说的尝试换用merge语法来改写一下；
2、或者像我上面提到的那样，尝试保留现在update文逻辑的基础上，将子查询拿出来先灌入一个有索引的临时表，然后再执行update，当然相应地，这时update的写法也可以简化，并且可以尝试在适当的地方应用Hint来微调SQL文的执行计划。
感觉只有改写，才能根本提升这条SQL文的执行效率。
-------------------------
今天早上看了一下帖子，楼主朋友还没有回帖说一下问题解决的进度，上面的分析和问题解决提案如果能对楼主朋友的问题解决 能够有些帮助，就十分高兴了！希望那位朋友的问题能够早日顺利解决。
以上。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20335819/viewspace-710308/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/20335819/viewspace-710308/
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
