oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation

Question

I have a Merge query (open to better suggestions) that seems to cause the server to hang. (I'm not sure what it's doing, but it pegs the CPU and never completes) When I run it through the query tuner, I'm told it's caused by "an expensive cartesian product operation was found at line ID 7 of the execution plan", and that I should "consider removing the disconnected table or view from this statement or add a join condition which refers to it." The problem is, there is no disconnected table.

Here's the explain plan:

---------------------------------------------------------------------------------------------------
| 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")

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

Here's my query:

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;

The cm_sss_detail table has an index on the sa_id column (it's also the PK).

Answer 1

According to the query plan, the optimizer expects that the MERGE statement is going to modify 1 row. It appears to expect the Cartesian join to return a single row as well. Based on the fact that the query isn't returning in a few milliseconds, that would generally imply that the statistics on one or more of the objects (tables or indexes) are substantially incorrect. When was the last time statistics were gathered on these tables and their dependent indexes? Has the actual data in the tables changed substantially since that time?