시소당

1.1 인덱스의 구조

<대용량데이터베이스 솔루션1 5page 그림참고>

1. B*Tree 방식으로 조건을 만족하는 첫 번째 인덱스 ROW를 찾는다.
2. 조건에 해당하는 처리가 끝날 때 까지 차례대로 다음 ROW를 스캔 한다.
3. 인덱스 ROW에 있는 ROWID 정보를 이용하여 테이블에 있는 실제 ROW를 랜덤하게 액세스 한다.
4. 결국 액세스되는 테이블 ROW의 순서는 인덱스 ROW의 순서와 일치한다.
5. 그러므로 인덱스를 이용하여 처리함으로써 정렬작업 없이 동일한 결과를 추출할 수 있다.

1.2 인덱스의 적용원칙

인덱스가 사용되지 않는 경우 인덱스컬럼의 변형(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 정리 ORACLE Hint 정리 HINT_Dictionray.xls