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
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
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 

[출처] 오라클자바커뮤니티 - http://www.oraclejavacommunity.co.kr/bbs/board.php?bo_table=LecOrccleTun&wr_id=71

오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급
www.oraclejavacommunity.com

평일주간(9:30~18:10) 개강
(7/21)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(7/21)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(7/21)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(7/21)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정
(7/28)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍
(7/28)[기업100%환급]안드로이드개발자과정
(7/28)[기업100%환급]SQL기초에서 Schema Object까지

평일야간(19:00~21:50) 개강
(7/22)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(7/22)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(7/24)SQL기초에서실무까지
(7/29)안드로이드개발자과정
(7/29)Spring3.X, MyBatis, Hibernate실무과정
(8/04)웹퍼블리싱 마스터
(8/05)MyBatis3.X, Hibernate4.X ORM실무과정

주말(10:00~17:50) 개강
(7/26)Spring3.X, MyBatis, Hibernate실무과정
(7/26)Spring,MyBatis,Hibernate속성할인과정
(7/26)개발자를위한PLSQL,SQL튜닝,힌트
(8/02)C#,ASP.NET마스터
(8/02)웹퍼블리싱 마스터
(8/02)SQL초보에서 Schema Object까지
(8/02)MyBatis3.X, Hibernate4.X ORM실무과정
(8/09)안드로이드개발자과정
(8/09)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(8/23)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지)

주말저녁(18:30~22:20) 개강
(8/02)JAVA,Network&WEB&Framework
(8/09)SQL기초에서실무까지