OR导致笛卡尔积
近期监控数据库，发现以下语句跑得很慢，原来运行计划走了导致笛卡尔积，来看以下语句：
SQL> explain plan for
2 SELECT COUNT(*)
3 FROM "GD_FS"."TZZ_SJ_DEV_DISC_79073" "A2",
4 "GD_FS"."TZZ_SJ_DEVELOP_MONTH" "A1"
5 WHERE "A1"."SERV_ID" = "A2"."SERV_ID"
6 AND "A1"."STAT_DATE" = "A2"."STAT_DATE"
7 AND "A2"."ALLO_DISC_TYPE_3" LIKE '%租机%'
8 OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零预存%';
Explained.
SQL> @getplan
'general,outline,starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 1410945947
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 271M (2)|999:59:59 |
| 1 | SORT AGGREGATE | | 1 | 42 | | |
| 2 | CONCATENATION | | | | | |
| 3 | MERGE JOIN CARTESIAN| | 82G| 3239G| 271M (2)|999:59:59 |
|* 4 | TABLE ACCESS FULL | TZZ_SJ_DEV_DISC_79073 | 22606 | 640K| 743 (2)| 00:00:11 |
| 5 | BUFFER SORT | | 3663K| 45M| 271M (2)|999:59:59 |
| 6 | TABLE ACCESS FULL | TZZ_SJ_DEVELOP_MONTH | 3663K| 45M| 11994 (2)| 00:02:48 |
|* 7 | HASH JOIN | | 1 | 42 | 12762 (2)| 00:02:59 |
|* 8 | TABLE ACCESS FULL | TZZ_SJ_DEV_DISC_79073 | 21476 | 608K| 743 (2)| 00:00:11 |
| 9 | TABLE ACCESS FULL | TZZ_SJ_DEVELOP_MONTH | 3663K| 45M| 11996 (2)| 00:02:48 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%零预存%')
7 - access("A1"."SERV_ID"="A2"."SERV_ID" AND "A1"."STAT_DATE"="A2"."STAT_DATE")
8 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%租机%' AND LNNVL("A2"."ALLO_DISC_TYPE_3"
LIKE '%零预存%'))
--表大小的情况：
OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb) -------------------- ------------------------------ -------------------- ---------- GD_FS TZZ_SJ_DEV_DISC_79073 TABLE 43 GD_FS TZZ_SJ_DEVELOP_MONTH TABLE 577.375
两个表并不大，一个43M，一个577M，这里肯定是开发者不小心，写错了语句，导致跑不出来。
AND "A2"."ALLO_DISC_TYPE_3" LIKE '%租机%' OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零预存%';改成
AND （"A2"."ALLO_DISC_TYPE_3" LIKE '%租机%' OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零预存%'）;
改动语句后，问题解决。
改写后：
SQL> explain plan for
2 SELECT COUNT(*)
3 FROM "GD_FS"."TZZ_SJ_DEV_DISC_79073" "A2",
4 "GD_FS"."TZZ_SJ_DEVELOP_MONTH" "A1"
5 WHERE "A1"."SERV_ID" = "A2"."SERV_ID"
6 AND "A1"."STAT_DATE" = "A2"."STAT_DATE"
7 AND ("A2"."ALLO_DISC_TYPE_3" LIKE '%租机%' OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零预存%');
Explained.
SQL> @getplan
'general,outline,starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3104770780
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 12765 (2)| 00:02:59 |
| 1 | SORT AGGREGATE | | 1 | 42 | | |
|* 2 | HASH JOIN | | 44081 | 1808K| 12765 (2)| 00:02:59 |
|* 3 | TABLE ACCESS FULL| TZZ_SJ_DEV_DISC_79073 | 44081 | 1248K| 745 (3)| 00:00:11 |
| 4 | TABLE ACCESS FULL| TZZ_SJ_DEVELOP_MONTH | 3663K| 45M| 11996 (2)| 00:02:48 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A1"."SERV_ID"="A2"."SERV_ID" AND "A1"."STAT_DATE"="A2"."STAT_DATE")
3 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%租机%' OR "A2"."ALLO_DISC_TYPE_3" LIKE
'%零预存%')
