[펌] [오라클] 인덱스

 

인덱스는 "값 + rowid" 로 구성되어 있습니다.

동일한 값을 같은 경우 rowid순으로 소트 되어 있습니다.

때문에 같은 블럭에서 테이블 억세스가 이루어 집니다.

 

쿼리에서 인덱스를 사용하는 경우와 사용하지 않는 경우를 정리해 보면, 아래와 같습니다.

 

index 못쓰는 경우 : '%ABC%', '%ABC', <>, NULL, NOT NULL
index 쓰는 경우 : 'ABC%', <, >, between, =

-----------------------------------------------

 인덱스에 관련 하여 쿼리 만들때 유의 사항 입니다.

-----------------------------------------------

[1. 좌변을 가공 하지 마라]
예 1)
select *
  from dept
where substr(dname, 1, 3) = 'ABC'
테이블의 모든 내용을 읽어서 서브 스트링을 모두 돌린 다음 같은것 찾는다.

select *
  from dep
where dname like 'ABC%'
만약 index잡혀 있다면 index에서 ABC로 시작 하는 것 찾아서 그것만 테이블 억세스 한다.

예2)
where sal*12 = 2000만  <-- sal * 12를 모두 다한후 비교 한다.
where sal = 2000만/12  <-- 한번만 연산후 그 값인것 찾는다.

select * from emp
where TO_CHAR(HIREDATE, 'YYMMDD') = '940101'
select * from emp
where HIREDATE = TO_DATE('940101', YYMMDD)

select *
  from emp
where NVL(COMM, 0) < 100
좌변 가공 했으므로(함수사용) 인덱스 사용
COMM이 널이면 0으로 바꾼후 100보다 적은지 비교.
NULL은 답이 안 나오므로. 강제로 바꿔 준 것이다.

NULL때문에 디어 버린 사람:
A = (B+12) * C 
C가 널이면 A가 0원이 되어 버린다.
이때부터 자신의 프로그램엔 NVL안쓰면 마음이 안 놓인다.
DB설계자가 조금만 더 생각 해서 not null이나 default를 설정해 줬더라면,
개발자가 저런짓을 했겠는가?

SELECT *
  from emp
where empno between 100 and 200
    and NVL(JOB, 'X') = 'CLERK'

select *
  from emp
where empno between 100 and 200
    and job = 'CLERK'

SELECT *
  from emp
where DEPNO|JOB = '10SALESMAN'

SELECT *
  from emp
where depno = '10'
  and job = 'SALESMAN'

 

 

[2. null, not null 비교인 경우 인덱스 사용 안함]
select *
  from emp
where ename is not NULL

 

(bitmap index인 경우 인덱스 사가능)

 

select * from emp
where ename is not null
=> select * from emp
where ename > ''

select * from emp
where comm is not null
=> select * from emp
where comm > 0

select * from emp
where comm is null
null에 대한 업무 디파인이 확실해야 한다.
어느 집합에도 속하면 안되는 경우 null이 맞다. 정말 null이 필요한지 파악해야 한다.
null이 필요한 경우는 정말 극히 드물다.

null이란? 모르는 값을 표현 하고자 할때 null을 사용한다.
          모르는 값은 어떠한 연사을 해도 모르는 값이다.
          => 널공포증 발생.

 

[3. 옵티마이저가 취사 한다]
select *
  from emp
where job like 'AB%'
    and empno = '7890'

job와 empno가 모두 인덱스로 잡혀 있을 경우 옵티마이저가 like보다 =을 우선 하므로
둘다 사용 하는 것이 아니라, empno를 사용한다.

[4. 의도적으로 인덱스를 못쓰게 함]

select *
  from AAA
where custno = 'DN02'
  and status = 90

실행계획:
TABLE ACCESS BY ROWID AAA
 ANDEQAL
  INDEX RANGE SCAN AAA_STATUS
  INdEX RANGE SCAN AAA_CUSTNO
:위에서 밑으로 읽어 나가되 안에서 밖으로 실행 된다.

0.5초

둘다 인덱스 이므로 인덱스 머지를 한다.

두개의 인덱스를 봐서 서로 같은 rowid를 나온 경우가 만족하는 경우 이다.
status쪽인 1첫째 부터 시작 해서 rowid의 크기를 비교 하여 서로의 인덱스를 머지 합니다.

ANDEQAL : toad에서 인덱스 머지를 뜻하는 문자.

고객이 dn02는 20명 된다(레인지가 좁다). status가 90인 것은 60%이다(레인지가 넓다)

select *
  from AAA
where custno = 'DN02'
  and RTRIM(status) = '90'

실행계획
TABLE ACCESS BY ROWID AAA
  INdEX RANGE SCAN AAA_CUSTNO

0.03초

select *
  from AAA
where cust like 'DN%'
   and status like '9%'
값이 틀려 지면 더 적은 rowid가 있을 수 있으므로 인덱스 머지 방식은 절대로 사용할 수 없다.
둘중 하나의 인덱스를 선정 합니다. 랭킹을 비교... 둘다 like이므로 만들어진 시점을
비교하여 최근에 만들어진 인덱스를 사용합니다. 전부다 비기면 나중에 쓰여진 인덱스를 사용.
status인덱스를 사용 했다면.

200초 걸림

select *
  from AAA
where cust like 'DN%'
   and RTRIM(status) like '9%'
일부러 한쪽의 인덱스는 안쓰게 만들었다.

0.1초 걸림

 

실행 계획에 확신이 안간다면 실행 계획을 바로 잡아줘야 한다.
오라클은 사람의 지능을 절대로 따라 올 수 없다.

and X.SALEDEPT = '710'
and Y.SALEDATE LIKE '9411%'
SALEDEPT가 = 이므로 saledept를 사용한다.

and RTRIM(X.SALEDEPT) = '710'
and Y.SALEDATE LIKE '9411%'
saledate 인덱스를 사용하게 해 줌.

X.ORDNO = Y.ORDNO
and X.ORDDATE like'9411%'
and Y.ORDDEPT = '710'
order by X.ORDDATE
order by가 없으면 orddept인덱스를 탔으므로 주문날짜 순으로 소트 되지 않는다.
때문에 order by를 주었다. 엄청나게 오래 걸림~

RTRM(X.ORDNO) = Y.ORDNO
and X.ORDDATE like'9411%'
and Y.ORDDEPT = '710'
조인 문장이면 연결 고리를 먼저 본다. 연결 고리에 이상이 있으면,
그 이상이 있는 쪽이 먼저 드라이빙 됩니다.
Y쪽이 먼저 드라이빙 되면 X.ORDNO 인덱스를 못 타므로 효율이 떨어 진다.
때문에 다리가 불어진 X쪽이 먼저 드라이빙 되는 것이다.
그런데 보니까. X쪽에 ORDDATE에 인덱스가 있다. 와~ 잘됐다.
ORDDATE인덱스를 타서 읽어 온다.

 

[5. 데이터 형에 유의]

where xyz = :v1
xyz에 인덱스가 있는데 full table 스캔을 하는 경우가 있다.
이런 경우 데이터 형이 안 맞을 경우이다. char인데 int...

select * from samplet
where chr = 10

위의 문장은 아래와 같다.
=> select * from samplet
where to_number(chr) = 10

이러한 이유로 인덱스를 쓰지 않는다.

아래의 예는 인덱스를 쓴다.
select * from samplet
where dat = '01-jan-04'
=> select * from samplet
where dat = to_date('01-jan-04')

NLS_DATE_FORMAT = 'dd-mm-yy' 로 되어 있다.
때문에 to_date로 바꾸어 준다. 'yyyymmdd'로 하면 편하다.

 

 

[6. not 사용 피해라]

select 'not found' into :col1
  from emp
where empno = '1234'

=>
select 'not found' into : col1
  where dual
where not exist (select * from emp
                 where empno = '1234')
위의 sql은 index 사용 한다.

 

select *
from emp
where ename like '천%'
  and job <> 'sales'

=>
select *
from emp a
where a.ename like '천%'
  and not exist (select * from emp b
                 where a.empno = b.empno
                  and b.job='sales')
또는
=>
select * from emp
where ename life '천%'
 MINUS (차집합)
select * from emp b
where b.JOB = 'SALES'

 

[7. NULL에 대하여]
1. 미확정 값을 표현 할 경우.
2. 특정 값의 분포가 몰리는 경우
예) 결제 정보의 경우
결제:1, 미결제:2 인 경우 미결제가 대부분이며, 미결제를 검색 하는 경우가 많다.
이럴때 결제를 NULL로 해주고, 미결제를 1로 하면 인덱스의 효율이 높아 진다.
3. 조인의 구성 요소인 경우 not null로
4. 입력 조건 값으로 자주 나오는 경우 not null로 한다.