SQL>explain plan for select emp.deptno,dept.deptno from emp,dept;
Please look at the execution plan for the above statement,there is a sort of table emp before it is merged to table dept
SELECT STATEMENT, GOAL = CHOOSE 12 70 280
MERGE JOIN CARTESIAN 12 70 280
TABLE ACCESS FULL SCOTT DEPT 2 5 10
BUFFER SORT 10 14 28
TABLE ACCESS FULL SCOTT EMP 2 14 28
As many Docs said that "If the row source is already appropriately sorted then no sorting is
required in SMJ", i have tried to recreate table emp by ascending order of deptno,such as:
SQL>create table emp_tmp as select * from emp order by deptno;
SQL>drop table emp;
SQL>alter table emp_tmp rename to 'emp';
Then i analyze table em and execute the above query again, but resulted the same execution plan in which sort operation still exists.
Any efficient way to eliminate the sort operation in SMJ ?