양호한 엑세스 경로를 보장받기 위한 조건

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

인덱스를 사용하지 않는 경우

• 인덱스 컬럼이 비교되기 전에 변형이 일어날 경우.

 SELECT deptno FROM dept WHERE SUBSTR(dname,1,3) = 'ABC'

• 부정형(NOT, <>)으로 조건을 기술한 경우.

 SELECT ename FROM emp WHERE job <> 'SALES' 

• 인덱스 컬럼이 NULL로 비교되는 경우(NULL, NOT NULL의 사용)

 SELECT * FROM emp WHERE ename IS NOT NULL 

• 옵티마이져가 특정 인덱스의 사용을 취사 선택할 경우 사용되지 않을 수 있음
• 조건의 순위(Ranking), 사용자의 힌트, 통계정보에 의거하여 산출된 액세스비용 등등..

 SELECT * FROM emp WHERE job LIKE 'AB%' AND empno = '7890 

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

외부적(External) 변형

인덱스의 특징을 역으로 이용하여 수행속도를 향상.

'CUSTNO'와 'STATUS'는 각각 인덱스가 생성되어 있고 'STATUS'가 '90'인 경우에는 분포도가 넓다고 가정하면.

• 개선 전

SELECT custno, chuldate
  FROM chulgot
 WHERE custno = 'DN01'
   AND status = '90'

• 개선 후

SELECT custno, chuldate
  FROM chulgot
 WHERE custno = 'DN01'
   AND RTRIM(status) = '90'

ORD_DATE LIKE '9502%'를 만족하는 로우수가 ORD_DEPT = '12345'를 만족하는 로우 수보다 적다고 가정 했을 경우..

• 개선 전

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

• 개선 후

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

sale_dept 가 95년도인 데이터가 아주 많고, sale_dept로 생성된 인덱스가 있다고 가정 했을 경우.

• 개선 전

SELECT sal_no, sale_date, sale_dept, saleqty
  FROM mechult
 WHERE sale_date LIKE '95%'
 ORDER BY sale_dept

• 개선 후

SELECT sal_no, sale_date, sale_dept, saleqty
  FROM mechult
 WHERE RTRIM(sale_date) LIKE '95%'
   AND sale_dept > ''

내부적 변형

JOIN시 데이터 타입의 통일

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

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

SELECT *
FROM SAMPLET
WHERE  DAT =  '01-JAN-94'

나. 부정형비교

부정형을 긍정형으로 유도

• 개선 전

SELECT 'Not found'
  FROM EMP
 WHERE EMPNO <> 7369

• 개선 후

SELECT 'NOT FOUND'
FROM DUAL
WHERE NOT EXISTS
                ( SELECT 'X'   FROM EMP
                   WHERE EMPNO = 7369 )

SELECT 'Not found'
  FROM emp a
 WHERE NOT EXISTS
                 (SELECT empno  FROM emp b
                   WHERE b.empno = 7369
                     AND a.empno = b.empno)

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

• 개선 전

1)
SELECT *
  FROM TAB1
 WHERE YYYYMM = '199910'
 AND NOT EXISTS ( SELECT *
                            FROM TAB2
                             WHERE COL2 = COL1
                               AND YYYYMM = '199910')

2)
SELECT *
  FROM TAB1
 WHERE YYYYMM ='199910'
   AND COL1 NOT IN (SELECT COL2
                      FROM TAB2
                     WHERE YYYYMM = '199910')

• 개선 후

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')

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

다. NULL을 사용한 비교

(1) NULL컬럼의 적용

• 개선 전

SELECT *
  FROM emp
 WHERE ename IS NOT NULL

• 개선 후

SELECT *
  FROM emp
 WHERE ename > ''

• 개선 전

SELECT *
  FROM emp
 WHERE empno IS NOT NULL

• 개선 후

SELECT *
  FROM emp
 WHERE empno > 0

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

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

• SUM, COUNT, MAX, MIN, AVG등의 그룹함수를 사용했거나, GROUP BY, ORDER BY, UNION, MINUS, INTERSECT등을 사용하면 전체범위를 인덱스를 경유함으로 인덱스를 사용하는 것이 훨씬 불리하다.
• 개선 전

SELECT *
  FROM emp
 WHERE ename IS NULL

• 개선 후

CREATE TABLE emp(
   ename VARCHAR2(20) DEFAULT '00'
   ......
)

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

(2) NULL공포증의 해소

• NULL이란?

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

SELECT ord_dept, SUM(ordqty), AVG(ordqty+asnqty), AVG(ordqty)
  FROM ORDER
 WHERE status < 'C'
 GROUP BY ord_dept

• 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}

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

(1) 순위(Ranking)의 차이

SELECT ord_dept, ordqty
  FROM ORDER1T
 WHERE status = 'C'
   AND ord_date like '9502%'

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

SELECT ord_dept, ordqty
  FROM ORDER1T
 WHERE status = 'C'
   AND ord_date = '950201'

• 각 컬럼이 별개의 인덱스로 생성이 되어 있다면 인덱스 머지를 일으킴

SELECT ord_dept, ordqty
  FROM ORDER1T
 WHERE ord_dept like '12%'
   AND ord_date like '9502%'

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

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

SELECT *
  FROM emp
 WHERE ename > 'A'

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

(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}