A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out.
It can be useful in some circumstances - Star joins uses cartesian products.Notice that there is no join between the 2 tables:
SQL> explain plan for
select emp.deptno,dept,deptno
from emp,dept
Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost=5
**MERGE JOIN CARTESIAN
****TABLE ACCESS FULL DEPT
****SORT JOIN
******TABLE ACCESS FULL EMP
The CARTESIAN keyword indicate that we are doing a cartesian product.
七、运算符
1.sort --排序，很消耗资源
There are a number of different operations that promote sorts:
order by clauses
group by
sort merge join
2.filter --过滤，如not in、min函数等容易产生
Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.
3.view --视图，大都由内联视图产生
When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.
eg:
SQL> explain plan for
select ename,tot
from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp
where emp.empno = tmp.empno;
Query Plan
------------------------
SELECT STATEMENT [CHOOSE]
**HASH JOIN
**TABLE ACCESS FULL EMP [ANALYZED]
**VIEW
****SORT GROUP BY
******INDEX FULL SCAN BE_IX
4.partition view --分区视图
Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.
作者“baobaojinjin”