MERGE JOIN CARTESIAN - cheeyoung/sqlplus-public GitHub Wiki
Example of MERGE JOIN CARTESIAN
...
59 PARSING IN CURSOR #140737265263456 len=86 dep=0 uid=0 oct=1 lid=0 tim=2308837199074 hv=3596291639 ad='2392907fd8' sqlid='65w8csmb5q3jr'
60 CREATE TABLE DDL_TEST_201
61 END OF STMT
...
+869 SELECT COUNT(*)
+870 FROM
+871 V$SESSION SS, V$SQLAREA SA WHERE SS.SQL_ID = SA.SQL_ID AND SS.AUDSID =
+872 USERENV('SESSIONID')
+873
+874
+875 call count cpu elapsed disk query current rows
+876 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
+877 Parse 2 0.00 0.00 0 0 0 0
+878 Execute 2 0.00 0.00 0 0 0 0
+879 Fetch 2 415.35 415.08 0 0 0 2
+880 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
+881 total 6 415.35 415.08 0 0 0 2
+882
+883 Misses in library cache during parse: 0
+884 Optimizer mode: FIRST_ROWS
+885 Parsing user id: 104 (recursive depth: 2)
+886 Number of plan statistics captured: 1
887
888 Rows (1st) Rows (avg) Rows (max) Row Source Operation
889 ---------- ---------- ---------- ---------------------------------------------------
890 1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=213285969 us)
891 1 1 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=213285947 us cost=0 size=66 card=1)
892 1 1 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=213285931 us cost=0 size=62 card=1)
893 156030622 156030622 156030622 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=44439384 us cost=0 size=1677 card=39)
894 133817 133817 133817 FIXED TABLE FULL X$KGLCURSOR_CHILD_SQLID (cr=0 pr=0 pw=0 time=2602184 us cost=0 size=34 card=1)
895 156030622 156030622 156030622 BUFFER SORT (cr=0 pr=0 pw=0 time=16698830 us cost=0 size=6975 card=775)
896 1166 1166 1166 FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=1053 us cost=0 size=6975 card=775)
897 1 1 1 FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=164558549 us cost=0 size=19 card=1)
898 1 1 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=11 us cost=0 size=4 card=1)
...
ALTER SESSION SET "_optimizer_cartesian_enabled" = FALSE
/
- 2032840.1
Other Example
9.3.5.3 Cartesian Join Controls
The ORDERED hint instructs the optimizer to join tables in the order in which they appear in the FROM clause. By forcing a join between two row sources that have no direct connection, the optimizer must perform a Cartesian join.
Example 9-19 ORDERED Hint
In the following example, the ORDERED hint instructs the optimizer to join employees and locations, but no join condition connects these two row sources:
SELECT /*+ORDERED*/ e.last_name, d.department_name, l.country_id, l.state_province
FROM employees e, locations l, departments d
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id