HighLight in your life~

*I/O 튜닝의 핵심 원리

 하나, Random 액세스 발생량을 줄인다.

 둘, Sequential 액세스에 의한 선택 비중을 높인다.

1. 테이블 Random 액세스 최소화 튜닝 - SQL전문가 가이드 p.628~p.630

(하나, Random 액세스 발생량을 줄인다.)

1) 인덱스 칼럼 추가
2) Covered Index
3) Include Index
4) IOT, 클러스터형 인덱스, 클러스터 테이블 활용
5) 수동으로 클러스터링 팩터 높이기

2. 인덱스 스캔범위 최소화 - SQL전문가 가이드 p.631~p.637

(둘, Sequential 액세스에 의한 선택 비중을 높인다.)

1) 인덱스 선행 칼럼이 범위조건일 때의 비효율

    - 인덱스 선행 칼럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를

스캔하는 단계에서 비효율이 발생한다.

2) 범위조건을 In-List로 전환

 (가) 1차 단계 - BETWEEN

 SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

 FROM 매물아파트매매

 WHERE 아파트시세코드 = 'A0100'

 AND 평형 = '59'

 AND 평형타입 = 'A'

 AND 인터넷매물 BETWEEN '1' AND '2'

 ORDER BY 입력일 DESC

 ;

 (나) 2차 단계 - IN

SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

FROM 매물아파트매매

WHERE 아파트시세코드 = 'A0100'

AND 평형 = '59'

AND 평형타입 = 'A'

AND 인터넷매물 IN ('1',  '2')

ORDER BY 입력일 DESC

;

 3) 조건을 2개 이상 사용할 때의 비효율
 - 인덱스 구성 [회사+지역+상품명]일 때, 아래와 같이 범위검색 조건을 2개 이상 사용하면 첫 번째가 인덱스 스캔 범위를 거의 결정되고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.

(가) 1차 단계

SELECT 고객ID, 상품명, 지역, ...

FROM 가입상품

WHERE 회사 = :com

AND 지역 LIKE :reg || '%'

1. 비트맵 인덱스는 Lock에 의한 DML 부하가 심하다.

   - 레코드 하나만 변경되더라도 해당 비트맵 범위에 속한 모든 레코드에 Lock이 걸린다.

   - OLTP성 환경에 비트맵 인덱스를 쓸수 없다.

   - 읽기 위주의 대용량 DW(특히, OLAP) 환경에 아주 적합하다.

2. Oracle B*Tree 인덱스와 달리 비트맵 인덱스는 NULL도 저장

       SELECT * FROM 상품

WHERE 색상 IS NULL

;

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

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

 칼럼의 Distinct Value 개수가 적을 때 비트맵 인덱스를 사용하면 저장효율이 매우 좋다. B*Tree 인덱스보다 훨씬 적은 용량을 차지하므로 인덱스가 여러 개 필요한 대용량 테이블에 유용하다. 다양한 분석관점(Dimension)을 가진 팩트성 테이블이 주로 여기에 속한다. 반대로 Distinct Value가 아주 많은 칼럼이면 오히려 B*Tree 인덱스보다

많은 공간을 차지한다.

 Distinct Value 개수가 적은 칼럼일 때 저장효율이 좋지만 테이블 Random 액세스 발생 측면에서는 B*Tree 인덱스와 똑같기 때문에 그런 칼럼을 비트맵 인덱스로 검색하면 그다지 좋은 성능을 기대하기 어렵다. 스캔할 인덱스 블록이 줄어도는 정도의 성능 이점만 얻을 수 있고, 따라서 하나의 비트맵 인덱스 단독으로는 쓰임새가 별로 없다. 그 대신, 여러 비트맵 인덱스를 동시에 사용할 수 있는 특징 때문에 대용량 데이터 검색 성능을 향상시키는 데에 효과가 있다.

 비트맵 인덱스는 여러 인덱스를 동시에 활용할 수 있다는 장점 때문에 다양한 조건이 사용되는, 특히 정형화되지 않은 임의 질의(ad-hoc query)가 많은 환경에 적합하다.

SELECT 지역, SUM(판매량), SUM(판매금액)

FROM 연도별지역별상품매출

WHERE (크기 = 'SMALL' OR 크기 IS NULL)

AND 색상 = 'GREEN'

AND 출시연도 = '2012'

GROUP BY 지역

;

*인덱스를 재생성하는 데 걸리는 시간과 부하를 무시할 수 없다. 따라서 인덱스의 주기적인 재생성 작업은 아래와 같이 예상효과가 확실할 때만 시행하는 것이 바람직하다.

 1. 인덱스 분할에 의한 경합이 현저히 높을 때

 2. 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때, 특히 NL Join에서 반복 액세스되는 인덱스 높이(height)가 증가했을 때

 3. 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때

 4. 총 레코드 수가 일정한데도 인덱스가 계속 커질 때

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

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

 Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나빠지면 인덱스를 재생성하거나 DBMS가 제공하는 명령어를 이용해 빈 공간을 제거하는 것이 유용할 수 있다. 하지만 일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋다. 왜냐하면, 빈 공간을 제거해 인덱스 구조를 슬림(slim)화하면 저장 효율이나 스캔효율에 좋겠지만 인덱스 분할이 자주 발생해 DML 성능이 나빠질 수 있기 때문이다.

 인덱스 분할에 의한 경합을 줄일 목적으로, 초기부터 빈 공간을 남기도록 옵션을 주고 인덱스를 재생성할 수도 있다. 하지만 효과는 일시적이다. 언젠가 빈 공간이 다시 채워지기 때문이며, 결국 적당한 시점마다 재생성 작업을 반복하지 않는 한 근본적인 해결책이 되지는 못 한다.

*OR조건은 Full Table Scan된다.

1차 단계- OR조건은 Full Table Scan된다.

 SELECT * FROM EMP

  WHERE JOB = 'CLERK' OR DEPTNO = 20;

2차 단계- JOB과 DEPTNO에 각각 생성된 인덱스를 사용하고 싶다면 UNION ALL 사용

 SELECT * FROM EMP

  WHERE JOB = 'CLERK'

  UNION ALL

 SELECT * FROM EMP

  WHERE DEPTNO = 20

  AND LNNVL(JOB = 'CLERK')

 ;

JOB과 DEPTNO 칼럼을 선두로 갖는 두 인덱스가 각각 사용되었고, UNION ALL 위쪽 브랜치는 JOB = 'CLERK'인 집합을 읽고 아래쪽 브랜치는 DEPTNO = 20 인 집합만을 읽는다.

 분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, EMP 테이블 액세스가 두 번 일어난다. 따라서 중복 액세스되는 영역(DEPTNO = 20 이면서 JOB = 'CLERK')의 데이터 비중이 작을 수록 효과적이고, 그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다.

 중복 액세스되더라도 결과집합에는 중복이 없게 하려고 UNION ALL 아래쪽에 ORACLE이 내부적으로 LNNVL함수를 사용한 것을 알 수 있다. JOB <> 'CLERK' 이거나 JOB IS NULL인 집합만을 읽으려는 것이며, 이 함수는 조건식이 FALSE이거나 알 수 없는(UNKNOWN) 값일 때 TRUE를 리턴한다.

 *ORACLE에서 OR-Expansion 제어(힌트 사용)

 SELECT /*+ USE_CONCAT */ FROM EMP  -- OR Expansion 기능 유도

WHERE JOB = 'CLERK' OR DEPTNO = 20;

 SELECT /*+ NO_EXPAND */ FROM EMP  -- OR Expansion 기능 방지

WHERE JOB = 'CLERK' OR DEPTNO = 20;

P.S. 사용자 쿼리를 직접 바꿔주지 않아도 옵티마이저가 2차 단계를 대신해 주는 경우가 있는데 이를 'OR-Expansion'이라고 한다.

*조인 제거(Join Elimination) 또는 테이블 제거(Table Elimination)

 - 1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. 결과집합에 영향을 미치지 않기 때문이다. 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 '조인 제거(Join Elimination)' 또는 '테이블 제거(Table Elimination)'라고 한다.  

 - 조인 제거 기능이 작동하려면 PK와 FK 제약이 설정돼 있어야만 한다. 이는 옵티마이저가 쿼리 변환을 수행하기 위한 지극히 당연한 조건이다. 만약 PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK가 없으면 조인에 실패하는 레코드가 존재할 수도 있어 옵티마이저가 함부로 쿼리 변환을 수행할 수가 없다.

- FK가 설정돼 있더라도 emp의 deptno 칼럼이 Null 허용 칼럼이면 결과가 틀리게 될 수 있다. 조인 칼럼 값이 Null인 레코드는 조인에 실패해야 정상인데, 옵티마이저가 조인문을 함부로 제거하면 그 레코드들이 결과 집합에 포함되기 때문이다. 이런 오류를 방지하기 위해 옵티마이저가 내부적으로 E.DEPTNO IS NOT NULL 조건을 추가해 준다. Oracle 10g까지는 조인 제거가 일어나지 않았지만 11g부터 Inner쪽 테이블 제거 기능이 구현되었다.

 SELECT E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDATE

 FROM DEPT D, EMP E

 WHERE D.DEPTNO = E.DEPTNO;

 ALTER TABLE DEPT ADD

  CONSTRAINT DEPTNO_PK PRIMARY KEY(DEPTNO);

 ALTER TABLE EMP ADD

  CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO)

  REFERENCES DEPT(DEPTNO);

 SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE

 FROM EMP E, DEPT D

 WHERE D.DEPTNO(+) = E.DEPTNO --- Outer 조인

'조인조건(Join Predicate) Pushdown'은 말 그대로 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것으로서 NL Join 수행 중에 드라이빙 테이블에서 읽은 조인 칼럼 값을 Inner 쪽(=right side) 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다.

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

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

1차 단계

 SELECT D.DEPTNO, D.DNAME, E.AVG_SAL

 FROM DEPT D

, (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) E

 WHERE E.DEPTNO(+) = D.DEPTNO;

2차 단계

 SELECT D.DEPTNO, D.DNAME

, (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)

 FROM DEPT D;

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

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

1차 단계 - 집계함수가 여러 개이면 EMP에서 같은 범위를 반복적으로 액세스하는 비효율이 생긴다.

 SELECT D.DEPTNO, D.DNAME

, (SELECT AVG(SAL) FROM EMP WHERE DEPTNO =  D.DEPTNO) AVG_SAL

, (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MIN_SAL

, (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MAX_SAL

 FROM DEPT D;

2차 단계 - 구하고자 하는 값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 SUBSTR함수로 분리하는 방법이 유용할 수 있다.

SELECT DEPTNO, DNAME

, TO_NUMBER(SUBSTR(SAL, 1, 7)) AVG_SAL

, TO_NUMBER(SUBSTR(SAL, 8, 7)) MIN_SAL

, TO_NUMBER(SUBSTR(SAL, 15)) MAX_SAL

FROM (

SELECT /*+ no_merge */ D.DEPTNO, D.DNAME

,(SELECT LPAD(AVG(SAL), 7) || LPAD(MIN(SAL), 7) || MAX(SAL)

FROM EMP WHERE DEPTNO = D.DEPTNO) SAL

FROM DEPT D

);