原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. 秒杀 原文链接：https://www.f2er.com/oracle/213836.html