Hi Gurus,
I have below query and exec plan. all tables E-rows are correct. as you can see table ACCT_STAT join to table ACCT and ACCT_DTL, but in plan, looks like MERGE JOIN CARTESIAN happened between table ACCT_STAT and ACCT_TYP table. Do I misunderstand anything? another question is: line 5 operation is BUFFER SORT, in the query, there is no any order by or other operation (like group by or window function ...) which trigger oracle sorting data, why oracle needs to sort data?
SELECT *
FROM ACCT B,
ACCT_DTL DT,
ACCT_TYP ATP,
ACCT_STAT S
WHERE B.SYS_CD = 'EST'
AND DT.SRC_CD = 'EST'
AND DT.RUN_ID = DATE '2018-02-06'
AND DT.ACCT_KEY = B.ACCT_KEY
AND DT.ACCT_TYPE_CODE = ATP.ACCT_TYP_CD
AND ATP.INC_EXC_IND != 'E'
AND S.SYS_CD = B.SYS_CD
AND DT.ACCT_STAT_CD = S.ACCT_STAT_CD;-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2236 (100)| | | | | | |
|* 1 | HASH JOIN | | 12421 | 1601K| 2236 (5)| 00:00:03 | | | 1844K| 895K| |
|* 2 | HASH JOIN | | 12421 | 849K| 1678 (3)| 00:00:03 | | | 899K| 899K| 1170K (0)|
| 3 | MERGE JOIN CARTESIAN | | 31 | 651 | 7 (0)| 00:00:01 | | | | | |
|* 4 | TABLE ACCESS FULL | ACCT_STAT | 1 | 11 | 3 (0)| 00:00:01 | | | | | |
| 5 | BUFFER SORT | | 61 | 610 | 4 (0)| 00:00:01 | | | 73728 | 73728 | |
|* 6 | TABLE ACCESS FULL | ACCT_TYP | 61 | 610 | 4 (0)| 00:00:01 | | | | | |
| 7 | PARTITION RANGE SINGLE| | 243K| 11M| 1660 (2)| 00:00:03 | 76 | 76 | | | |
| 8 | PARTITION LIST SINGLE| | 243K| 11M| 1660 (2)| 00:00:03 | KEY | KEY | | | |
|* 9 | TABLE ACCESS FULL | ACCT_DTL | 243K| 11M| 1660 (2)| 00:00:03 | 6427 | 6427 | | | |
| 10 | PARTITION LIST SINGLE | | 245K| 14M| 545 (11)| 00:00:01 | KEY | KEY | | | |
| 11 | TABLE ACCESS FULL | ACCT | 245K| 14M| 545 (11)| 00:00:01 | 123 | 123 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."SYS_CD"="B"."SYS_CD" AND "DT"."ACCT_KEY"="B"."ACCT_KEY")
2 - access("DT"."ACCT_TYPE_CODE"="ATP"."ACCT_TYP_CD" AND "DT"."ACCT_STAT_CD"="S"."ACCT_STAT_CD")
4 - filter("S"."SYS_CD"='EST')
6 - filter("ATP"."INC_EXC_IND"<>'E')
9 - filter("DT"."RUN_ID"=TO_DATE(' 2018-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
You are correct that *normally* a MERGE JOIN CARTESIAN is worth exploring to see if there is a join clause missing or similar. We have 'M' rows to join to 'N' rows, and the result might come out as 'M x N'
However in this case, notice also the full scan under it:
TABLE ACCESS FULL | ACCT_STAT | 1
We expect to see only a single row. This is why we opted for a MERGE JOIN CARTESIAN, because the general case of the cartesian join being 'M x N' rows is not a problem if either 'M' or 'N' is 1.
BUFFER SORT is poorly named. It's not really a sort. Let me backtrack a bit. When we *do* need to do a sort in Oracle (order by, etc), we buffer a batch of rows and sort them. The "BUFFER SORT" plan step is really saying:
"We're doing some buffering of data in the same way that we would do when we are about to do a sort"
but unless you see a subsequent "SORT" operation in the plan, we didn't actually do a sort, we just used the buffering mechanism.