꿈꾸는 개발자, DBA 커뮤니티 구루비

인덱스가 사용되지 않는 경우

인덱스컬럼의 변형(Suppressing)
부정형비교
NULL을 사용한 비교
옵티마이져에 의한 취사선택

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

(1) 외부적(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'

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

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

(2) 내부적 변형

JOIN시 데이터 타입의 통일


SELECT chr,num,var,dat
  FROM samplet
  WHERE chr = 10


SELECT chr,num,var,dat
  FROM samplet
  WHERE chr = '10'

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

나. 부정형비교

부정형을 긍정형으로 유도


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'
  FROM TAB2AND NOT EXISTS ( SELECT *
                             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'로 설정되었을 때 가능하다.
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이 있을때와 없을때의 차이점??

NULL 공포증의 해소 방안

'확정은 되었으나 값이 없다' 경우에는 문자 타입일 때는 ' '(Space)나 기타 문자를 필요에 따라 지정하고, 숫자 타입일 때는 0 을 지정한다.
'미확정'인 값도 하나의 의미를 지닌 값이라고 보아야 한다는 것이 NULL 값이 만들어진 이유이다.
테이블을 생성시 DEFAULT 제약조건을 이용해서 기본값을 지정하여 처리 할 수도 있다.
NVL함수의 사용으로 인한 불필요한 연산 보다는 NULL값에 대한 일관성을 유지하는 것이 필요하다.

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

(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: 분포도에 따라 옵티마이저가 선택.

범위 처리가 넓다고 할 수 있는 'LIKE', 'BETWEEN', '<', '>'등과 같이 사용될 경우 결코 인덱스 머지를 하지 않고 어느 하나의 인덱스만 사용하고 나머지는 포기한다.

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


SELECT *
  FROM emp
 WHERE ename > 'A'

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

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

옵티마이져가 액세스 경로를 결정할 때 옵티마이져에게 모든 것을 맡기지 않고 사용자가 원하는 보다 좋은 접근경로를 직접 선택해서 최적의 튜닝을 할 수 있도록 도와줌

힌트의 사용 방법

/*\+ \*/ : 힌트의 내용을 여러 라인에 걸쳐서 기술할 수 있음.
\--\+ : 오직 한 라인에만 기술해야 하며, 컬럼은 반드시 다음 라인에 기술해야 함.

ORACLE_Hint 정리