使用minus优化子查询一例

               某日一开发同事反映一个SQL语句执行非常慢,语句如下.

select  count(t1.id)
  from t1 where t1.status!=0 and not exists(select 1 from
t2 where t2.id1=t1.id or t2.id2=t1.id)

表结构
create table t1
(
 id  INTEGER primary key,
 name VARCHAR2(100),
  status   INTEGER)

create table t2
(
 id1  INTEGER,
 id2   INTEGER)

t1表中数据为7296行，t2表中数据为65158行。执行时间将到了53秒

SQL> select  count(t1.id)
  2    from t1 where t1.status!=0 and not exists(select 1 from
  3  t2 where t2.id1=t1.id or t2.id2=t1.id);

COUNT(T1.ID)
------------
        7082

Elapsed: 00:00:53.74

确认了统计状态也是最新的，以及表的存储状态都没有问题
由于在那个时间段服务器资源比较空闲，就以并行的方式去执行，

SQL> select  /*+ parallel(16) */count(t1.id)
  2    from t1 where t1.status!=0 and not exists(select 1 from
  3  t2 where t2.id1=t1.id or t2.id2=t1.id);

COUNT(T1.ID)
------------
        7082

Elapsed: 00:01:03.55
发现执行速度没有明显的加快,而且时间还变长了,看来并行的方式也不合适.

 

查看执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 59119136

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     9 | 18579   (2)| 00:03:43 |
|   1 |  SORT AGGREGATE     |      |     1 |     9 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1   |  7296 | 65664 |     7   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T2   |     3 |    30 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."ID2"=:B1
              OR "T2"."ID1"=:B2))
   3 - filter("T1"."STATUS"<>0)
   4 - filter("T2"."ID2"=:B1 OR "T2"."ID1"=:B2)

在这个查询中，最大的成本就在最后过滤.看来优化器也没有改写这个sql语句.没有把子查询改写成连接一类的操作.如果能去掉过滤的操作，应该能减少很多执行时间

通过该sql语句的特点，使用集合操作minus来改写not exists逻辑，语句如下

select count(*)
  from t1
 where id in (select id
                from t1
               where t1.status != 0
              minus
              select t1.id id
                from t2, t1
               where t2.id1 = t1.id
                  or t2.id2 = t1.id)

  COUNT(*)
----------
      7082

Elapsed: 00:00:00.05

改写后的执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 1961550418

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     1 |    19 |       |   114   (6)| 00:00:02 |
|   1 |  SORT AGGREGATE          |          |     1 |    19 |       |            |          |
|*  2 |   HASH JOIN              |          |  7296 |   135K|       |   114   (6)| 00:00:02 |
|   3 |    TABLE ACCESS FULL     | T1       |  7296 | 43776 |       |     7   (0)| 00:00:01 |
|   4 |    VIEW                  | VW_NSO_1 |  7296 | 94848 |       |   473  (79)| 00:00:06 |
|   5 |     MINUS                |          |       |       |       |            |          |
|   6 |      SORT UNIQUE         |          |  7296 | 65664 |       |            |          |
|*  7 |       TABLE ACCESS FULL  | T1       |  7296 | 65664 |       |     7   (0)| 00:00:01 |
|   8 |      SORT UNIQUE         |          | 68416 |  1069K|  1624K|   465   (2)| 00:00:06 |
|   9 |       CONCATENATION      |          |       |       |       |            |          |
|* 10 |        HASH JOIN         |          | 65158 |  1018K|       |    49   (5)| 00:00:01 |
|  11 |         TABLE ACCESS FULL| T1       |  7296 | 43776 |       |     7   (0)| 00:00:01 |
|  12 |         TABLE ACCESS FULL| T2       | 65158 |   636K|       |    41   (3)| 00:00:01 |
|* 13 |        HASH JOIN         |          |  3258 | 52128 |       |    49   (5)| 00:00:01 |
|  14 |         TABLE ACCESS FULL| T1       |  7296 | 43776 |       |     7   (0)| 00:00:01 |
|  15 |         TABLE ACCESS FULL| T2       | 65158 |   636K|       |    41   (3)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"="ID")
   7 - filter("T1"."STATUS"<>0)
  10 - access("T2"."ID2"="T1"."ID")
  13 - access("T2"."ID1"="T1"."ID")
       filter(LNNVL("T2"."ID2"="T1"."ID"))

 

可以看到已经去了filter操作，只有连接操作了,执行间还不到一秒钟就完成了.