[SQLP 요약] 제 5, 6장 (이력, 소계, Top N, 고급 SQL 튜닝) - (3/4)

이전 내용 :

2024.02.07 - [자격증/SQLP] - [SQLP 요약] 제 5장 인덱스와 조인 (2절 조인의 원리와 활용) - (2/4)

[SQLP 요약] 제 5장 인덱스와 조인 (2절 조인의 원리와 활용) - (2/5)

 

선분이력

선분이력은 시작과 종료를 같이 저장하는 것을 말함.

따라서 조건에 맞는 이력을 가져올 시 아래와 같은 형식이 됨.

SELECT *
  FROM 할인율 A
 WHERE A.고객번호 = '123'
   AND A.시작일자 <= '20210131' -- 시작일은 큰 거보다 작고
   AND A.종료일자 >= '20210101' -- 종료일은 작은 거보다 크도록 짜면 된다
;

-- 요런 식이다.
SELECT *
  FROM 할인율 A, 월도 B
 WHERE A.고객번호 = '123'
   AND B.고객번호 = A.고객번호
   AND A.시작일자 <= B.종료일자
   AND A.종료일자 >= B.시작일자
   ;

 

소계(Rollup, Grouping sets, Cube)

이건 SQLD 과정이니 적기만 하고 넘어가자.

🟢 Rollup (전체 별, 맨뒤 빼고 나머지별 ... 1개 남을 때까지)

 

🟢 Grouping sets (각각 소계)

 

🟢 Cube ( 각각 모든 상황 다 그룹핑 )

 

✅ 작년 15점 짜리로 일별, 월별 구간을 각각 group by 한 후 union all 처리한 쿼리를 grouping sets로 변환하는 과정이 포함된 문제가 나왔었다고 한다

-- 이걸
SELECT '월별', TO_CHAR(HIRE_DATE, 'YYYYMM'), SUM(SALARY)
  FROM HR.EMPLOYEES
 GROUP BY TO_CHAR(HIRE_DATE, 'YYYYMM')
 UNION ALL
SELECT '일별', TO_CHAR(HIRE_DATE, 'YYYYMMDD'), SUM(SALARY)
  FROM HR.EMPLOYEES 
 GROUP BY TO_CHAR(HIRE_DATE, 'YYYYMMDD')
 ORDER BY 1, 2;
 
 -- 이렇게 바꾸는 형식이었는 듯
SELECT DECODE(TO_CHAR(HIRE_DATE, 'YYYYMM'), NULL, '일별', '월별') AS 구분
     , NVL(TO_CHAR(HIRE_DATE, 'YYYYMM'), TO_CHAR(HIRE_DATE, 'YYYYMMDD')) AS 날짜
     , SUM(SALARY)
  FROM HR.EMPLOYEES
 GROUP BY GROUPING SETS(TO_CHAR(HIRE_DATE, 'YYYYMM'), TO_CHAR(HIRE_DATE, 'YYYYMMDD'))  
 ORDER BY 1, 2

 

상호배타적 관계 조인

배타 관계의 설계

분리되어있는 방식이라면 아래처럼 쿼리를 조인해도 중복 조인 시도하지 않는다.

 

다만, 통합된 컬럼에 구분코드 등으로 나뉘어 있는 형식이라면 위와 똑같은 모습으로 아우터 조인하면 배타 관계의 컬럼 조인에도 필요 없는 조인 시도를 하게 된다.

1. 인덱스가 결제일자 + 상품권구분 일 경우

따라서 위 사진에서 아래 쿼리처럼 특정 요건에 따라 조인을 분기시켜 주어 필요없는 조인 시도를 제거할 수 있다.

구분이 '1' 일때는 '2' 쪽은 DECODE에 의해 NULL이 들어가므로 조인에서 걸러지는 방식이다.

 

2. 인덱스가 상품권구분 + 결제일자 일 경우

인덱스가 상품권구분 + 결제일자 이면서 배타적이라면 UNION ALL로 나눠버려도 전혀 비효율이 없다.

 

게시판 쿼리(TOP-N, STOPKEY)

✅ TOP-N 쿼리

TOP-N의 핵심은 아래 세개이다. 

1. ORDER BY

2. ROWNUM <= N

3. ROWNUM ALIAS NO 의 BETWEEN

SELECT JOB_ID, NO
  FROM (SELECT JOB_ID, ROWNUM AS NO
          FROM (SELECT JOB_ID
                  FROM HR.EMPLOYEES
                 ORDER BY JOB_ID) -- 1.
         WHERE ROWNUM <= 10) -- 2.
 WHERE NO BETWEEN 6 AND 10; -- 3.
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |      5 |00:00:00.01 |       1 |
|*  1 |  VIEW              |            |      1 |      5 |00:00:00.01 |       1 |
|*  2 |   COUNT STOPKEY    |            |      1 |     10 |00:00:00.01 |       1 |
|   3 |    VIEW            |            |      1 |     10 |00:00:00.01 |       1 |
|   4 |     INDEX FULL SCAN| EMP_JOB_IX |      1 |     10 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------

 

ROWNUM <= N을 주면서 옵티마이저에게 배열 버퍼의 개수를 고정시킬 수 있도록 함.

최대 N개의 배열에 담으면서 SORT함으로 한 건당 최대 N번의 비교만 함.

( BUFFER 개수를 고정 = 메모리 적게 사용 + 비교연산 회수 줄임 )

만약 INDEX가 있었다면 SORT도 제거되면서 바로 N 건만 출력해버릴 수 있는 효과!(=STOPKEY)

 

🟢 WINDOW SORT 시에도 RANK()나 ROW_NUMBER()로 TOP-N 알고리즘 작동함(STOPKEY 말고!) = MAX() 보다 부하 경감됨

 

만약 INDEX가 있었다면

-> 인덱스는 다음 순서로 구성해야 STOPKEY가 잘 작동한다.

1. = 조건 컬럼

2. ORDER BY 컬럼

3. 추가 조건

 

+ 추가. 징검다리 테이블을 이용한 조인.
-- 위 쿼리와 아래 쿼리의 일량은 동일하다. 인덱스의 ROWID를 조인하는게 랜덤 액세스 이니까.
-- 이걸 활용해서 징검다리 테이블의 ROWID만 가지고 조인을 하는 경우도 있다 하는데 
-- 그냥 그러려니 하고 넘어가자

SELECT /*+ NO_QUERY_TRANSFORMATION ORDERED USE_NL(T) */ *
   FROM (SELECT ROWID
           FROM HR.EMPLOYEES
          WHERE JOB_ID = 'FI_ACCOUNT') S, HR.EMPLOYEES T
  WHERE S.ROWID = T.ROWID;
  
 -------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |      5 |00:00:00.01 |       2 |
|   1 |  NESTED LOOPS               |            |      1 |      5 |00:00:00.01 |       2 |
|   2 |   VIEW                      |            |      1 |      5 |00:00:00.01 |       1 |
|*  3 |    INDEX RANGE SCAN         | EMP_JOB_IX |      1 |      5 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS BY USER ROWID| EMPLOYEES  |      5 |      5 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------
  
SELECT *
  FROM HR.EMPLOYEES
 WHERE JOB_ID = 'FI_ACCOUNT';
 
 ---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |      1 |      5 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES  |      1 |      5 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | EMP_JOB_IX |      1 |      5 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------

 

점이력 조회

 

특정 상품의 최종 값. 게시판 STOPKEY이다.

상품별이라면 ROW_NUMBER로 TOP-N 알고리즘을 동작시켜 뽑아낼 수 있다.

 

✅ 오라클 12C 부터 사용 가능한 아래 쿼리!! 꼭 외우자

-- 12C 부터 가능한 쿼리!!
SELECT E.사원번호, E.사원명, E.부서코드, H.직급 AS 최종직급
  FROM 사원 E, 발령이력 H
 WHERE E.사원번호 = H.사원번호 
   AND E.부서코드 = '10'
   AND (H.변경일자, H.이력순번) = (SELECT 변경일자, 이력순번
                                FROM (SELECT 변경일자, 이력순번
                                        FROM 발령이력
                                       WHERE 사원번호 = E.사원번호 
                                       ORDER BY 변경일자 DESC, 이력순번 DESC)
                                WHERE ROWNUM <= 1)
;

-- 이러면 오답임!
SELECT E.사원번호, E.사원명, E.부서코드, H.직급 AS 최종직급
  FROM 사원 E, (SELECT /*+ NO_MERGE PUSH_PRED */ 사원번호, 직급
                 FROM (SELECT 사원번호, 직급, ROW_NUMBER() OVER(PARTITION BY 사원번호 ORDER BY 변경일자 DSEC, 이력순번 DESC) RN 
                         FROM 발령이력)
                WHERE RN = 1) H
 WHERE E.사원번호 = H.사원번호 
   AND E.부서코드 = '10'
;

 

 

🟢 인라인 뷰 힌트 주는 법

-- 인라인뷰의 경우 S.H 처럼 사용
SELECT /*+ INDEX(S.H INDEX_NAME) */ *
  FROM (SELECT *
          FROM TABLE H) S
          
-- 서브쿼리의 경우 QB_NAME으로 쿼리블록 이름 지정 후 A@쿼리블록이름 으로 사용

SELECT /*+ INDEX(h@qblock index_name) *
  FROM TABLE T
 WHERE EXISTS (SELECT /*+ qb_name(qblock) */ 1
                 FROM TABLE H
                WHERE H.COL = T.COL) S

 

제 6장 고급 SQL 튜닝

 

✅ 이번에 pivot이 추가됬음. 무조건 나온다는 얘기겠지? 이거 꼭 기억하자

이걸 PIVOT 한다면 아래처럼 하면 된다

 

 

🟢 Full Outer Join은 좌측 outer 조인 후 우측 outer 조인 한다. 즉 2번 outer join 이다.

따라서 Full Outer Join 할바에 Union all로 처리하는게 낫다!!

 

with 문

 

🟢 Materialize 방식

내부적으로 임시테이블을 생성하는 방식. MV 와는 다른게 Materialize 방식은 with 절을 선언한 sql 종료 시 해제된다.

🟢 Inline 방식

인라인뷰랑 동일함. 그냥 쿼리 여러번 쓰기 귀찮을 때 방식이다.

 

 

소트와 성능

소트를 발생시키는 오퍼레이션 종류

JO GA UW.. 조가유..로 외우라 하신다. 너무 잘 외워져

1. Sort Join

2. Sort Order by

3. Sort Group by

4. Sort Aggregate

5. Sort Unique (=distinct)

Unnesting 된 서브쿼리가 M쪽 집합이거나, 인덱스가 없거나, 세미조인으로 수행되지 않는다면

메인쿼리와 조인되기 전에 중복 레코드 제거를 위해서 Sort Unique 연산이 수행된다.

6. Window Sort

 

Sort Group by

SELECT DEPTNO, MAX(SAL), SUM(SAL)
  FROM EMP
 GROUP BY DEPTNO
 ORDER BY DEPTNO -- 얘가 없다면 Hash Group by가 될 것임
 ;
 
 
Hash Group by : 결과값이 정렬되지 않는다

SELECT DEPTNO, MAX(SAL), SUM(SAL)
  FROM EMP
 GROUP BY DEPTNO
 ;

 

 

✅ 소트튜닝 요약

1. 소트가 발생하지 않도록 SQL 작성

2. 인덱스를 이용한 소트 연산 대체

3. Sort Area를 적게 사용하도록 SQL 작성

4. Sort Area 크기 조정

 

가. Union을 Union All로 대체

나. Distinct를 Exists로 대체

다. 불필요한 Count 연산 제거

 

✅ Distinct를 Exists로 대체

위와 같은 1:M 에서 M 기준으로 가져와야 할 때 distinct가 필요하다면 Exists로 대체하면 Sort 연산을 제거할 수 있다.

 

다음 내용 :

2024.02.26 - [자격증/SQLP] - [SQLP 요약] 제 6장 (고급SQL튜닝, 파티션, 배치 튜닝, 병렬 처리) - (4/4)

[SQLP 요약] 제 6장 (고급SQL튜닝, 파티션, 배치 튜닝, 병렬 처리) - (4/4)