How to prevent the sort operation from happening in sort merge join ?

[signalsys]

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 ?

 

[taupirho]

Try the following to avoid the sort.

1) create an index on deptno and also ...
2) create a not null constraint on deptno

 

[signalsys]

Hi,
I tried creating an index and add a not null constraint on deptno but lead to the same result.

 

[Dagon]

You could try putting hints on your queries to use either index joins or hash joins instead of the merge join e.g.

select /*+ INDEX(table_name index_name) */ ...

 

[carp]

Since you are doing a cartesian join of the two tables, indexes will not make any difference. The optimizer uses the join conditions (WHERE clause) to determine if indexes can be used to any advantage; no WHERE clause, no indexes.

 

[Beilstwh]

What possible reason would you do a cartsian on emp and dept? What are you trying to do?




Bill
Oracle DBA/Developer
New York State, USA