通过外连接提高SQL性能

数据库使用：通过外连接提高SQL性能

1、现象、问题描述
select CID from v_contents a where a.spid like '%' and a.type=98 and a.updateflg = '0' and a.curstatus='5'
and a.cid not in
(select distinct b.fieldvalue from v_contentdatavisual_zh b, v_contents c
where c.type=1 and c.updateflg!='2' and b.cid=c.cid and b.fieldid like 'download_onlineServiceID'
and b.fieldvalue is not null)
2、关键过程、根本原因分析
使用NOT IN 操作时执行计划如下：
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost |
| 0 | SELECT STATEMENT | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| |
| 1 | FILTER | | |
| |
| 2 | TABLE ACCESS FULL | CONTENTS | |
| |
| 3 | TABLE ACCESS BY INDEX ROWID| CONTENTDATAVISUAL_ZH | |
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | NESTED LOOPS | | |
| |
| 5 | TABLE ACCESS FULL | CONTENTS | |
| |
| 6 | INDEX RANGE SCAN | PK_CONTENTDATAVISUAL_ZH | |
| |
NOT IN的操作即使字段上有索引执行计划也会造成全表的扫描，因为NOT 的值并没有记录在索引中，所以上述SQL要执行7分多种。
修改SQL：
select a.CID from
v_contents a,
(select distinct b.fieldvalue from v_contentdatavisual_zh b, v_contents c
where c.type=1 and c.updateflg!='2' and b.cid=c.cid and b.fieldid like 'download_onlineServiceID'
and b.fieldvalue is not null) b
where a.cid=b.fieldvalue(+) and b.fieldvalue is null
and
a.spid like '%' and a.type=98 and a.updateflg = '0' and a.curstatus='5'
修改之后的执行计划如下：
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | By
tes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| |
| 1 | FILTER | | |
| |
| 2 | MERGE JOIN OUTER | | |
| |
| 3 | SORT JOIN | | |
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL | CONTENTS | |
| |
| 5 | SORT JOIN | | |
| |
| 6 | VIEW | | |
| |
| 7 | SORT UNIQUE | | |
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 8 | TABLE ACCESS BY INDEX ROWID| CONTENTDATAVISUAL_ZH | |
| |
| 9 | NESTED LOOPS | | |
| |
| 10 | TABLE ACCESS FULL | CONTENTS | |
| |
| 11 | INDEX RANGE SCAN | PK_CONTENTDATAVISUAL_ZH | |
采用外连接之后SQL速度由原来的7分钟下降为7秒钟。