구체화뷰(MATERIALIZED VIEW, MVIEW)를 이용한 GROUP함수 튜닝, 부서의 급여 최소값을 갖는 직원 추출

 구체화뷰(MATERIALIZED VIEW, MVIEW)를 이용한 GROUP함수 튜닝, 부서의 급여 최소값을 갖는 직원 추출

 

-- 실습테이블 생성
create table myemp1
(empno number not null primary key,
ename varchar2(100),
deptno number,
addr varchar2(100),
sal number
)

-- 실습을 위해 myemp1을 2000만건 만들자.
DECLARE
v_c NUMBER := 1;
BEGIN
WHILE (v_c <= 20000000) LOOP
insert into myemp1 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c, 1000000));
v_c := v_c + 1;
insert into myemp1 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c, 1000000));
v_c := v_c + 1;
insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c, 1000000));
v_c := v_c + 1;
insert into myemp1 values ( v_c, '하길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c, 1000000));
v_c := v_c + 1;
insert into myemp1 values ( v_c, '순길동'||v_c, mod(v_c, 5), '대전'||v_c, mod(v_c, 1000000));
v_c := v_c + 1;
END LOOP;
commit;
END;

 

-- 인덱스 생성
create index idx_myemp1_deptno_sal on myemp1(sal, deptno)

 

-- 통계정보 생성
analyze table myemp1 compute statistics

 

-- 부서의 최소급여 받는 사원 추출, 약 40초

-- 힌트구문이 게시판에서 주석처리되어 0로 대체했습니다,

-- 실행시 *로 바꾸세요
SELECT /0+ index(A idx_myemp1_sal_dept) 0/
       ENAME, SAL, ADDR
FROM myemp1 A
WHERE sal = (SELECT MIN(SAL)
                    FROM myemp1 B
                    WHERE B.DEPTNO = A.DEPTNO);

-- 구체화뷰(mview) 생성
CREATE MATERIALIZED VIEW m7
     BUILD IMMEDIATE
     REFRESH
     COMPLETE     
     ON DEMAND  
     ENABLE QUERY REWRITE
     AS
     select
           deptno, min(sal) avgsal from myemp1
    group by deptno
   

-- 바로 나옴
SELECT /0+ index(A idx_myemp1_sal_dept) 0/
       ENAME, SAL, ADDR
FROM myemp1 A, m7
WHERE A.SAL = m7.avgsal
 and  A.deptno = m7.deptno

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

[개강임박강좌, 오프라인교육장에 오시면 보다 자세히 배울 수 있습니다.]

오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)

[주간]
  [11/18]Spring3.X, MyBatis, Hibernate실무과정
  [11/18]iPhone 하이브리드 앱 개발 실무과정
  [11/20]SQL초보에서실전전문가까지
  [11/20]안드로이드개발자과정


[평일야간]
  [11/13]C#,ASP.NET마스터
  [11/19]iPhone하이브리드앱개발실무과정
  [11/19]안드로이드개발자과정
  [11/20]초보자를위한실전SQL
  [11/21]JAVA&WEB프레임워자실무과정
  [11/21]Spring3.X, MyBatis, Hibernate실무과정

[주말]
  [11/16]JAVA&WEB프레임워크실무과정
  [11/16]웹퍼블리싱 마스터
  [11/16]Spring3.X,MyBatis,Hibernate실무과정
  [11/16]SQL초보에서전문가까지
  [11/16]PL/SQL,오라클힌트,SQL튜닝,사례연구
  [11/23]C#,ASP.NET마스터