SQL> SELECT COUNT(*) FROM TC2_SEG S WHERE S.PART_ID = 11 AND S.CLASS_ID IN (310,510)
2 AND NOT EXISTS (SELECT NODEID FROM TEMP_PORTNODE WHERE NODEID=S.HEAD_NODE OR NODEID=S.TAIL_NODE);
执行计划
----------------------------------------------------------
Plan hash value: 3774119106
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 102K (1)| 00:20:30 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| TC2_SEG | 54783 | 1230K| 581 (1)| 00:00:07 |
|* 4 | TABLE ACCESS FULL| TEMP_PORTNODE | 73598 | 1850K| 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT 0 FROM "TEMP_PORTNODE" "TEMP_PORTNODE"
WHERE "NODEID"=:B1 OR "NODEID"=:B2))
3 - filter(("S"."CLASS_ID"=310 OR "S"."CLASS_ID"=510) AND
"S"."PART_ID"=11)
4 - filter("NODEID"=:B1 OR "NODEID"=:B2)
这个SQL跑了16分钟,从执行计划看到走FILTER,效率极低,,原因是子查询WHERE中包含了OR。
尝试将SQL改为:
SQL> SELECT COUNT(*) FROM TC2_SEG S WHERE S.PART_ID = 11 AND S.CLASS_ID IN (310,510)
2 AND NOT EXISTS (SELECT NODEID FROM TEMP_PORTNODE WHERE NODEID=S.HEAD_NODE )
3 AND NOT EXISTS (SELECT NODEID FROM TEMP_PORTNODE WHERE NODEID=S.TAIL_NODE );
执行计划
----------------------------------------------------------
Plan hash value: 3386542280
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 587 (1)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 49 | | |
|* 2 | HASH JOIN RIGHT ANTI | | 54781 | 2621K| 587 (1)| 00:00:08 |
| 3 | TABLE ACCESS FULL | TEMP_PORTNODE | 73598 | 1850K | 2 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT ANTI| | 54782 | 1925K| 584 (1)| 00:00:08 |
| 5 | TABLE ACCESS FULL | TEMP_PORTNODE | 73598 | 1850K | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | TC2_SEG | 54783 | 1230K| 581 (1)| 00:00:07 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NODEID"="S"."HEAD_NODE")
4 - access("NODEID"="S"."TAIL_NODE")
6 - filter(("S"."CLASS_ID"=310 OR "S"."CLASS_ID"=510) AND "S"."PART_ID"=11)
1秒跑完,从执行计划看,子查询UNNEST了,选择了HASH JOIN RIGHT ANTI,原SQL由于子查询WHERE中含又OR导致无法UNNEST。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22990797/viewspace-1294341/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22990797/viewspace-1294341/