데이터베이스 질문과 답변
select /*+gather_plan_statistics*/d.deptno, d.dname, avg_sal, min_sal, max_sal
from dept d
,(select deptno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
from emp group by deptno) e
where e.deptno(+) = d.deptno
and d.loc = 'CHICAGO';
이방식과
SELECT /*+gather_plan_statistics */D.deptno, D.DNAME, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
FROM EMP E
, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
AND D.LOC = 'CHICAGO'
GROUP BY D.deptno, D.DNAME;
이 방식중 어느게 더 좋은 방법인가요?
단순하게 보면 데이터에 분포도에 따라 다르지 않을 까 싶습니다.
지역이 시카고인 회원(emp)이 대부분이면 첫번째 쿼리가 빠를 것 같고요.
그게 아니라면 두번째가 빠르지 않을까 싶네요.
인라인뷰 쓰는것보다 안쓰는것에 한표 드립니다.
저는 첫 번째 쿼리가 더 성능이 좋을 가능성이 있을 것 같습니다.
쿼리만으로 보면 모든 deptno별로 avg, min, max 값을 구하는 것과 조인되는 것 같아 보이지만
실제로는 d부터 탐색할 것이고 탐색한 d의 deptno를 e의 검색 조건으로 넘겨주게 되므로(pushed predicate)
필요한 deptno만 group by 하게 됩니다. 결과적으로는 조인횟수가 줄어들게 됩니다.
모두 조인하고 group by 하는 것보다는 group by 해놓은 작은 집합과 조인하는 것이 효과적이겠지요.
흠...empno 와 deptno 가 키라고 하고 plan 을 봤는데 cost 는 동일하지만 cardinality가 약간 적음 2번째가 약간 나은 거 같습니다.
| Description | Object owner | Object name | Cost | Cardinality | Bytes |
| SELECT STATEMENT, GOAL = ALL_ROWS | 4 | 3 | 81 | ||
| HASH GROUP BY | 4 | 3 | 81 | ||
| NESTED LOOPS OUTER | 3 | 4 | 108 | ||
| TABLE ACCESS FULL | SYSTEM | DEPT | 2 | 1 | 20 |
| TABLE ACCESS BY INDEX ROWID | SYSTEM | EMP | 1 | 4 | 28 |
| INDEX RANGE SCAN | SYSTEM | EMP_N1 | 0 | 4 | |
| Description | Object owner | Object name | Cost | Cardinality | Bytes |
| SELECT STATEMENT, GOAL = ALL_ROWS | 4 | 1 | 27 | ||
| HASH GROUP BY | 4 | 1 | 27 | ||
| NESTED LOOPS OUTER | 3 | 4 | 108 | ||
| TABLE ACCESS FULL | SYSTEM | DEPT | 2 | 1 | 20 |
| TABLE ACCESS BY INDEX ROWID | SYSTEM | EMP | 1 | 4 | 28 |
| INDEX RANGE SCAN | SYSTEM | EMP_N1 | 0 | 4 |
야신님 플랜을 보고(플랜이 동일해보입니다. group by 하여 조인된 흔적이 없어요. 아마도 쿼리 변형된 듯합니다.)
저도 실측 PLAN을 떠보았는데요.
GROUP BY 하지 않았을 때..
9 ----------------------------------------------------------------------------------------------------------------------------------------------
10 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
11 ----------------------------------------------------------------------------------------------------------------------------------------------
12 | 0 | SELECT STATEMENT | | 1 | | 468 |00:00:00.23 | 3236 | | | |
13 | 1 | HASH GROUP BY | | 1 | 134 | 468 |00:00:00.23 | 3236 | 1127K| 1127K| 1399K (0)|
14 | 2 | NESTED LOOPS | | 1 | 147 | 196K|00:00:00.16 | 3236 | | | |
15 | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| AAAATABLE | 1 | 134 | 544 |00:00:00.01 | 549 | | | |
16 |* 4 | INDEX RANGE SCAN | IX_AAAATABLE_03 | 1 | 134 | 544 |00:00:00.01 | 5 | | | |
17 |* 5 | INDEX RANGE SCAN | PK_BBBBTABLE | 544 | 1 | 196K|00:00:00.11 | 2687 | | | |
18 ----------------------------------------------------------------------------------------------------------------------------------------------
GROUP BY 했을 때
11 ----------------------------------------------------------------------------------------------------------------------------------------------
12 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
13 ----------------------------------------------------------------------------------------------------------------------------------------------
14 | 0 | SELECT STATEMENT | | 1 | | 468 |00:00:00.23 | 3236 | | | |
15 | 1 | HASH GROUP BY | | 1 | 1 | 468 |00:00:00.23 | 3236 | 1137K| 1137K| 1399K (0)|
16 | 2 | NESTED LOOPS | | 1 | 1 | 1331 |00:00:00.10 | 3236 | | | |
17 | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| AAAATABLE | 1 | 134 | 544 |00:00:00.01 | 549 | | | |
18 |* 4 | INDEX RANGE SCAN | IX_AAAATABLE_03 | 1 | 134 | 544 |00:00:00.01 | 5 | | | |
19 | 5 | VIEW PUSHED PREDICATE | | 544 | 1 | 1331 |00:00:00.22 | 2687 | | | |
20 | 6 | SORT GROUP BY | | 544 | 1 | 1331 |00:00:00.22 | 2687 | 2048 | 2048 | 2048 (0)|
21 |* 7 | INDEX RANGE SCAN | PK_BBBBTABLE | 544 | 1 | 196K|00:00:00.11 | 2687 | | | |
22 ----------------------------------------------------------------------------------------------------------------------------------------------
보시다시피
GROUP BY 하지 않았을 때는 조인횟수가 196K만큼입니다만
GROUP BY 하였을 때는 조인횟수가 1331로 줄었습니다.
다만 GROUP BY 를 하기 위해 메모리를 조금더 사용하긴 했습니다.
GROUP BY하였을 때 집합이 얼마나 줄어들 수 있느냐에 따라 GROUP BY 하고 조인할지 GROUP BY 하지 않고 조인할지 결정하시면 될 것 같습니다.
1. 조인 일량 줄이기 : 그룹바이 후 (1:1)조인인지? / (1:다)조인 후 그룹바이인지? 차이
2. 집계 일량 줄이기 : 전체 집계인지? / 조인 대상만 집계인지?
두가지 관점에서 바라 볼 수 있는데요.
바라보는 관점에 따라 유불리가 달라집니다.
1. 각 데이터 량
2. dept 에서의 검색 대상의 비율
3. emp 에서의 부서별 분포도
등을 종합적으로 분석해야 합니다.
실행계획도 확인해 봐야 하고요.
쿼리만 보고 유불리를 판단하기 어렵습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.