답변: 유수익님의 말씀대로 2번이 정답입니다. 아래는 실행 순서입니다. 1) 먼저 2~3 번을 수행하고 2) 5~13번을 수행해서 3) id 4번에서 sort merge 조인을 하게 됩니다. 한가지 유의 사항은 id 5번에서 서브쿼리집합의 결과로 filter 가 수행됩니다. 즉 9~13 번의 서브쿼리 결과집합을 이용하여 6~8 의 결과집합을 filter 하는것입니다. 아래는 최종적으로 Transformation 된 SQL 입니다. SELECT d.department_id, d.department_name, v.sal FROM department d, (SELECT /*+ no_merge */ department_id, SUM (salary) AS sal FROM employee WHERE job_id = 'ST_CLERK' GROUP BY department_id ) v WHERE d.department_id = v.department_id AND v.sal = (SELECT MAX (v.sal) --> filter 수행 FROM (SELECT department_id, SUM (salary) AS sal FROM employee WHERE job_id = 'ST_CLERK' GROUP BY department_id ) v) ; ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | A-Rows | Buffers | ------------------------------------------------------------------------------------ | 1 | MERGE JOIN | | 1 | 1 | 8 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 6 | 4 | | 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 6 | 2 | |* 4 | SORT JOIN | | 6 | 1 | 4 | |* 5 | VIEW | | 1 | 1 | 4 | | 6 | HASH GROUP BY | | 1 | 1 | 2 | | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 20 | 2 | |* 8 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 20 | 1 | | 9 | SORT AGGREGATE | | 1 | 1 | 2 | | 10 | VIEW | | 1 | 1 | 2 | | 11 | SORT GROUP BY | | 1 | 1 | 2 | | 12 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 20 | 2 | |* 13 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 20 | 1 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID") filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID") 5 - filter("V"."SAL"=) 8 - access("JOB_ID"='ST_CLERK') 13 - access("JOB_ID"='ST_CLERK') 질문: -------------------------------------------------------------------------------------------------------------- - 11gr2에서 URSW가 잘 동작하는것을 확인했습니다. - 10g에서 어떻게 동작하나 확인하기 위해 플랜을 보니 URSW가 동작하지 않는것을 확인했습니다. - 궁굼한점 : 실행계획 순서(10GR2에서) 1) MERGE JOIN시 id기준으로 1 ~ 8까지 수행한후, emploryy 테이블을 읽는지 2) id기준 5번의 view로 해서 1~3까지 sort, 5~13까지 sort해서 4번으로 sort하는지 - 저는 2)번으로 동작한단고 생각하는데 view가 어디까지 인지 모르겠어요. starts항목을 보면 2)번으로 순서로 움직일거 같은데.. - 도와주시면 감사하겠습니다. <<쿼리>> WITH v AS (SELECT /*+ inline */ department_id, SUM (salary) AS sal FROM employee WHERE job_id = 'ST_CLERK' GROUP BY department_id ) SELECT d.department_id, d.department_name, v.sal FROM department d, v WHERE d.department_id = v.department_id AND v.sal = (SELECT MAX (v.sal) FROM v) ; <<11gr2>> --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 6 | | | | | 1 | MERGE JOIN | | 1 | 1 |00:00:00.01 | 6 | | | | | 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 6 |00:00:00.01 | 4 | | | | | 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 6 |00:00:00.01 | 2 | | | | |* 4 | SORT JOIN | | 6 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)| |* 5 | VIEW | | 1 | 1 |00:00:00.01 | 2 | | | | | 6 | WINDOW BUFFER | | 1 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)| | 7 | HASH GROUP BY | | 1 | 1 |00:00:00.01 | 2 | 801K| 801K| 397K (0)| | 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 20 |00:00:00.01 | 2 | | | | |* 9 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 20 |00:00:00.01 | 1 | | | | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID") filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID") 5 - filter("V"."SAL"="ITEM_0") 9 - access("JOB_ID"='ST_CLERK') <<10gr2>> ---------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------- | 1 | MERGE JOIN | | 1 | 1 |00:00:00.01 | 8 | | | | | 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 6 |00:00:00.01 | 4 | | | | | 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 6 |00:00:00.01 | 2 | | | | |* 4 | SORT JOIN | | 6 | 1 |00:00:00.01 | 4 | 2048 | 2048 | 2048 (0)| |* 5 | VIEW | | 1 | 1 |00:00:00.01 | 4 | | | | | 6 | HASH GROUP BY | | 1 | 1 |00:00:00.01 | 2 | 801K| 801K| 314K (0)| | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 20 |00:00:00.01 | 2 | | | | |* 8 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 20 |00:00:00.01 | 1 | | | | | 9 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 2 | | | | | 10 | VIEW | | 1 | 1 |00:00:00.01 | 2 | | | | | 11 | SORT GROUP BY | | 1 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)| | 12 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 20 |00:00:00.01 | 2 | | | | |* 13 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 20 |00:00:00.01 | 1 | | | | ---------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID") filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID") 5 - filter("V"."SAL"=) 8 - access("JOB_ID"='ST_CLERK') 13 - access("JOB_ID"='ST_CLERK')