首页 > 代码库 > OR导致笛卡尔积
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
'%零预存%')
OR导致笛卡尔积
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。