SQL> get a
1 select /*+ ordered */ e.ename,d.dname
2 from
3 emp e,
4 (select sum(e.sal) sal,e.deptno,d.dname
5 from emp e,dept d
6 where e.deptno = d.deptno
7 group by e.deptno,d.dname) d
8* where e.deptno = d.deptno
Rows Row Source Operation
------- ---------------------------------------------------
14 HASH GROUP BY (cr=76 pr=0 pw=0 time=24752 us)
70 NESTED LOOPS (cr=76 pr=0 pw=0 time=18272 us)
70 NESTED LOOPS (cr=4 pr=0 pw=0 time=5963 us)
14 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=1576 us)
14 INDEX FULL SCAN EMP_DEPTNO_IX (cr=1 pr=0 pw=0 time=1146 us)(object id 54486)
70 INDEX RANGE SCAN EMP_DEPTNO_IX (cr=2 pr=0 pw=0 time=2036 us)(object id 54486)
70 TABLE ACCESS BY INDEX ROWID DEPT (cr=72 pr=0 pw=0 time=7996 us)
70 INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=2913 us)(object id 51147)
오랜만에 잡담이다.
문득 그룹핑되어있는 조인의 순서를 깨버리면 어떤 플랜이 나오나 확인하고 싶었다.
이를테면 A,B 가 GROUP BY 되어있는 구문 ((A,B),C) 를 (A,(B,C)) 로 바꾸는 것이다.
위의 플랜은 ORDERED 를 사용해 위와 같은 시도를 하였다.
힌트가 없으면 inline view 의 쿼리를 먼저 실행 후 메인쿼리와 조인을 한다.
그러나 emp가 driving 되면 cost 와 힌트에 따라 다른 operation 이 나올 수 있다.
이 경우는 그룹핑을 깬 결과가 나왔다.
(먼저 group by 되어있는 테이블에 대해 결과집합을 만들지 않았다는 의미)
처음 메인쿼리의 emp index 를 읽고 table 에 엑세스 후 다시 emp index 에 엑세스해
앞서 엑세스한 값을 상수값으로 조인을 시도한다.
DEPTNO COUNT(*)
---------- ----------
1
30 6
20 5
10 3
위의 표는 부서별 직원수이다.
emp 에 엑세스 해 다시 자신의 index 에 엑세스 하는데 조인조건으로 사용하는 것은
emp.deptno 이다.
이 조건은 autotrace 나 explain plan 에서 predicate information 으로 확인할 수 있다.
결과적으로 같은부서에 대해 조인은 모두 성공하므로 null 을 제외한
(6*6) + (5*5) + (3*3) = 36 + 25 + 9 = 70 이다.
이는 위 트레이스와 동일한 결과이다.
다음으로 dept 와 join 하게 된다.
dept와 emp는 부/자 관계로 조인은 모두 성공한다.
n:1 관계이므로 결과는 n이 된다.
조인의 결과 ROW 수는 70이 된다.
마지막으로 HASH GROUP BY를 하게 된다.
그룹핑의 키는 DEPTNO 이다.
중복된 DEPTNO 가 모두 사라지므로 ROWS 수는 6 + 5 + 3 가 된다.
sum 값은 /*+ ORDERED */ 와 관계없이 정상적인 값을 보이는데 중복된 값이
오퍼레이션 중에 제거되고 남은 값으로 sum 한 것으로 보인다.
제거되지 않았다면 힌트없이 실행한 결과와 다른 값이 나와야 하는데 동일하기
때문이다.
오늘 이야기를 한 것은 inline view 가 merging 되면서 비효율적으로 풀리는 경우가
있어 생각해 보았다.
