View Source

h2. 양호한 엑세스 경로를 보장받기 위한 조건
- 좋은 액세스 경로가 생성될 수 있도록 테이블을 설계.
- 적절하고 종합적인 경우를 대비한 인덱스의 지정.
- 넓은 범위의 처리나 조인의 효율성을 향상하기 위한 클러스터링.
- 인덱스 적용원칙에 맞는 SQL코딩.
- 효율적인 SQL구사.
- 통계 자료의 주기적인 재생성.
- 힌트나 사용제한(Suppressing) 기능의 활용.

\\

h2. 인덱스를 사용하지 않는 경우
- 인덱스 컬럼이 비교되기 전에 변형이 일어날 경우.
{code} SELECT deptno FROM dept WHERE SUBSTR(dname,1,3) = 'ABC'{code}
- 부정형(NOT, <>)으로 조건을 기술한 경우.
{code} SELECT ename FROM emp WHERE job <> 'SALES' {code}
- 인덱스 컬럼이 NULL로 비교되는 경우(NULL, NOT NULL의 사용)
{code} SELECT * FROM emp WHERE ename IS NOT NULL {code}
- 옵티마이져가 특정 인덱스의 사용을 취사 선택할 경우 사용되지 않을 수 있음
- 조건의 순위(Ranking), 사용자의 힌트, 통계정보에 의거하여 산출된 액세스비용 등등..
{code} SELECT * FROM emp WHERE job LIKE 'AB%' AND empno = '7890 {code}

\\


h2. 가. 인덱스컬럼의 변형(Suppressing)


h4. 외부적(External) 변형

|| 개선 전 || 개선 후 ||
| SELECT dept, ename
FROM emp
WHERE SUBSTR(job, 1, 4)='SALE' | SELECT dept, ename
FROM emp
WHERE job LIKE 'SALE%' |
| SELECT dept, ename
FROM emp
WHERE sal*12 = 100000 | SELECT dept, ename
FROM emp
WHERE sal = 100000/12 |
| SELECT dept, ename
FROM emp
WHERE NVL(job, 'X') = 'SALE'| SELECT dept, ename
FROM emp
WHERE job = 'SALE' |

\\

h5. 인덱스의 특징을 역으로 이용하여 수행속도를 향상.
h6. 'CUSTNO'와 'STATUS'는 각각 인덱스가 생성되어 있고 'STATUS'가 '90'인 경우에는 분포도가 넓다고 가정하면.
* 개선 전
{code:SQL}
SELECT custno, chuldate
FROM chulgot
WHERE custno = 'DN01'
AND status = '90'
{code}

* 개선 후
{code:SQL}
SELECT custno, chuldate
FROM chulgot
WHERE custno = 'DN01'
AND RTRIM(status) = '90'
{code}

h6. ORD_DATE LIKE '9502%'를 만족하는 로우수가 ORD_DEPT = '12345'를 만족하는 로우 수보다 적다고 가정 했을 경우..
* 개선 전
{code:SQL}
SELECT x.ordno, x.ord_date, y.item, y.ordqty
FROM ORDER1T x, ORDER2T y
WHERE x.ordno = y.ordno
AND x.ord_date LIKE '9502%'
AND y.orddept = '12345'
ORDER BY ord_date
{code}

* 개선 후
{code:SQL}
SELECT x.ordno, x.ord_date, y.item, y.ordqty
FROM ORDER1T x, ORDER2T y
WHERE x.ordno = y.ordno
AND x.ord_date LIKE '9502%'
AND RTRIM(y.orddept = '12345')
ORDER BY ord_date
{code}

h6. sale_dept 가 95년도인 데이터가 아주 많고, sale_dept로 생성된 인덱스가 있다고 가정 했을 경우.
* 개선 전
{code:SQL}
SELECT sal_no, sale_date, sale_dept, saleqty
FROM mechult
WHERE sale_date LIKE '95%'
ORDER BY sale_dept
{code}

* 개선 후
{code:SQL}
SELECT sal_no, sale_date, sale_dept, saleqty
FROM mechult
WHERE RTRIM(sale_date) LIKE '95%'
AND sale_dept > ''
{code}

\\

h4. 내부적 변형

h5. JOIN시 데이터 타입의 통일

| {code:SQL}
SELECT chr,num,var,dat
FROM samplet
WHERE chr = 10
{code} | ===> | {code:SQL}
SELECT chr,num,var,dat
FROM samplet
WHERE to_number(chr) = 10
{code} |

- 문자타입을 숫자와 비교: 숫자로 변형.
- 숫자타입을 문자와 비교: 형변환 없음.(단, like '10%'와 같이 비교하는 경우 문자로 형변환 됨)

* 그럼 여기서 질문....(DAT컬럼은 Data type)

{code:SQL}
SELECT *
FROM SAMPLET
WHERE DAT = '01-JAN-94'
{code}

h2. 나. 부정형비교

h4. 부정형을 긍정형으로 유도
* 개선 전
{code:SQL}
SELECT 'Not found'
FROM EMP
WHERE EMPNO <> 7369
{code}

* 개선 후
{code:SQL}
SELECT 'NOT FOUND'
FROM DUAL
WHERE NOT EXISTS
( SELECT 'X' FROM EMP
WHERE EMPNO = 7369 )
{code}
{code:SQL}
SELECT 'Not found'
FROM emp a
WHERE NOT EXISTS
(SELECT empno FROM emp b
WHERE b.empno = 7369
AND a.empno = b.empno)
{code}

* TAB1테이블의 'YYYYMM','COL1'가 각각 인덱스로 생성되어 있고 TAB2의 'YYYYMM','COL2'가 각각 인덱스로 생성되어 있다

* 개선 전
{code:SQL}
1)
SELECT *
FROM TAB1
WHERE YYYYMM = '199910'
AND NOT EXISTS ( SELECT *
FROM TAB2
WHERE COL2 = COL1
AND YYYYMM = '199910')
{code}
{code:SQL}
2)
SELECT *
FROM TAB1
WHERE YYYYMM ='199910'
AND COL1 NOT IN (SELECT COL2
FROM TAB2
WHERE YYYYMM = '199910')
{code}

* 개선 후
{code:SQL}
3)
SELECT *
FROM TAB1
WHERE (YYYYMM, COL1) IN (SELECT '199910', COL1
FROM TAB1
WHERE YYYYMM = '199910'
MINUS
SELECT '199910', COL2
FROM TAB2
WHERE YYYYMM = '199910')
{code}

* 1) 과 2) 에서의 sub쿼리는 나중에 수행되거나 check조건으로 수행된다.
* 3) 인 경우엔 서브쿼리에서 MINUS결과를 먼저 수행하고, 그 결과를 가지고 메인쿼리를 엑세스 한다.
* 각각 테이블에 'YYYYMM+COL1','YYYYMM+COL2'로 구성된 인덱스가 있다면 테이블을 엑세스 하지 않고 인덱스만 가지고 sort merge방식으로 서브쿼리를 먼저 수행한 후 처리된 서브쿼리의 결과로 메인쿼리의 조건으로 사용한다.
\\

\\

h2. 다. NULL을 사용한 비교


h4. (1) NULL컬럼의 적용
* 개선 전
{code:SQL}
SELECT *
FROM emp
WHERE ename IS NOT NULL
{code}
* 개선 후
{code:SQL}
SELECT *
FROM emp
WHERE ename > ''
{code}

* 개선 전
{code:SQL}
SELECT *
FROM emp
WHERE empno IS NOT NULL
{code}

* 개선 후
{code:SQL}
SELECT *
FROM emp
WHERE empno > 0
{code}

* 옵티마이져 모드가 Rule_based 이거나 Cost_based 모드에서 'FIRST_ROWS'로 설정되었을 때 가능하다.

{code:SQL}
SELECT ord_dept, ord_date, SUM(ord_qty), COUNT(*)
FROM?? order1t
WHERE? ord_no > 0
GROUP? BY ord_dept
???????? ,ord_date
ORDER? BY ord_date
{code}

* SUM, COUNT, MAX, MIN, AVG등의 그룹함수를 사용했거나, GROUP BY, ORDER BY, UNION, MINUS, INTERSECT등을 사용하면 전체범위를 인덱스를 경유함으로 인덱스를 사용하는 것이 훨씬 불리하다.
* 개선 전
{code:SQL}
SELECT *
FROM emp
WHERE ename IS NULL
{code}

* 개선 후
{code:SQL}
CREATE TABLE emp(
ename VARCHAR2(20) DEFAULT '00'
......
)
{code}

- 분포도가 양호할 경우에는 Default 사용이 유리, 그렇지 않을 경우에는 null 값을 가지고 있는 것이 유리하다.

h4. (2) NULL공포증의 해소

* NULL이란?

** NULL도 1, A와 같은 하나의 값이다.
** 어떤 값보다 크지도 않고 작지도 않다.'
** 그러므로 어떤 값과 비교 될 수도 없다.
** 즉 NULL과 연산 결과는 NULL이 된다.

{code:SQL}
SELECT ord_dept, SUM(ordqty), AVG(ordqty+asnqty), AVG(ordqty)
FROM ORDER
WHERE status < 'C'
GROUP BY ord_dept
{code}

- status가 null인 값은 작업대상에서 빠진다.
- AVG(ordqty+asnqty)는 ordqty나 asnqty 중 하나만 NULL을 가진다면 처리대상에서 제외된다.
- SUM(ordqty) 와 SUM(nvl(ordqty,0)) 은 동일하다.(결과는 동일하나 후자는 불필요한 연산을 수행하므로 불리함)
- AVG(ordqty+asnqty) 와 AVG(nvl(ordqty,0)+nvl(asnqty,0))는 서로 다르다.
- AVG(ordqty) 에서 ordqty에 NULL이 있을때와 없을때의 차이점?? {tip:title=NULL 공포증의 해소 방안}'확정은 되었으나 값이 없다' 경우에는 문자 타입일 때는 ' '(Space)나 기타 문자를 필요에 따라 지정하고, 숫자 타입일 때는 0 을 지정한다.
'미확정'인 값도 하나의 의미를 지닌 값이라고 보아야 한다는 것이 NULL 값이 만들어진 이유이다.
테이블을 생성시 DEFAULT 제약조건을 이용해서 기본값을 지정하여 처리 할 수도 있다.
NVL함수의 사용으로 인한 불필요한 연산 보다는 NULL값에 대한 일관성을 유지하는 것이 필요하다. {tip}

h4. 라. 옵티마이져에 의한 취사선택


h4. (1) 순위(Ranking)의 차이

{code:SQL}
SELECT ord_dept, ordqty
FROM ORDER1T
WHERE status = 'C'
AND ord_date like '9502%'
{code}

- RBO: 순위에 의해서 ord_date 인덱스는 무시되고, status인덱스를 사용.
- CBO: 분포도에 따라 옵티마이저가 선택.

{code:SQL}
SELECT ord_dept, ordqty
FROM ORDER1T
WHERE status = 'C'
AND ord_date = '950201'
{code}
- 각 컬럼이 별개의 인덱스로 생성이 되어 있다면 인덱스 머지를 일으킴

{code:SQL}
SELECT ord_dept, ordqty
FROM ORDER1T
WHERE ord_dept like '12%'
AND ord_date like '9502%'
{code}
- 두개 인덱스중 하나만 사용함.
- RBO: 나중에 생성된 컬럼 사용.
- CBO: 분포도에 따라 옵티마이저가 선택. {tip}범위 처리가 넓다고 할 수 있는 'LIKE', 'BETWEEN', '<', '>'등과 같이 사용될 경우 결코 인덱스 머지를 하지 않고 어느 하나의 인덱스만 사용하고 나머지는 포기한다. {tip}

h4. (2) 낮은 처리비용의 선택

{code:SQL}
SELECT *
FROM emp
WHERE ename > 'A'
{code}

- FIRST_ROWS 로 설정되었을 때 인덱스를 사용하지만 ALL_ROWS인 경우에는 전체 테이블을 스캔 함.

h4. (3) 힌트(Hint)에 의한 선택

* 옵티마이져가 액세스 경로를 결정할 때 옵티마이져에게 모든 것을 맡기지 않고 사용자가 원하는 보다 좋은 접근경로를 직접 선택해서 최적의 튜닝을 할 수 있도록 도와줌 {tip:title=힌트의 사용 방법}/*\+ \*/ : 힌트의 내용을 여러 라인에 걸쳐서 기술할 수 있음.
\--\+ : 오직 한 라인에만 기술해야 하며, 컬럼은 반드시 다음 라인에 기술해야 함. {tip}{tip:title=ORACLE_Hint 정리}[HINT_Dictionray.xls|http://wiki.gurubee.net/download/attachments/688163/HINT_Dictionray.xls?version=1] {tip}