Post List

2015년 1월 7일 수요일

대용량 데이터베이스 솔루션 1권 #09 OPTIMIER 와 INDEX의 조화






















- RANKING 의 차이 : EQUAL 이 LIKE, BETWEEN 보다 앞선다.

- INDEX MERGE 회피 : LIKE * LIKE 의 경우는 INDEX MERGE 가 훨씬 느림

- LOW COST의 선택

  Cost-Based Optimizer는 Column으 분포도를 가지고 있어서 그것을 활용할 수 있다.
  EMPNO > 10 의 조건을 만족하는 것이 대부분이라면 OPTIMIZER는 그냥 FULL-SCAN을 선택한다.

- HINT에 의한 선택 : 말이 되는 HINT라면 Optimizer가 활용 할 수도 있다.



  Cost-Based Optimizer는 통계정보들을 가지고 있다. (Empty Block, Chain Block의 분포, MIN, MAX, Row건수 등등...)
  하지만 Program에서 주로 사용하는 STATIC SQL에서는 WHERE DEPTNO = :v1 으로 변수를 사용하는데 이것을 보고   통계정보를 쓸수 있나 ? 절대로 없다.
  Cost-Based Optimizer가 제대로 활용되는 곳은 End-User의 SQL문에서만 활용이 가능하다.

  Cost-Based의 경우는 럭비공같다. 어디로 튈지 모른다.
  예술사진작가가 자동카메라랑 수동카메라 중 뭐를 쓸까 ?
  DB 전문가라면 Cost-based 보다는 Rule-based 를 선호한다.

어떨 때INDEX를 써야 할까 ?

- 6 Block 이상의 테이블에 적용
- Column 분포도가 10 ~ 15% 이내인 경우에 적용
- 분포도가 범위 이내라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 검토할 것
- 분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 적용
- 인덱스만을 사용하여 요구를 해겷고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음

 10 ~ 15% 이내라하더라도 3000건 이내가 아니라면 ? INDEX로는 답이 없다.
 CLUSTER 라면 또 모를까. 하지만 CLUSTER를 만들려면 기존의 INDEX를 전부 다 재편해야 한다.
 고성능의 CLUSTER를 사용하는게 기존에 어줍짢은 INDEX들이 깨작대면 더 느려질수도 있다.


위의 예제는 1만건의 ROW가 1000 BLOCK에 저장되어 있다.
INDEX를 활용하여 1000 ROW를 읽는데, 모두 다른 BLOCK이면 결국 RANDOM ACCESS I/O로 SINGLE-BLOCK을 읽어서 1000 BLOCK을 읽어야 한다.
FULL-SCAN의 경우는 그냥 무조건 전체 BLOCK을 읽는다. 1000 BLOCK을 EQUENCIAL-ACCESS I/O로 읽으며 MULTI-BLOCK을 한번에 읽는다. 통상적으로 RANDOM-ACCESS 하는 경우보다 SEQUENCIAL-ACCESS 하는게 10배가량 더 빠르다. 그래서 손익분기점에 10~15% 라는 말이 나오는 것이다.

CLUSTERING FACTOR : INDEX로 읽을 때 다음 DATA가 같은 BLOCK에 있는 정도























분포도 차이가 심하면 INDEX MERGE가 더 불리하다.

    FROM A, B
  WHERE A.key = B.key AND A.c1 = 'ABC' AND B.c2 = 100
 이라는 조건이 있고, A.c1, B.c2에 각각 INDEX가 있을 경우
 A.c1 = 'ABC' 인것이 58000건 있고,
 B.c2 = 100 인것이 12건 있고,
둘다 만족하는 조건이 3건 있는 경우

1. A가 Driving 하면 58000건을 INDEX-SCAN 하여 3건을 찾아야 한다. 몇시간이 걸릴 것이다.
2. B가 Driving 하면 12건에서 3건을 INDEX-SCAN하는건 순간적으로 될 것이다.












CUSTNO 가 엄청 똑똑해서 DN02인 경우가 엄청 적다면 CHILDATE에 대해서 하루를 하나 한달을 하나, 아에 FULL-SCAN을 하나 속도차이가 거의 없다.


STATUS = 90 인게 엄청 많다면 해당 INDEX를 안쓴느게 더 좋다.

INDEX MERGE 와 결합 INDEX의 비교


 INDEX를 따로 만든 경우는 LIKE가 들어간 경우 다른 INDEX를 사용하지만
 STATUS=90의 범위가 너무 넓어서 FULL-SCAN보다 더 느려졌다.

위의 예제에서 결합 INDEX를 아래와 같이 만들었다고 할때,

CREATE INDEX CH_DEPT_STATUS ON CHULGOT (CHULDEPT, STATUS)

SELECT CUSTNO, CHULDATE FROM CHULGOT A WHERE CHULDATE = '980120' ANS STATUS = '90'

위의 문장은 순간적으로 답이 나온다.
하지만 아래 문장은 답이 없다.

SELECT CUSTNO, CHULDATE FROM CHULGOT A WHERE CHULDATE LIKE '9801%' AND STATUS = '40'

왜일까 ? CHULDATE가 RANGE 범위이다. 980101 에서 STATUS=40 을 찾았다고 하더라도 계속해서 쭉~ 찾아야 한다. 왜냐면 LIKE 니깐. 위의 2개의 SQL문의 실행계획은 똑같지만, 실행속도차이는 엄청 차이가 난다.

빠르게 하는 방법이 없나 ? 있다. RANGE 에서 31개의 EQUAL로 바꾸면 위의 SQL문장 X 31번의 실행이 되어서 엄청 빨라진다.

CHULDATE LIKE '9801%' 를 아래와 같이 바꾸면 된다.
CHULDATE IN (980101, 980102, ... , 980131)

이렇게 하면 980101 AND STATUS = 40 , 980102 AND STATUS = 40, ... 이것들이 31번 실행된다.
OPTIMIZER는 AND 와 OR 조건들이 있을 때는 AND 조건들을 묶어서 그것들을 OR로 처리하려고 한다.  왜냐면 AND 할수록 범위가 좁아지는데, OR 할수록 범위가 넓어지기 때문이다.

근데 그럼 매번 할때 마다 IN ( ... ) 를 입력해야하나 ? 이거 너무한거 아니냐라고 생각할 수도 있다.
물론 편하게 하는 방법이 있다.

WHERE CHULDATE IN (SELECT YMD FROM COPY_YMD WHERE TMD LIKE '9801%')
와 같이 날짜를 별도 TABLE로 만들어 놓고 활용하면 된다.

EQUAL이 결합 INDEX에 미치는 영향


둘 다 EQUAL인 경우 그냥 한방에 끝 !


한 쪽만 EQUAL인 경우 앞에 COLUMN에 EQUAL인 경우와 뒤에꺼인 경우 속도차이가 엄청 난다.

결합 INDEX를 만들때 어느 COLUMN이 앞에 와야하나 ?
분포도가 좋은게 ? 아니다.
EQUAL 로 자주 나오는게 앞에 와야 한다.

IN을 이용한 ACCESS 효율향상


BETWEEN 은 선형으로 RANGE-SCAN 하지만 IN은 그냥 2개를 따로따로 뽑아낸다.

작가
이화식
출판
엔코아컨설팅
발매
1996.03.01
평점
블로거의 오늘의 책에 참여한 포스트 입니다

댓글 없음:

댓글 쓰기