问oracle合并操作挂起(或循环)；查询调谐器声称这是笛卡尔产品操作
EN

Database Administration用户

提问于 2012-10-24 08:42:15

回答 1查看 2.8K关注 0票数 2

我有一个合并查询(开放给更好的建议)，它似乎会导致服务器挂起。(我不知道它在做什么，但它固定CPU，永远不会完成)当我通过查询调谐器运行它时，我被告知这是“在执行计划的第7行发现了昂贵的笛卡尔产品操作”，我应该“考虑从这个语句中删除断开连接的表或视图，或者添加一个引用它的联接条件”。问题是，没有断开连接的表。

下面是解释计划：

---------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |               |     1 |    18 |    75  (10)| 00:00:01 |
|   1 |  MERGE                            | CM_SSS_DETAIL |       |       |            |          |
|   2 |   VIEW                            |               |       |       |            |          |
|   3 |    SORT GROUP BY                  |               |     1 |  1352 |    75  (10)| 00:00:01 |
|   4 |     NESTED LOOPS                  |               |       |       |            |          |
|   5 |      NESTED LOOPS                 |               |     1 |  1352 |    74   (9)| 00:00:01 |
|   6 |       NESTED LOOPS                |               |     1 |  1328 |    73   (9)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN       |               |     1 |  1306 |    72   (9)| 00:00:01 |
|   8 |         TABLE ACCESS FULL         | CM_SSS_DETAIL |     1 |  1274 |    18   (0)| 00:00:01 |
|   9 |         BUFFER SORT               |               | 12815 |   400K|    54  (12)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL        | CI_CC         | 12815 |   400K|    54  (12)| 00:00:01 |
|  11 |        TABLE ACCESS BY INDEX ROWID| CI_CASE       |     4 |    88 |     1   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN          | XT220S1       |     6 |       |     1   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN            | XT222P0       |     1 |       |     1   (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID  | CI_CASE_CHAR  |     1 |    24 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

10 - filter("CC"."CC_TYPE_CD"='1NAA-LTR' OR "CC"."CC_TYPE_CD"='1NEA-LTR' OR
"CC"."CC_TYPE_CD"='1NIP-LTR' OR "CC"."CC_TYPE_CD"='CBS-LTR' OR
"CC"."CC_TYPE_CD"='CBS1-LTR' OR "CC"."CC_TYPE_CD"='CBS2-LTR' OR
"CC"."CC_TYPE_CD"='DNAR-LTR' OR "CC"."CC_TYPE_CD"='INR-LTR' OR "CC"."CC_TYPE_CD"='IPL-LTR'
OR "CC"."CC_TYPE_CD"='SOFB-LTR')
12 - access("CC"."PER_ID"="C"."PER_ID")
13 - access("CHR"."CASE_ID"="C"."CASE_ID" AND "CHR"."CHAR_TYPE_CD"='OBLGID')
14 - filter("D"."SA_ID"="CHR"."CHAR_VAL_FK1")

-------------------------------------------------------------------------------

以下是我的查询：

MERGE INTO cm_sss_detail d
USING (
    SELECT chr.char_val_fk1, MIN(cc.cc_dttm) first_date
    FROM ci_case c
    JOIN ci_cc cc
        ON (cc.per_id = c.per_id)
    JOIN ci_case_char chr
        ON (chr.case_id = c.case_id)
    WHERE chr.char_type_cd = 'OBLGID'
        AND cc.cc_type_cd IN (  '1NAA-LTR','CBS1-LTR','CBS2-LTR','CBS-LTR',
                                'DNAR-LTR','IPL-LTR','INR-LTR','SOFB-LTR',
                                '1NEA-LTR','1NIP-LTR')
    GROUP BY chr.char_val_fk1
) b
ON (d.sa_id = b.char_val_fk1)
WHEN MATCHED THEN UPDATE
    SET d.fst_bill_date = b.first_date;