Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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?

share|improve this answer
    
hmm. Well, this is a fairly new table, and the gets populated during the first run. Would it make sense to do the initial insert (fill) then update the statistics, before continuing with the merge(s)? We're only dealing with about 8.5k in the details and and about 5.6k that would be eligible for matching. –  end-user Oct 24 '12 at 17:20
1  
@end-user - If there are statistics on the table saying that it has 0 rows when it really has ~8500 rows, yes, you'd need to gather statistics. If there were no statistics on the table, Oracle would use dynamic sampling to generate some (less accurate but generally reasonable) statistics. If dynamic sampling was used, there would be a note to that effect in the query plan output (just below what you posted here). –  Justin Cave Oct 24 '12 at 17:26
    
Yep, that looked to be what it was. I stuck an alter index ... unusable before truncating and inserting, then a alter index ... rebuild after, followed by a dbms_stats.gather_table_stats(). Maybe a little ham-fisted, but it's running smoothly now. Thanks! –  end-user Oct 24 '12 at 18:42
2  
@end-user: no need for the alter index stuff. Just use gather_table_stats() after the load. –  a_horse_with_no_name Oct 24 '12 at 18:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.