昨天在和论坛上一位朋友讨论一条update SQL的性能优化时，聊到了Oracle的TEMPORARY TABLE，感觉在某些情况下还是有些用的，这里备忘一下。

帖子地址：http://www.itpub.net/thread-1508518-1-1.html

那位朋友的问题：

--------------------------------------

這段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博客

【性能调优】记一次性能调优问题的讨论（用到了TEMPORARY TABLE）

最新文章