OR導致笛卡爾積，or笛卡爾

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                        43GD_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              '%零預存%')

笛卡爾積符號問題： AxB={<x,y>|x∈A∧y∈B} 上式中的“∧”是什？如果是倒過來類似“V”？

∧= and 並
V = or 或
 
今天寫了一個hql作查詢，結果查詢出很多條重複的記錄，肯定做了笛卡爾積

from t1,t2,t3..tn這就是笛卡爾積
t1表總記錄乘以t2表總記錄乘以t3表總記