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