[오라클교육,SQL교육,튜닝교육학원추천_탑크리에듀][mview, group by 튜닝]다음은 부서별 최대급여를 구하는 쿼리문이다. SQL문을 튜닝 하시오.

[mview, group by 튜닝]다음은 부서별 최대급여를 구하는 쿼리문이다. SQL문을 튜닝 하시오. 

myemp1 : 2000만건 
mydept1 : 5건(0,1,2,3,4) 

-- 약1분 소요 
SELECT A.ENAME, 
      A.SAL, 
      A.DNAME 
  FROM (SELECT A.ENAME, 
              B.DNAME, 
              A.SAL, 
              RANK() OVER (PARTITION BY A.DEPTNO 
                ORDER BY A.SAL DESC) AS RANKING 
          FROM MYEMP1 A, 
              MYDEPT1 B 
        WHERE A.DEPTNO = B.DEPTNO ) A 
 WHERE A.RANKING = 1 
  

|  0 | SELECT STATEMENT        |        |    20M|  2479M|      |  218K  (1) 
|*  1 |  VIEW                    |        |    20M|  2479M|      |  218K  (1) 
|*  2 |  WINDOW SORT PUSHED RANK|        |    20M|  648M|  844M|  218K  (1) 
|*  3 |    HASH JOIN            |        |    20M|  648M|      | 36575  (2) 
|  4 |    TABLE ACCESS FULL    | MYDEPT1 |    5 |    50 |      |    3  (0) 
|  5 |    TABLE ACCESS FULL    | MYEMP1  |    20M|  457M|      | 36473  (1) 
  

-------------------------------------------------------------------------------- 
-- 아래처럼 상관서브쿼리로... 확인해보자. 
    다음에 나오는 PAREWIAE 방식보다 느리다. 

SELECT ENAME, SAL 
FROM MYEMP1 A 
WHERE SAL = (SELECT MAX(SAL) FROM MYEMP1 B 
                    WHERE B.DEPTNO = A.DEPTNO); 


--------------------------------------------------------------------------------- 
create index idx_myemp1_sal on myemp1(deptno, sal)  
  
SQL> conn scott/tiger 
연결되었습니다. 
SQL> set timing on 
SQL>  set autotrace on explain 
SQL> select deptno, empno, sal 
      from myemp1 
      where (deptno, sal) in (select deptno, max(sal) 
                            from myemp1 
                            group by deptno); 

    DEPTNO      EMPNO        SAL 
---------- ---------- ---------- 
        0    3999995    3999995 
        1    3999996    3999996 
        ....................... 

25 개의 행이 선택되었습니다. 

경  과: 00:00:14.20 

Execution Plan 
---------------------------------------------------------- 
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%C 
PU)| Time    | 
-------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT            |                |    5 |  205 | 37588 
|  1 |  NESTED LOOPS                |                |      |      | 
|  2 |  NESTED LOOPS              |                |    5 |  205 | 37588 
|  3 |    VIEW                      | VW_NSO_1      |    5 |  130 | 37555 
|  4 |    HASH GROUP BY            |                |    5 |    45 | 37555 
|  5 |      TABLE ACCESS FULL      | MYEMP1        |    20M|  171M| 36473 
(|*  6 |    INDEX RANGE SCAN          | IDX_MYEMP1_SAL |    5 |      |    2 
|  7 |  TABLE ACCESS BY INDEX ROWID| MYEMP1        |    1 |    15 |    7 
-------------------------------------------------------------------------------- 


인덱스를 이용하고 서브쿼리로 바꾸니 14초 정도 나온다. 

이번에는 mview를 만들자 


SQL> CREATE MATERIALIZED VIEW m2 
  2      BUILD IMMEDIATE 
  3      REFRESH 
  4      COMPLETE 
  5      ON DEMAND 
  6      ENABLE QUERY REWRITE 
  7      AS 
  8      select 
  9            deptno, max(sal) maxsal from myemp1 
 10      group by deptno; 

구체화된 뷰가 생성되었습니다. 



작성 후 group by가 있는 쿼리문을 다시 실행하자. 
바로 나온다. 
( 
  인덱스를 생성해야 한다. 이미 했으면 SKIP 
  create index idx_myemp1_sal on myemp1(deptno, sal)  
) 

SQL>                select deptno, empno, sal 
  2        from myemp1 
  3        where (deptno, sal) in (select deptno, max(sal) 
  4                              from myemp1 
  5                              group by deptno); 

    DEPTNO      EMPNO        SAL 
---------- ---------- ---------- 
        0    3999995    3999995 
        0    7999995    3999995 
        ....................... 

25 개의 행이 선택되었습니다. 

경  과: 00:00:01.26 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 1180890275 

------------------------------------------------------------------------------- 
| Id  | Operation                      | Name          | Rows  | Bytes | Cost 
------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT                |                |    5 |  205 |    2 
|  1 |  NESTED LOOPS                  |                |      |      | 
|  2 |  NESTED LOOPS                  |                |    5 |  205 |    2 
|  3 |    SORT UNIQUE                  |                |    5 |  130 | 
|  4 |    MAT_VIEW REWRITE ACCESS FULL| M2            |    5 |  130 | 
|*  5 |    INDEX RANGE SCAN            | IDX_MYEMP1_SAL |    5 |      | 
|  6 |  TABLE ACCESS BY INDEX ROWID  | MYEMP1        |    1 |    15 |