oracle存储过程insert语句效率,一条insert语句导致的性能问题分析（二)

今天对之前描述的问题一条insert语句导致的性能问题分析(一) 进行了进一步的补充。

有一条insert语句的主要性能瓶颈在于insert子句中的查询语句，查询中的主要资源消耗在于对两个表进行了多次关联

语句主要的结构如下：

insert into xxxxx   (select * from TEST_vip_new minus select * from TEST_vip_new_bak

) a left join TEST_vip_new_bak b

on a.cn=b.cn

对于这个test_vip_new和test_vip_new_bak我产生了疑问，觉得这个临时表test_vip_new_bak有些多余。带着这种思路分析，看起来逻辑很简单啊，于是就联系了开发的同学，一起讨论一番。

但是讨论完之后，还是让我有些不知所措。

首先，语句为什么要多次关联，自己做了一个小的测试，感觉这种关联方式还是有些多余。

SQL> create table a (id number);

Table created.

SQL> create table b (id number);

Table created.

SQL> insert into a values(1);

1 row created.

SQL> insert into a values(2);

1 row created.

SQL> insert into b values(1);

1 row created.

SQL> select * from a minus select * from b;

ID

----------

2

SQL> select *from a left join b on a.id=b.id;

ID         ID

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

1          1

2

如果按照这样的思路，仿照原来的结构输出就是下面这样的结果。

SQL> select *from (select * from a minus select * from b) a left join b on a.id=b.id;

ID         ID

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

2

但是和开发讨论了一番，发现我的想法有些简单了，具体的场景中数据过滤的逻辑比上面这种略微复杂一些。

通过一个两个测试表来模拟。

create table a (id1 number,id2 number);create table game_new (id1 number,id2 number);

create table game_new_bak (id1 number,id2 number);

如果原来的数据内容为(1,1)，在运行存储过程之后，会修改为(1,2)

SQL> insert into game_new values(1,2);

1 row created.

然后存储过程在运行过程中，会插入一些新的数据，假设为(2,2)

SQL> insert into game_new values(2,2);

1 row created.

而临时表game_new_bak中的数据是存储过程运行之前的数据状态，即(1,1)

SQL> insert into game_new_bak values(1,1);

1 row created.

按照这种情况，两个表做了minus操作之后会输出两行，即修改之后的数据和新增的数据。

而这个需求需要实现的是，根据id1进行匹配，把修改前的id2一并输出。这样就知道修改前是什么样的数据了，如果是新增的，那这列的值就保持为空。

SQL>   select a.id1,a.id2,b.id2 from (select * from game_new minus select *from game_new_bak)  a left join game_new_bak b on a.id1=b.id1

ID1        ID2        ID2

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

1          2          1

2          2

如果是这样的情况，就完全可以使用一次表关联就可以改进。可以用下面的形式。

select a.id1,a.id2,b.id2 from game_new a ,game_new_bak b where a.id1=b.id1(+)

所以就建议语句从原来的形式

SELECT A.CN,A.GRADE,A.RANK,A.SCORE,DECODE(SIGN

(A.RANK-(NVL(B.RANK,-1))),1,2,-1,3,0,1),

SYSDATE,(NVL(B.RANK,-1)),B.SIGN,B.FLAG,B.TAG,B.SCORE FROM ( SELECT *

FROM GAME_VIP_NEW MINUS SELECT * FROM GAME_VIP_NEW_BAK ) A LEFT JOIN

GAME_VIP_NEW_BAK B ON A.CN=B.CN

修改为：

SELECT /*+parallel(4)*/ A.CN,A.GRADE,A.RANK,A.SCORE,DECODE(SIGN

(A.RANK-(NVL(B.RANK,-1))),1,2,-1,3,0,1),

SYSDATE,(NVL(B.RANK,-1)),B.SIGN,B.FLAG,B.TAG,B.SCORE FROM GAME_VIP_NEW a,cydba.GAME_VIP_NEW_BAK B

where  A.CN=B.CN(+)

考虑到执行的情况和资源情况，加了一个并行，可以在一定程度上缓解这个问题。

在本地的环境中进行了测试，发现几分钟就可以轻松搞定，做了基本的确认，就和开发进行了反馈，对线上的存储过程内容进行了修改。

这个问题的解决也就终于告一段落。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-2060204/，如需转载，请注明出处，否则将追究法律责任。