[오라클교육,SQL교육,튜닝교육학원추천_탑크리에듀]group by, where vs having성능비교, 복합인덱스, 구체화뷰를 이용한 SQL튜닝(materialized view, composite index, group by SQL 튜닝)

group by, where vs having성능비교, 복합인덱스, 구체화뷰를 이용한 SQL튜닝(materialized view, composite index, group by SQL 튜닝)

myemp1은 2000만건 deptno는 0,1,2,3,4의 5가지 종류의 값을 가지고 있다.

1. B*Tree 인덱스 이용

-- B*Tree 인덱스
create index idx_myemp1_deptno on myemp1(deptno)

-- 전체 group by를 먼저하고 having으로 조건비교
--13.8초
select deptno, avg(sal) from myemp1
group by deptno
having deptno = 1

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 37555 (4)| 00:07:31 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 9 | 37555 (4)| 00:07:31 |
| 3 | TABLE ACCESS FULL| MYEMP1 | 20M| 171M| 36473 (1)| 00:07:18 |
------------------------------------------------------------------------------


-- 1번부서 데이터만 스캔하여 그룹핑
-- 28초
select /*+ index(myemp1 idx_myemp1_deptno) */
deptno, avg(sal) from myemp1
where deptno = 1
group by deptno

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 103
| 1 | SORT GROUP BY NOSORT | | 1 | 9 | 103
| 2 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 4000K| 34M| 103
| 3 | INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO | 4000K| | 7732



2. deptno, sal 복합인덱스를 이용하자.
drop index idx_myemp1_deptno
create index idx_myemp1_deptno_sal on myemp1(deptno,sal)

-- 1초, deptno컬럼은 varchar2임
select /*+ index(myemp1 idx_myemp1_deptno_sal) */
deptno, avg(sal) from myemp1
where deptno = '1'
group by deptno

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
U)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 10930
1)| 00:02:12 |
| 1 | SORT GROUP BY NOSORT| | 1 | 9 | 10930
1)| 00:02:12 |
|* 2 | INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO_SAL | 4000K| 34M| 10930
1)| 00:02:12 |
-------------------------------------------------------------------------------


3. MVIEW를 이용

CREATE MATERIALIZED VIEW m7
BUILD IMMEDIATE 
REFRESH
COMPLETE 
ON DEMAND -- 요청이 있을때 뷰갱신 
ENABLE QUERY REWRITE
AS
select 
deptno, avg(sal) avgsal from myemp1
group by deptno

-- 0초
select deptno, avg(sal) from myemp1
group by deptno
having deptno = 1 


--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00
|* 1 | MAT_VIEW REWRITE ACCESS FULL| M7 | 1 | 26 | 3 (0)| 00:00