oracle mview를 이용한 group by 튜닝 [오라클/자바/닷넷/C#/ASP.NET/아이폰/안드로이드/초보/실무/교육/학원]

oracle mview를 이용한 group by 튜닝

create table myemp1
(empno number not null primary key,
 ename varchar2(100),
 deptno number,
 addr   varchar2(100),
 sal    number
 )

-- 실습을 위해 myemp1을 1000만건 만들자.
DECLARE
          v_c NUMBER := 1;
BEGIN

          WHILE (v_c <= 10000000) LOOP
                insert into myemp1 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c, 2000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c, 2000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c, 2000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c, 2000000));
                v_c := v_c + 1;
          END LOOP;
          commit;
END;

deptno컬럼은 0,1,2,3,4중 하나의 값이다.

다음과 같은  group by SQL문을 보자.

      select 
           deptno, avg(sal) avgsal from myemp1
    group by deptno

100만건의 데이터가 있는 경우 약 22초 걸린다.

이를 위해 비트맵 인덱스를 만들어 처리해 보았지만 더 느림!@!

mview를 만들어 처리하자.

conn / as sysdba
grant query rewrite to scott
grant create materialized view to scott
conn scott/tiger

drop MATERIALIZED VIEW m2
CREATE MATERIALIZED VIEW m2
     BUILD IMMEDIATE 
     REFRESH
     COMPLETE      
     ON DEMAND   
     ENABLE QUERY REWRITE
     AS
     select 
           deptno, avg(sal) avgsal from myemp1
    group by deptno

select * from m2;

바로 데이터 나온다,

----------------------------------------------------------

다음과 같은 SQL문장을 생각해 보자.

select count(empno)
  from myemp1 m1
 where sal > (
                select avg(sal)
                  from myemp1 m2
                 where m2.deptno = m1.deptno    
              )

바깥루프(1000만건)의 deptno값이 매 레코드가 select될땨 마다
내부 서브쿼리에 대입되어 그 결과가 다시 바깥 외부루프로 돌아가므로
성능이 저하된다.

약 22초 소요

1. WITH문으로 빼서 처리

with m2 as (
    select 
           deptno, avg(sal) avgsal from myemp1
     where deptno = 1
    group by deptno
)
select count(empno) from  myemp1 m1, m2
 where m1.deptno = m2.deptno
   and sal > m2.avgsal

별로 빨라지지 않음

 

2. 만들어진 mview를 이용하여 처리하자.

  select count(empno) from  myemp1 m1, m2
 where m1.deptno = m2.deptno
   and sal > m2.avgsal

약 10초 소요.!!

테스트 해 보라.

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

• JAVA
• ORACLE
• iPhone/Android
• .NET
• 표준웹/HTML5
• 채용/취업무료교육
• 초보자(재학생)코스

[기업100%환급]Spring ,MyBatis,Hibernate실무과정	총	5일	40시간		01-13
[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지	총	5일	40시간		01-13
자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지	총	24일	72시간		01-24
Spring3.X, MyBatis, Hibernate실무과정	총	12일	36시간		02-03
Spring3.X, MyBatis, Hibernate실무과정	총	5일	35시간		01-11
JAVA&WEB프레임워크실무과정	총	14일	98시간		01-11