通过外连接提高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秒钟。