近期監(jiān)控?cái)?shù)據(jù)庫(kù)，發(fā)現(xiàn)以下語句跑得很慢，原來運(yùn)行計(jì)劃走了導(dǎo)致笛卡爾積，來看以下語句：

      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 '%租機(jī)%'
  8      OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%';

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 '%零預(yù)存%')
   7 - access("A1"."SERV_ID"="A2"."SERV_ID" AND "A1"."STAT_DATE"="A2"."STAT_DATE")
   8 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' AND LNNVL("A2"."ALLO_DISC_TYPE_3"
              LIKE '%零預(yù)存%'))

    

--表大小的情況：

      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
    

兩個(gè)表并不大，一個(gè)43M，一個(gè)577M，這里肯定是開發(fā)者不小心，寫錯(cuò)了語句，導(dǎo)致跑不出來。
AND "A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' ?OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%';改成
AND （"A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' ?OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%'）;
改動(dòng)語句后，問題解決。

改寫后：

      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 '%租機(jī)%' OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%');

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 '%租機(jī)%' OR "A2"."ALLO_DISC_TYPE_3" LIKE
              '%零預(yù)存%')