Ashburton Grove

1. SUBQUERY의 종류
- 하나의 질의문 내부에 하나 이상의 다른 질의문이 포함되어 그 결과를 이용할 때, 내부에 포함된 쿼리문을 의미한다.

(Scalar Subquery, Inline View, Nested Subquery, Correlated Subquery 등이 Subquery의 속한다.)
- GROUP BY를 제외하고는 모든 절에는 SUBQUERY가 위치할 수 있다.

- Scalar Subquery는 SELECT, FROM, WHERE, ORDER BY에 모두 존재 가능.
- Inline View는 FROM절에만 존재하며,
- Nested Subquery, Correlated Subquery는 WHERE절에만 존재


2. NESTED SUBQUERY
- 서브쿼리가 WHERE 절에서 사용된 경우, Nested Subquery라 함.
- Nested Subquery가 Main Query보다 먼저 실행될 때 속도를 낼 수 있는 유형임
- 단, 서브쿼리 쪽에서 조회하는 Main Query 값에 인덱스가 없으면 서브쿼리는 먼저 실행되지 않음

ex)
SELECT empno, ename
FROM emp
WHERE deptno = (SELECT deptno 
                FROM dept
WHERE dname = 'SALES');
-> deptno에 인덱스가 있다면 Nested Subquery가 먼저 실행,
     다만, 인덱스가 있더라도 인덱스를 사용할 수 없는 환경(!= 연산자 등)이라면 Nested Subquery가 먼저 실행되지 않음.

위 쿼리의 실행계획은 아래 그림과 같다. Subquery 3번이 먼저 실행된다.

3. CORRELATED SUBQUERY

- 서브쿼리가 WHERE절에서 사용되고 메인 쿼리에서 데이터를 하나씩 읽을 때마다 서브쿼리가 실행되어 데이터를 리턴하는 서브쿼리를 의미함.
- 메인쿼리에서 데이터를 읽고 있는 Row 수만큼 서브쿼리가 실행됨.
(메인쿼리보다 나중에 실행된다.)

ex)
SELECT ename, empno
FROM emp
WHERE EXISTS  (SELECT 'X' FROM dept
 WHERE dept.deptno = emp.deptno
 AND dept.dname = 'SALES');
dept.deptno = emp.deptno 때문에 emp에서 데이터를 먼저 가져온다.

위 쿼리의 실행계획은 아래와 같다.

4. SCALAR SUBQUERY

- 단 하나의 데이터와 단 하나의 칼럼에 대한 정보를 리턴함
- Scalar Subquery 사용 위치
(Select List 항목, 함수의 인자,WHERE절의 조건, Order by 절, case조건절, case 결과절)
- S결과 값이 0개인 경우, NULL / 결과 값이 2개인 경우, ORA-1427를 반
- 메인쿼리보다 나중에 실행된다.

ex)
SELECT  e.ename,
d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

- 위 쿼리는 아래와 같이 변경 가능.

SELECT  e.ename
(SELECT d.dname
 FROM dept d
 WHERE d.deptno = e.deptno) AS dname
FROM emp e;

- 아래와 같이 PL/SQL로 함수(User Define Function)를 만들 수도 있음.

CREATE OR REPLACE FUCTION f_dnm(a_dno IN dept.deptno%TYPE)
RETURN VARCHAR2
RESULT_CACHE
RELIES ON(dept)
AS
 h_dnm dept.dname%type := NULL;
BEGIN
 SELECT dname INTO h_dnm
 FROM dept
 WHERE deptno = a_dno;

RETURN h_dnm;
END;
/

SELECT  ename,
f_dnm(deptno) AS dname
FROM emp;


5. ROLLUP() & CUBE()

ROLLUP() : 데이터의 총계를 나타낼 때 사용하는 함수

CUBE() : 데이터의 소계를 나타낼 때 사용하는 함수

예시A) 
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY d.dname, e.job -> R
ORDER BY 1, 2;

예시B)
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY ROLLUP(d.dname, e.job)
ORDER BY 1, 2;

예시C)
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY CUBE(d.dname, e.job)
ORDER BY 1, 2;

6. GROUPING SETS()

- 여러 개의 GROUP BY 쿼리를 동시에 실행한 것과 같은 결과를 나타냄
- GROUPING SETS()로 ROLLUP(), CUBE(), ROLLUP()&CUBE() 구현이 가능함.

ex) 
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY GROUPING SETS((d.dname, e.job), (d.dname), ())
ORDER BY 1, 2;
-> GROUP BY ROLLUP(d.dname, e.job)과 동일한 결과

SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY GROUPING SETS((d.dname, e.job), (d.dname), (e.job), ())
ORDER BY 1, 2;
-> GROUP BY CUBE(d.dname, e.job) 과 동일한 결과 


7. ANALYTIC FUNCTIONS

- 행과 행 간의 관계를 정의하거나 비교, 연산하기 위해 사용함

SELECT Analytic_Function (arguments) OVER
([Partition By 칼럼] [Order by 절] [Windowing 절])
FROM 테이블명
WHERE 
;

Argument : 함수에 따라 0 ~ 3개의 인자가 지정됨.
Partition By 절 : 전체 집합을 기준에 의해 소그룹으로 나눔.
Order By 절 : 어떤 항목에 대한 정렬 기준을 기술함.
Windowing 절 : 함수에 의해서 제어하고자 하는 데이터 범위를 정의함.

*종류
그룹 내 데이터 순위 : ROW_NUMBER, RANK, DENSE_RANK
그룹 내 비율 : RATIO_TO_REPORT


* EMP 테이블에서 부서(DEPTNO) 별로 급여가 높은 사람 순으로 순위를 구하기 위한 SQL
ex)
SELECT  deptno, ename, sal,
ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) As rno,
RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) as rk,
DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) as drk
FROM emp;

* EMP 테이블에서 부서 별로 각 사원을 기준으로 해서 급여에 대한 누계를 구하기 위한 SQL

ex) 
SELECT  deptno, ename, sal,
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ASC ROWS UNBOUNDED PRECEDING) AS csum
FROM emp;

* EMP테이블에서 업무별로 총급여를 기준으로 각 사원의 급여를 백분율(소수점)을 구하기 위한 SQL

ex)
SELECT  job, ename, sal
RATIO_TO_REPORT(sal) OVER (PARTITION BY job) AS rr
FROM emp;

1. Cartesian Product의 개념

곱집합 연산을 생각하면 된다.
특정 테이블의 데이터를 필요한 만큼 복사(copy) 하기 위한 방법

* Cartesian Product가 발생하는 경우
- WHERE 절이 없는 조인 수행
- WHERE 절은 있으나 테이블 조인을 위한 조건 없이 조인을 수행

* '데이터 복제'라는 개념을 활용하기 위해 사용하지만, 잘못 사용하게 되면 오히려 데이터를 부풀리는 원인이 되기 때문에 퍼포먼스를 오히려 나쁘게 할 수 있다. 

2. 사용하는 방법

 
위 그림에서 ?에는 Table이 올수도, View가 올수도 있다.

Cartesian Product는 아래 3가지 방법 중에 하나를 선택하여 사용한다.
① COPY_T, IMSI_T, DUMMY_T 와 같은 temporary Table을 활용함
(Cartesian product 만을 위한 전용 테이블을 생성해서 사용)
② DUAL과 같은 dummy 테이블을 사용
③ 타 SQL에서 사용하고 있는 Table 활용 및 ROWNUM을 사용한다. (주로 Master 테이블을 사용)

3. Cartesian Product 적용 예제
- UNION으로 연결된 각각의 SQL이 읽고 있는 데이터가 전부 같을 경우, 데이터 복제와 같은 개념을 활용하기 위해 사용
- 데이터 구조 변환을 통해 사용자가 요청한 구조대로 데이터를 조회할 때 사용

ex)

SELECT '직군별' AS class, job, COUNT(*) AS cnt
FROM emp
GROUP BY job
UNION ALL
SELECT '부서별' AS class, TO_CHAR(deptno), COUNT(*)
FROM emp
GROUP BY deptno
UNION ALL
SELECT '총인원' AS CLASS, NULL, COUNT(*)
FROM emp

모두 emp 테이블을 참고하며, WHERE절이 없는 쿼리들을 UNION ALL 시킨 것.
각 SQL문이 읽어들이는 데이터가 같을 때, 데이터 복제를 통해 튜닝을 진행해야함.
(WHERE절이 있다면, WHERE절도 모두 같아야 한다).

Cartesian Product를 사용하면 아래 쿼리로 수정이 가능함.

SELECT DECODE(rn, 1, '직군별', 2, '부서별', '총인원') AS class

               DECODE(rn, 1, job, 2, deptno),

               SUM(cnt)

FROM (SELECT job, deptno, COUNT(*) AS cnt

             FROM emp

             GROUP BY job, deptno), 

            (SELECT ROWNUM AS rn

             FROM (SELECT LEVEL FROM dual CONNECT BY ROWNUM <=3 ))

GROUP BY rn, DECODE(rn, 1, '직군별', 2, '부서별', '총인원'), DECODE(rn, 1, job, 2, deptno);

-> 수정된 쿼리는 emp 테이블을 한 번만 읽은 효과가 있다.

그림처럼 변환하기 위해서는 아래와 같은 쿼리를 사용


SELECT  a.ename, b.qtr
DECODE(b.qtr, 1, a.q1, 2, a.q2, 3, a.q3, a.q4) AS sl
FROM (SELECT e.name, q1, q2, q3, q4, ROWNUM
 FROM emp_sal) a,
(SELECT ROWNUM AS qtr
 FROM (SELECT LEVEL FROM DUAL CONNECT BY ROWNUM <=4)) b
ORDER BY 1, 2;

1. SORT / MERGE JOIN

- 연결 고리에 인덱스가 전혀 없는 경우에 사용.
- 대용량의 자료를 조인해야 함으로써  인덱스 사용에 따른 랜덤 엑세스의 오버헤드가 많은 경우에 사용.

* 튜닝을 하기 위해서는 각 테이블로부터 데이터를 빨리 읽어 들이도록 해야 함.
  조인하기 전 정렬을 빠르게 하기 위해 메모리(SORT_AREA_SIZE)를 최적화해야 한다.

- 조인하고자 하는 각 테이블에 대해서 독립적으로 데이터를 읽어 들일 때, 이를 얼마나 빠르게 할 것 인가가 중요함 (FTS 인 경우, I/O 시 읽어 들이는 blocks 수를 늘리는 방법을 고려해 볼 수 있음.)
- 각 테이블로부터 읽혀진 데이터를 연결고리에 대해 정렬을 수행할 때 이를 얼마나 빠르게 할 것인가가 중요하다.

2. SORT / MERGE JOIN의 수행절차

① Driving, Driven Table이 없으므로 각 테이블에 대해 동시에 독립적으로 데이터를 먼저 읽어 들임
② 읽혀진 각 테이블의 데이터를 조인을 위한 연결 고리에 대하여 정렬을 수행함. (모든 테이블에 대한 정렬이 끝나야 다음 단계로 넘어갈 수 있음)
③ 정렬이 모두 끝난 후에 조인 작업이 수행됨

* 튜닝을 하기 위해서는 각 테이블로부터 데이터를 빨리 읽어 들이도록 해야 한다.

   또한, 정렬을 빠르게 하기 위해 메모리(SORT_AREA_SIZE)를 최적화해야 한다.

예시) color만 index가 존재하는 경우
SELECT /*+USE_MERGE(a b)*/
            a.color, ... , b.size, ...
FROM table_a a, table_b b
WHERE a.joinkey_a = b.joinkey_b
AND a.color = 'RED'
AND b.size = 'MED';

-> b테이블은 Full Table Scan을 타게 된다.


3. SORT / MERGE JOIN 이 불리한 경우

- JOIN하는 테이블의 정렬속도가 차이가 클 경우, 대기 시간이 크게 발생하므로 성능상 불리하다.
- 각 테이블로 읽어 들인 데이터의 크기가 매우 큰 경우
- 각 테이블로 읽어 들인 데이터를 조인 전, 정렬하는데 이때 정렬할 데이터가 지나치게 큰 경우


4. SORT / MERGE JOIN의 장단점

장점 : 연결고리에 인덱스가 생성되어 있지 않는 경우에 빠른 조인을 위하여 사용 가능
단점 : 각 테이블로부터 읽어 들인 데이터의 크기가 매우 큰 경우 성능상 불리함 

 (데이터 정렬 소요시간, 데이터 스캔 소요시간)


5. HASH JOIN

- NESTED LOOPS JOIN은 인덱스 사용에 의한 랜덤 엑세스 오버헤드 이슈가 있음.
- SORT/MERGE JOIN은 정렬작업으로 인한 오버헤드 이슈가 있음.

- SORT/MERGE JOIN과 비교해 보면, 각 테이블에 대한 처리를 독립적으로 하는 것은 같지만, HASH JOIN에서는 Driving Table이 존재함.
- 읽어 들인 각 테이블의 데이터를 서로 조인하기 위해 해싱(Hashing)을 이용해서 해시 값을 만듦 (Driving Table을 먼저 읽어 들여 해시값을 메모리에 올려두는 작업을 수행)
-> 해시 값으로 조인을 수행함.

* Driving table을 잘 결정해야함. (적은 양의 데이터를 가진 테이블을 선택하는 게 중요)
   각 테이블로부터 데이터를 읽어 들일 때, 빨리 읽을 수 있도록 함
   메모리(HASH_AREA_SIZE)를 최적화하는게 중요함. (보통 SORT_AREA_SIZE의 2배를 기본 값으로 설정)


6. HASH JOIN의 수행절차

① Driving Table 결정
② Driving Table의 연결조건 칼럼 해싱 및 해시 값 생성
③ 읽어 들인 데이터와 해싱에서 만들어진 해시 값을 메모리에 저장
④ Hash Join이 적용될 테이블의 연결조건 칼럼 해싱 및 해시 값 생성
⑤ 읽어 들인 데이터와 해싱에서 만들어진 해시 값을 메모리에 저장
⑥ 각 테이블에 조인할 데이터가 있는지, 조인하고자 만들었던 해시 값 간에 충돌이 있는지 확인

 (충돌이 있을 경우, 2차 해싱을 수행)
⑦ 각 테이블의 해시값을 '='로 조인을 수행함.


7. HASH JOIN의 장단점
- Hash Bucket이 조인 집합에 구성되어 해시 함수 결과를 저장해야 하는데 이러한 처리에는 많은 메모리와 CPU 자원을 소모하게 됨.
- 기본적으로 HASH_AREA_SIZE에 지정된 크기만큼의 메모리가 할당되어 사용됨.
* 조인을 수행하기에 메모리가 부족하다면 가장 큰 순서대로 Hash Bucket이 Temporary Tablespace로 내려가서 구성되며,
 디스크로 내려간 Hash Bucket에 변경이 일어날 때마다 디스크 I/O가 발생하게 되어 성능이 현저하게 저하된다.
  하드웨어 자원이 넉넉한 상황에서는 다른 조인에 비해 보다 효율적인 수행이 가능하지만, 부족한 상황에서는 다른 조인 방법보다 오히려 느려질 수 있다는 이슈가 발생.

장점 : 하드웨어 자원이 넉넉한 상황에서는 다른 조인에 비해 보다 효율적인 수행이 가능함
단점 : 하드웨어 자원이 부족한 상황에서는 다른 조인 방법보다 비효율적

1. NESTED LOOPS JOIN (NL Join)

- 옵티마이저가 Driving Table을 결정함. (Outer Table)
- Driving Table이 아닌 테이블은 Drvien Table로 지정함 (Inner Table)
- Driving Table의 각 row에 대해 이들이 추출될 때마다 Driven Table의 연관된 모든 row를 조인에 의해 엑세스

* NESTED LOOPS JOIN의 튜닝포인트
① 테이블 간 조인 횟수를 최소화 할 수 있도록 Driving Table을 구성 -> 조인 순서 제어
② Driven Table의 연결고리 칼럼에 대한 인덱스 구성
(연결되는 테이블은 인덱스가 있어야 효율적으로 쿼리 실행이 가능함)

* NESTED LOOPS JOIN의 특징
① 인덱스에 의한 랜덤 엑세스에 기반하고 있기 때문에 대량의 데이터 처리 시 적합하지 않음. (테이블 간의 조인 횟수 최소화를 위한 조인 순서의 최적화)
② Driving Table로는 테이블의 데이터가 적은 마스터 테이블이거나, where절 조건으로 적절하게 row를 제어할 수 있는 것이어야 함
③ Driven Table에는 조인을 위한 적절한 인덱스가 생성되어 있어야 한다.


2. NESTED LOOPS JOIN의 수행 절차

ex) joinkey_a, joinkey_b, color, size 등은 모두 인덱스임
SELECT /*+ORDERED USE_NL(a b)*/
a.color, ... , b.size, ...
FROM table_a a, table_b b
WHEREE a.joinkey_a = b.joinkey_b
AND a.color = 'RED'
AND b.size = 'MED'
;
힌트(a b) 구문을 통해 table a를 먼저 읽고(driving table),  USE_NL로 인해 NESTED LOOPS를 사용해 table b를 읽어온다. 연결고리 칼럼 joinkey_b에 인덱스가 존재하므로 효율적으로 쿼리가 실행된다,


3. NESTED LOOPS JOIN 장단점
*장점 
① 인덱스를 통한 랜덤 엑세스(random access) 기반에서 좋은 성능을 보인다.
*단점

① 인덱스가 없는 상태에선 속도가 저하됨
② 대용량 데이터를 처리할 경우 성능이 저하됨.


4. DRVING TABLE의 원리

위 그림과 같이, 여러 번의 NL Join을 진행할 경우, 데이터가 적은 테이블부터 NL Join을 진행하는 것이 효율적.

5. 조인 순서 제어 방법

① 힌트로 아래와 같이 순서를 제어 
/*+ORDERED */
-> FROM 절에 기술한 테이블 순서대로 제어
/*+LEADING(table명) */
-> 힌트 내에 제시한 테이블이 Driving Table으로 채택됨
(ORDERED 힌트와 같이 사용할 경우 LEADING 힌트는 무시됨)

② 뷰(view) 활용
③ suppressing 활용
④ FROM 절의 테이블 순서 변경
(RBO 에서는 각 테이블에 대한 규칙이 동일할 때, FROM 절로부터 멀리 있는 테이블부터 처리함, CBO에서는 이 방법이 의미가 없다.)

6. 연결고리에 대한 인덱스

① 양 쪽 모두 인덱스가 있는 경우
- 두 테이블 중 조회되는 결과가 적은 테이블을 선택하여 driving table로 선택
② 한쪽만 인덱스가 있는 경우
- 인덱스가 없는 쪽 테이블을 driving table로 사용함.
③ 양쪽 모두 연결고리에 대한 인덱스가 없는 경우
ex)  SELECT *
       FROM TAB_A A, TAB_B B
        WHERE A.NAME = B.NAME;
A -> B 이던, B -> A이던 Full table scan으로 간다.

driven 테이블에 대한 Full table scan 회수는 driving 테이블로부터 읽어 들이는 row 수만큼 된다.

이러한 상황에서 할 수 있는 조인이 Sort Merge와 Hash조인, Nested Loops 조인방식으로 조인이 이뤄지지 않는다.

- 인덱스로 구성된 칼럼에 변형을 가하게 되면 옵티마이저는 해당 인덱스를 사용하지 못하게 된다.
- 많은 양의 데이터를 갖는 테이블인 경우, 전체의 15% 이하를 처리할 때만 인덱스 사용이 빠르다.


1. 인덱스 활용이 불가능한 경우
- 인덱스 스캔이 무조건 빠른건 아니다.
- 조건에 의한 처리범위가 넓어짐으로 인해 분포도가 나빠지는 경우가 있는데, 이 경우 인덱스 스캔을 하는 것보다는 FULL TABLE SCAN을 하는 것이 바람직함.
-> FULL TABLE SCAN 시엔 한 번의 I/O 때마다 여러 개의 데이터 Blocks을 처리하기 때문에 I/O 횟수가 감소하게 됨. 

(인덱스는 한번의 I/O 발생 시, 하나의 Block만 처리가 가능함.)


2. 인덱스 사용이 불가능한 경우

① NOT 연산자 사용
ex)  SELECT * FROM emp WHERE empno!= 2170000;
       SELECT * FROM emp WHERE emp <> 2170000;
한 건의 데이터를 제외하고 나머지 모두를 찾는 것 이므로, FULL TABLE SCAN이 유리하다.

② IS NULL, IS NOT NULL 사용
ex) SELECT * FROM emp WHERE empno is NULL;
INDEX에는 NULL 값이 저장되지 않음. 그러므로 NULL 과의 비교연산자는 인덱스를 사용할 수 없다.

③ 옵티마이저의 취사 선택
옵티마이저의 자의적 판단에 의해서 인덱스를 사용할 수도 있고 사용하지 않을 수도 있다. 이러한 것을 '취사선택'이라 한다. Rule Base Optimizer와 Cost Base Optimizer에서는 옵티마이저의 자의적 판단으로 인한 잘못된 선택을 강제로 제어하기 위해 Hint를 사용한다.

④ External suppressing 
suppressing 이란 인덱스로 구성된 컬럼에 변형을 가했을 때, 그로 인해 해당 컬럼으로 구성된 인덱스를 사용하지 못하는 것을 의미함. External suppressing은 코드와 같이 겉으로 드러나는 suppressing을 의미함.

ex1) 불필요한 함수를 사용하는 경우
WHERE SUBSTR(ename, 1, 1) = 'M' (SUBSTR로 칼럼에 변형을 가함)
-> WHERE ename LIKE 'M%'; 로 사용해야 INDEX 사용 가능.

ex2) 문자열 결합
WHERE job || deptno = 'MANAGER10'; (|| 로 칼럼을 변형)
-> WHERE job = 'MANAGER' AND deptno = 10;  을 사용해야 인덱스를 사용가능.

ex3) DATE 변수의 가공
WHERE TO_CHAR(hiredate, 'YYYYMMDD') = '20021023'
(TO_CHAR로 칼럼이 변형됨)
-> WHERE hiredate BETWEEN TO_DATE('20021016' ,'YYYYMMDD') AND TO_DATE('20021023', 'YYYYMMDD') 와 같이 사용하는 게 좋다.

ex4) 산술식의 적용
WHERE sal*12 > 40000;
WHERE sal > 40000/12; -> 이렇게 사용

⑤ Internal suppressing
Internal suppressing은 DB 내부에서 자체적으로 존재하는 suppressing을 의미함

NUMBER, DATE, CHAR 형을 VARCHAR2로 바꾸거나, 그 반대의 경우들이 있음. 즉 서로 다른 data type끼리 비교와 연산이 발생하는 경우를 의미한다.

3. 옵티마이저에 의한 선택절차

특정 테이블에 대해서 SQL의 주어진 조건으로 인해 사용될 수 있는 인덱스가 두 개 이상인 경우, 옵티마이저는 조건에 가장 적절한 인덱스를 선택하여 사용함. 
-> 주어진 조건에 가장 적절한 인덱스를 선택하려 할 때, 일련의 절차에 따라 결정한다.

* 옵티마이저의 인덱스 선택 시 판단 절차
① 주어진 조건에 대한 각 인덱스 별로 매칭률을 계산해서 매칭률이 높은 것을 우선적으로 선택한다.
* 인덱스매칭률 =  WHERE절에서 1st 칼럼부터 연속된 칼럼에 대해 상수(값)를 '='로 비교하는 칼럼의 개수 / 인덱스를 구성하는 총 칼럼의 개수
② 인덱스 별 매칭률이 같을 경우에는 인덱스를 구성하는 칼럼의 개수가 많은 것을 우선적으로 선택한다.
③  인덱브 별 매칭률과 인덱스를 구성하는 칼럼의 개수가 같은 경우에는 가장 최근에 생성된 것을 우선적으로 선택한다.

위 사례에서는 인덱스 변경 전에는 IX1_SALES 인덱스를 타지만, 인덱스 변경 후에는 IX2_SALES 인덱스를 타게된다.

* RBO와 CBO가 선택한 인덱스 차이
ex)
EC_COURSE_SQ_PK : COURSE_CODE + YEAR + COURSE_SQ_NO

EC_COURSE_SQ_IDX_01 : YEAR (Non Unique)

SELECT MIN(course_sq_no) AS min_sq

               MAX(course_sq_no) AS max_sq

FROM ec_course_sq

WHERE course_code = 1960

AND year = '2002' ;

RBO은 인덱스 매칭률이 높은 것을 선택하므로,

EC_COURSE_SQ_PK 인덱스 매칭률 : (2/3)

EC_COURSE_SQ_IDX_01 : 인덱스 매칭률 (1/1) 에 따라 EC_COURSE_SQ_IDX_01인덱스를 선택함.

CBO는 YEAR 칼럼을 하나 사용하는 것 보다, COURSE_CODE + YEAR 결합 칼럼이 데이터 범위를 줄여줄 수 있다면, EC_COURSE_SQ_PK 인덱스를 선택한다.

-> CBO, RBO의 실행계획이 서로 달라질 수 있으므로, 좋은 인덱스는 아니다.

1. 인덱스 머지(Index Merge) vs 결합인덱스(Composite Index)

인덱스 머지는 각각 단 하나의 칼럼으로 구성된 인덱스들을 동시에 사용하는 방법.(개별 칼럼에 인덱스가 생성되어 있으면서 모두 where 절에 '=' 조건으로 사용되며, 각각의 인덱스 조합으로 자료에 접근하는 것을 의미함.)
결합인덱스는 2개 이상의 칼럼으로 구성된 인덱스를 의미함.
결합인덱스가 보다 간결한 프로세스, 좋은 엑세스 경로를 제공함.

2. 결합인덱스의 구성

1) 결합인덱스 칼럼 선택
① WHERE 절에서 AND 조건으로 자주 결합되어 사용되면서 각각의 분포도 보다 두 개의 칼럼이 결합될 때 분포도가 좋아지는 칼럼들 

(분포도란 '칼럼에 속한 데이터의 종류가 많다' 라는 의미.  분포도 = 1 / 칼럼의 DISTINCT 데이터 수 * 100)
② 다른 테이블과 조인의 연결고리로 자주 사용되는 칼럼들
③ 하나 이상의 키 칼럼 조건으로 같은 테이블의 칼럼들이 자주 조회될 때, 이러한 칼럼을 모두 포함(결합)

2) 결합인덱스의 칼럼 순서 결정
① WHERE 절 조건에 많이 사용되는 칼럼 우선
② Equal('=')로 사용되는 칼럼 우선
③ 분포도가 좋은 칼럼 우선
④ 자주 이용되는 Sort의 수서로 결정
* 고정된 Sort의 순서를 가진 SQL문이 자주 사용되는 경우에는 2-3), 2-4)의 비중이 바뀔 수 있음.


3. 결합인덱스 사용 방법

결합인덱스의 첫 번째 칼럼이 where 절에서 제외되어 있는 경우, 결합인덱스를 사용할 수 없음.

* Skip Scanning : 결합인덱스의 첫 번째 칼럼이 WHERE절에서 제외되어 있고, 두 번째 칼럼부터 WHERE절에 조건으로 기술되어 있는 경우에도, 그 인덱스가 사용되는 경우를 의미한다.

* Skip Scannig을 쓰려면 아래와 같이 Hint를 주면 된다
INDEX_SS (테이블명 INDEX명)
INDEX_SS_ASC (테이블명 INDEX명)
INDEX_SS_DESC (테이블명 INDEX명)

4. 결합인덱스 칼럼에 대한 '='의 의미

체크조건이 될 수도, 범위 제한 조건이 될 수도 있다.
(인덱스 순서대로 바로 사용된다면, 범위제한조건이 되고 아니면 체크 조건이 되어 버린다.)

예시)  AREA_X1 : (시, 구, 동) 이라는 index가 존재

WHERE 시 LIKE '서%' 

AND 구 =  '강남구'  ← 체크조건

AND 동 =  '역삼동'  ← 체크조건 

WHERE 시 =  '서울시'  ← 범위제한조건 

AND 구 LIKE '강%'

AND 동 =  '역삼동'  ← 체크조건 

WHERE 시 = '서울시'  ← 범위제한조건 

AND 동 = '역삼동'  ← 체크조건 

* 인덱스 매칭률 =

 WHERE절에서 첫번째 칼럼부터 연속된 칼럼에 대해 상수(값)를 '='로 비교하는 칼럼의 개수 / 인덱스를 구성하는 칼럼의 총 개수  (범위제한 조건인 '=')

예시) AREA_X1 : (시, 구, 동) 이라는 index가 존재

WHERE 시 = '서울시' (매칭률 = 1/3)

WHERE 시 = '서울시'

AND 구 = '강남구'  (매칭률 = 2/3)

WHERE 시 = '서울시'

AND 구 =  '강남구' 

AND 동 =  '역삼동'  (매칭률 = 3/3)

* 인덱스 매칭률 향상을 통한 속도 개선방법 예시

EMP_PAY_X1 : (급여연월, 급여코드, 사원번호) 라는 index가 존재

WHERE 급여연월 LIKE '2016%'

AND 급여코드 = '정기급여' ;

-> 아래와 같이 튜닝이 가능함,

WHERE 급여연월 IN ('201612', '201611', '201610',  ..... , '201601')

AND 급여코드 = '정기급여'

;

1. 인덱스의 필요성

DB에 저장된 자료를 더욱 빠르게 조회하기 위해 인덱스를 생성하여 사용함.
일반적으로, 인덱스는 테이블의 전체 데이터 중에서 일반적인 경우, 10~15% 이하의 데이터를 처리하는 경우에 효율적이며 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 좋다. 

(테이블마다 저장된 데이터의 수가 다르므로 그 기준은 절대적이진 않다.)

2.  B* Tree(Balanced Tree)구조

가장 많이 사용되는 인덱스의 구조이며, 인덱스의 데이터 저장 방식이기도 함

- Root(기준) / Branch(중간) / Leaf(말단) Node로 구성됨
- Branch 노드는 Leaf 노드에 연결되어 있으며, 조회하려는 값이 있는 Leaf 노드까지 도달하기 위해 비교/분기해야 될 값들이 저장됨.
- 찾는 데이터는 Leaf 노드에 저장됨.

Leaf 노드 = 인덱스 칼럼의 값(오름(내림)차순으로 Sort되어 저장됨) + ROWID (테이블에 있는 해당 row를 찾기 위해 사용되는 논리적인(저장위치) 정보)

B*Tree 구조의 핵심은 Sort
- Order by에 의한 Sort를 피할 수 없음. (memory 소비가 줄어든다.)
- MAX/MIN의 효율적인 처리가 가능함. (미리 sorting 되어 있으므로)

3. 인덱스 선정 절차

1) 프로그램 개발에 이용된 모든 테이블에 대하여 Access Path 조사
2) 인덱스 칼럼 선정 및 분포도 조사
3) Critical Access Path(자주 수행되는 경로) 결정 및 우선 순위 결정
4) 인덱스 칼럼의 조합 및 순서 결정
5) 시험 생성 및 테스트
6) 결정된 인덱스를 기준으로 프로그램 반영
7) 실제 적용

4. 인덱스 생성 및 변경 시 고려할 사항 

- 기존 프로그램에 미치는 영향도 검토
- 필요할 때 마다 인덱스 생성으로 인한 인덱스 개수의 증가와 이로 인한 DML 작업의 속도가 지연될 수 있음.
- 비록 개별 칼럼의 분포도가 좋지 않을지라도 다른 칼럼과 결합하여 자주 사용되고, 결합할 경우에 분포도가 양호하다면 결합 인덱스 생성을 긍정적으로 검토

5. 인덱스 스캔의 원리

OPTIMIZER가 인덱스 사용을 위한 실행계획을 아래와 같이 수립한다.
1) 조건을 만족하는 최초의 인덱스 row를 찾음
2) Access 된 인덱스 row의 ROWID를 이용해서 테이블에 있는 row를 찾음 (Random Access)
3) 처리 범위가 끝날 때까지 차례대로 다음 인덱스 row를 찾으면서(Scan) 2)를 반복함.

인덱스 스캔시에는 한 번의 I/O가 발생할 때 마다 하나의 Block을 처리함.

1. 옵티마이저의 개념

사용자가 실행한 SQL을 해석하고, 데이터 추출을 위한 실행계획을 수립하는 프로세스. 프로세스가 수립하는 실행계획은 전반적인 SQL의 속도를 결정 짓는다.


2. 옵티마이저의 종류

오라클에서는 RBO(Rule Based Optimizer), CBO(Cost Based Optimizer) 2가지의 옵티마이저를 지원해주고 있다. 나머지 DBMS는 CBO만 지원해주고 있다. 
RBO는 초창기 버전부터 제공. CBO는 v10g부터 기본적인 설정으로 적용되고 있다. 

1) RBO
- 기본적으로 15개의 순위가 매겨진 규칙이 있음. (이를 기초로 하여 실행계획을 수립함.) 순위는 인덱스 구조나 사용하는 연산자에 따라 각각 다르다.
- SQL에 대한 실행계획이 하나 이상일 경우엔, 순위가 높은 규칙을 이용하게 됨.
- 수립될 실행계획이 예측 가능하기 때문에 개발자가 원하는 처리 경로로 유도하기가 쉬움.

* 아래 표는 RBO의 우선 순위를 보여준다.

(Rank 1 ~ 4는 하나의 데이터만 찾는다는게 특징)

2) CBO
- 대상 row들을 처리하는데 필요한 자원 사용을 최소화해서, 궁극적으로 데이터를 빨리 처리하는 데 목적이 있음
- CBO에 영향을 미치는 산정 요소
(각종 통계 정보, Hint, 연산자, Index, Cluster, DBMS 버전, CPU/Memory 용량, Disk I/O 등과 같이 매우 다양함) 

* 통계정보
- CBO의 성능을 최적의 상태로 유지시키기 위해서 테이블, 인덱스, 클러스터 등을 대상으로 통계정보를 생성함 -> 정기적으로 ANALYZE 작업을 하는 것이 가장 중요함(변경된 부분이 생길 수도 있으므로)
- 가장 효율적인 실행계획을 수립하기 위해 최소비용을 계산할 때 중요하게 사용됨

ANALYZE 예시)
ANALYZE TABLE emp COMPUTE STATISTICS; (전체대상)
ANALYZE TABLE emp ESTIMATE STATISTICS
  SAMPLE 10 PERCENT;
ANALYZE TABLE emp ESTIMATE STATISTICS
  SAMPLE 5 ROWS;

ANALYZE 실행 여부 확인 쿼리)
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME IN ('EMP', 'DEPT')
;

-> 오라클에서는 DBMS_STATS Package를 통해 확인 가능함.
(DBMS_STATS.GATHER_TABLE_STATS, DBMS_STATS.GATHER_SCHEMA_STATS, DBMS_STATS.GATHER_DATABASE_STATS)


3. 옵티마이저의 레벨별 설정

1) Instance Level 

- initSID.ora를 이용해서 지정함 (DB전체 차원)
OPTIMIZER_MODE = RULE, CHOOSE, FIRST_ROWS, ALL_ROWS 중에 하나 선택
(9g까지는 CHOOSE가 default, 10g부터는 ALL_ROWS가 default)

2) Session Level
- ALTER SESSION SET OPTIMIZER_MODE = RULE, CHOOSE, FIRST_ROWS, ALL_ROWS 중 하나를 선택

3) Statement Level
- 아래 쿼리와 같은 힌트 사용
SELECT /*+first_rows*/
ename
FROM emp;

* 우선 순위는 Statement Level이 가장 높다.


4. RBO와 CBO의 실행계획 비교

- 동일 SQL에 대해서 각 OPTIMIZER가 수립한 실행계획은 서로 다를 수 있음.
- 이는 SQL의 퍼포먼스가 OPTIMIZER에 따라 다르다는 의미.
- OPTIMIZER 종류에 따라 달라지는 DB성능의 차이점을 이해하는 것이 중요함.

인덱스 :

EC_TASK_PK : COURSE_CODE + TASK_NO

EC_TASK_TERM_IDX00 : COURSE_CODE + TASK_NO + YEAR + COURSE_SQ_NO

쿼리문 :

SELECT 

A.COURSE_CODE, A.TASK_NO, A.BBS_YN, A.UPDATE_DATE,

B.YEAR,  B.S_DATE, B.E_DATE

FROM EC_TASK A, EC_TASK_TERM B

WHERE A.COURSE_CODE = B.COURSE_CODE

AND A.TASK_NO = B.TASK_NO

AND B.COURSE_CODE = 36;

AND B.TASK_NO = 1

AND B.COURSE_SQ_NO = 1

;

위 쿼리문을 RBO로 해석하면 B에서 WHERE 조건절을 통해 데이터를 찾고 A와 조인을 통해 데이터를 합친다.

(B -> A 의 순서)

이에 반해 CBO로 해석하면 JOIN 조건인 COURSE_CODE, TASK_NO의 조건이 A에도 입력되어 있다 가정하고 A에서 데이터를 먼저 찾고 B와 합친다. (A는 조건이 2개(COURSE_CODE, TASK_NO)지만 B는 조건이 3개(COURSE_CODE, TASK_NO, COURSE_SQ_NO) (A-> B의 순서)

SQL를 만들 때, 실행계획이 차이나면 좋지 못한 SQL이다.예시에서는 CBO가 순서가 더 빠르다 ROWS가 적으므로
SQL을 수정한다면, 누가봐도 A에서 데이터를 찾고 B에서 데이터를 찾도록 아래와 같이 수정해야한다,

수정된 쿼리문 :

SELECT 

A.COURSE_CODE, A.TASK_NO, A.BBS_YN, A.UPDATE_DATE,

B.YEAR,  B.S_DATE, B.E_DATE

FROM EC_TASK A, EC_TASK_TERM B

WHERE A.COURSE_CODE = B.COURSE_CODE

AND A.TASK_NO = B.TASK_NO

AND A.COURSE_CODE = 36;

AND A.TASK_NO = 1

AND B.COURSE_SQ_NO = 1

;

사내 교육으로 듣게된 SQL 튜닝 기법에 대한 강의 내용을 정리해본다.

1. 실행계획의 정의

실행계획 : 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때, 옵티마이저가 수립하는 작업절차, SQL의 Performance를 결정짓는 요인. 실행계획이 목적했던 대로 실행되지 않으면, SQL Performance에 문제가 발생함.

OPTIMIZER : SQL 해석 > 실행계획(데이터 처리 절차) 수립 > 실행


2. 실행계획의 확인 방법

오라클에서는 아래 명령어를 사용해 확인 가능함.
(특별한 권한부여가 필요 없다.)

*EXPLAIN PLAN
- SQL에 대한 실행계획만을 확인할 수 있음
- 명령을 사용할 때 데이터를 처리하지 않음. (DB에 부하를 미치지 않음.)
- 데이터를 읽지 않기에 소요시간과 I/O, Sorting 관련 정보는 확인이 어렵다.

*SET AUTOTRACE
- EXPLAIN PLAN 명령과 달리 한 번의 명령으로, 여러 개의 SQL에 대한 실행계획을 바로 볼 수 있음.
- 다양하게 옵션을 사용할 수 있어서 여러 가지의 정보를 선택적으로 확인할 수 있음. (SET AUTOTRACE 뒤에 ON EXPLAIN, ON STATISTICS, TRACEONLY, TRACEONLY EXPLAIN, TRACEONLY STATISTICS, OFF 등의 옵션들이 붙는다.)

큰 데이터에 대한 실행계획을 알아보기 위해서는 실제 데이터를 읽지 않는게 좋은 방법이지만, 데이터의 크기가 작다면 데이터를 읽어가면서 실행계획을 확인하는게 좋은 방법이 될 수 있다.

3. 실행계획 분석

 실행계획은 데이터 처리를 위한 작업 방법으로, 실행계획을 구성하는 내용의 분석을 통해 SQL의 비효율적인 부분을 확인할 수 있음. 실행계획 분석을 통해 SQL이 작성자의 의도대로 실행되었는지 확인할 수 있으며, 그렇지 않았을 경우 성능상 문제가 되는 부분을 찾아낼 수 있음. 실행계획의 정확한 분석을 통해 SQL문장의 튜닝 포인트를 도출할 수 있음.

명령어 예시) 
SET AUTOTRACE TRACEONLY EXPLAIN;
SELECT/*+USE_NL(e d) */
e.name, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

실행결과 예시)

ID | Opreation                                                      | Name

0   |  SELECT STATMENT                                   |
1   |  NESTED LOOPS                                         |
2   |      NESTED LOOPS                                     |
3   |          TABLE ACCESS FULL                        | EMP
4   |          INDEX UNIQUE SCAN                       | PK_DEPT
5   |      TABLE ACCESS BY INDEX ROWID      | DEPT

들여쓰기 depth가 같다면 위에 있는 것이 먼저 실행되며,  실행계획을 도식화하면 아래 트리구와 같다.

위 트리구조의 실행순서 : 3 -> 4 -> 2 -> 5 -> 1 -> 0

가장 먼저실행되는 것은 가장 왼쪽하단에 위치한 노드이며, 왼쪽 하단 노드 -> 오른쪽 하단 노드 -> 중간 상단 노드의 순서로 실행되는 트리구조이다.