|
<대용량데이터베이스 솔루션1 5page 그림참고>
- B*Tree 방식으로 조건을 만족하는 첫 번째 인덱스 ROW를 찾는다.
- 조건에 해당하는 처리가 끝날 때 까지 차례대로 다음 ROW를 스캔 한다.
- 인덱스 ROW에 있는 ROWID 정보를 이용하여 테이블에 있는 실제 ROW를 랜덤하게 액세스 한다.
- 결국 액세스되는 테이블 ROW의 순서는 인덱스 ROW의 순서와 일치한다.
- 그러므로 인덱스를 이용하여 처리함으로써 정렬작업 없이 동일한 결과를 추출할 수 있다.
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) 내부적 변형
나. 부정형비교
- 부정형을 긍정형으로 유도
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도 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)에 의한 선택
- 옵티마이져가 액세스 경로를 결정할 때 옵티마이져에게 모든 것을 맡기지 않고 사용자가 원하는 보다 좋은 접근경로를 직접 선택해서 최적의 튜닝을 할 수 있도록 도와줌
|
힌트의 사용 방법
/*+ */ : 힌트의 내용을 여러 라인에 걸쳐서 기술할 수 있음.
--+ : 오직 한 라인에만 기술해야 하며, 컬럼은 반드시 다음 라인에 기술해야 함. |
|
