ORACLE:EXISTS优化

原sql：

SELECT a.id,
       a.orderid,
       a.keyword,
       a.begindate,
       a.enddate,
       a.providerid
  FROM searchengine_keyword a
 WHERE EXISTS
          (SELECT *
             FROM (  SELECT b.keyword, COUNT (*)
                       FROM searchengine_keyword b
                      WHERE b.keyword IN (SELECT c.keyword
                                            FROM searchengine_keyword c
                                           WHERE     c.providerid = :1
                                                 AND c.state = '0'
                                                 AND c.TYPE = '1'
                                                 AND c.begindate IS NOT NULL
                                                 AND c.enddate IS NOT NULL
                                                 AND c.begindate < SYSDATE
                                                 AND c.keyword IS NOT NULL)
                   GROUP BY b.keyword
                     HAVING COUNT (*) < 2 AND COUNT (*) > 0) x
            WHERE x.keyword = a.keyword)
Plan hash value: 1334176065
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |       |       |  2488M(100)|          |
|*  1 |  FILTER                         |                               |       |       |            |          |
|   2 |   TABLE ACCESS FULL             | SEARCHENGINE_KEYWORD          |   561K|    23M|  4633   (1)| 00:00:56 |
|*  3 |   FILTER                        |                               |       |       |            |          |
|   4 |    SORT GROUP BY NOSORT         |                               |     1 |    46 |  4635   (1)| 00:00:56 |
|*  5 |     HASH JOIN SEMI              |                               |     1 |    46 |  4635   (1)| 00:00:56 |
|*  6 |      TABLE ACCESS FULL          | SEARCHENGINE_KEYWORD          |     5 |    45 |  4630   (1)| 00:00:56 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| SEARCHENGINE_KEYWORD          |     1 |    37 |     5   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | I_SEARCHENGINE_KEYWORD_PROVID |     2 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 100015402133
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   3 - filter((COUNT(*)<2 AND COUNT(*)>0))
   5 - access("B"."KEYWORD"="C"."KEYWORD")
   6 - filter("B"."KEYWORD"=:B1)
   7 - filter(("C"."KEYWORD"=:B1 AND "C"."TYPE"=1 AND "C"."STATE"=0 AND "C"."ENDDATE" IS NOT NULL AND
              "C"."BEGINDATE"<SYSDATE@! AND "C"."KEYWORD" IS NOT NULL))
   8 - access("C"."PROVIDERID"=:1)

10分钟不出结果，进程大量积压，cpu100%

改写如下：

explain plan for SELECT a.id,
  2         a.orderid,
  3         a.keyword,
  4         a.begindate,
  5         a.enddate,
  6         a.providerid
  7    FROM searchengine_keyword a
  8   WHERE a.keyword in
  9            (SELECT x.keyword
 10               FROM (  SELECT b.keyword, COUNT (*)
 11                         FROM searchengine_keyword b
 12                        WHERE b.keyword IN (SELECT c.keyword
 13                                              FROM searchengine_keyword c
 14                                             WHERE     c.providerid = 100015402133
 15                                                   AND c.state = '0'
 16                                                   AND c.TYPE = '1'
 17                                                   AND c.begindate IS NOT NULL
 18                                                   AND c.enddate IS NOT NULL
 19                                                   AND c.begindate < SYSDATE
 20                                                   AND c.keyword IS NOT NULL)
                   GROUP BY b.keyword
                     HAVING COUNT (*) < 2 AND COUNT (*) > 0) x
 23           );
Explained.

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4028499329
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |     1 |   101 |  9270   (1)| 00:01:52 |
|*  1 |  FILTER                          |                               |       |       |            |          |
|   2 |   HASH GROUP BY                  |                               |     1 |   101 |  9270   (1)| 00:01:52 |
|*  3 |    HASH JOIN                     |                               |     1 |   101 |  9269   (1)| 00:01:52 |
|   4 |     MERGE JOIN CARTESIAN         |                               |  9768 |   877K|  4638   (1)| 00:00:56 |
|   5 |      SORT UNIQUE                 |                               |     1 |    37 |     5   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| SEARCHENGINE_KEYWORD          |     1 |    37 |     5   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | I_SEARCHENGINE_KEYWORD_PROVID |     2 |       |     3   (0)| 00:00:01 |
|   8 |      BUFFER SORT                 |                               |   561K|    29M|  4633   (1)| 00:00:56 |
|   9 |       TABLE ACCESS FULL          | SEARCHENGINE_KEYWORD          |   561K|    29M|  4632   (1)| 00:00:56 |
|  10 |     TABLE ACCESS FULL            | SEARCHENGINE_KEYWORD          |   561K|  4935K|  4629   (1)| 00:00:56 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(COUNT(*)<2 AND COUNT(*)>0)
   3 - access("A"."KEYWORD"="B"."KEYWORD" AND "B"."KEYWORD"="C"."KEYWORD")
   6 - filter("C"."TYPE"=1 AND "C"."STATE"=0 AND "C"."ENDDATE" IS NOT NULL AND "C"."BEGINDATE"<SYSDATE@!
              AND "C"."KEYWORD" IS NOT NULL)
   7 - access("C"."PROVIDERID"=100015402133)
26 rows selected.

秒杀