반응형
출처: http://www.devpia.com/DevStudy/Lecture/OffLineDetail.aspx?nSemiID=1431&lectype=evt

데브피아에 DB 튜닝관련 컬럼 연재가 2회차가 올라왔습니다. ^^

 지난회에는 인덱스를 생성했으나 컬럼의 가공, 내부적 변형, null과의 비교, 부정형 조건등으로 인하여 인덱스를 사용하지 못하는 경우를 보았다.
그럼 과연 인덱스를 타기만 하면 무조건 빠를까?
불행하게도 그렇지 않다. 대부분의 경우는 빠르겠지만 경우에 따라서는 인덱스를 타기 때문에 느려지는 경우가 많이 발생한다.

EMPLOYEE에 성별 컬럼을 추가하고 절반정도 되게 남성과 여성을아래와 같은 분포도로 넣었다.

SELECT GENDER , COUNT(*) CNT, ROUND(COUNT(*) / 15132,3)*100 RATIO FROM EMPLOYEES
GROUP BY GENDER;

G
----
F
M
CNT
--------
7590
7542
RATIO
---------
50.2
49.8
 
그리고 아래와 같은 INDEX를 생성하였다.
CREATE INDEX IDX_GENDER ON EMPLOYEES(GENDER);
그러면 이제 2개의 SQL의 수행 결과를 보자.
첫번째 경우는 INDEX를 탄경우다.
아래와 같이 HINT를 주어서 OPTIMIZER의 PLAN을 고정하였다.
/*+ INDEX(E IDX_GENDER) */ 는 E라는 별명의 테이블에 IDX_GENDER이라는 INDEX를 이용하여 테이블에 데이터를 가져오라는 뜻이다.
SELECT /*+ INDEX( E IDX_GENDER) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE GENDER = 'M'
GROUP BY GENDER

Call
----------
Parse
Execute
Fetch
----------
Total

Count
----------
1
1
2
----------
4
CPU Time
----------
0.000
0.000
0.030
----------
0.030
Elapsed Time
-------------
0.000
0.000
0.026
-------------
0.026
Disk
----------
0
0
0
----------
0
Query
----------
0
0
127
----------
127
Current
----------
0
0
0
----------
0
Rows
----------
0
0
1
----------
1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user: SCOTT (ID=54)

Rows
----------
0
1
7542
7542
Row Source Operation
---------------------------------------------------
STATEMENT
SORT GROUP BY NOSORT (cr=127 pr=0 pw=0 time=25567 us)
TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=127 pr=0 pw=0 time=15176 us)
INDEX RANGE SCAN IDX_GENDER (cr=16 pr=0 pw=0 time=61 us)OF IDX_GENDER (NONUNIQUE)
두번째 경우는 INDEX를 타지 않은 경우다.
아래와 같이 HINT를 주어서 OPTIMIZER의 PLAN을 고정하였다.
/*+ FULL(E) */ 는 E라는 별명의 테이블을 할 때 테이블 전체를 다 읽어서 처리(FULL TABLE SCAN)하라는 뜻이다.
SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE GENDER = 'M'
GROUP BY GENDER

Call
----------
Parse
Execute
Fetch
----------
Total

Count
----------
1
1
2
----------
4
CPU Time
----------
0.000
0.000
0.010
----------
0.010
Elapsed Time
-------------
0.000
0.000
0.014
-------------
0.015
Disk
----------
0
0
0
----------
0
Query
----------
0
0
115
----------
115
Current
----------
0
0
0
----------
0
Rows
----------
0
0
1
----------
1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user: SCOTT (ID=54)

Rows
----------
0
1
7542
Row Source Operation
---------------------------------------------------
STATEMENT
SORT GROUP BY NOSORT (cr=115 pr=0 pw=0 time=14410 us)
TABLE ACCESS FULL EMPLOYEES (cr=115 pr=0 pw=0 time=181 us)
인덱스를 탄 경우는 0.03초가 걸렸고 인덱스를 타지 않은경우는 0.01초가 걸렸다.
인덱스를 타서 3배나 더 느려졌다!. 이것이 가능한가? 그러면 왜 인덱스를 탔는데도 시간이 더 걸리는 것인가?
이유는 Disk io에 있다. 일반적으로 Full table scan을 할때는 한번에 1개의 block씩 i/o를 하지 않고 muti block를 한번에 요구한다. 그 이유는 읽을 양이 많다고 미리 가정하기 때문이다. 따라서 Oracle의 경우 db_file_multiblock_read_count라는 파라미터가 있고 일반적으로 8또는 16을 설정한다. 만약 16이라면 한번 I/O에 16개의 BLOCK을 읽어오게 되는것이다.
따라서 EMPLOYEE 115BLOCK을 한번에 16개씩 읽으면 약 8번의 IO 요청으로 완료가 된다.
그러나 Index를 사용할 경우 index를 사용하면 기본적으로 대량의 io가 발생할 것이라고 가정하지 않기 때문에 1개의 block씩 i/o를 하게 된다.
따라서 16개의 index block과 115개의 block의 물리적 i/o가 발생한다 16+115를 하면 총 131번의 물리적 io가 발생하게 되는것이다. 논리적으로는 인덱스 1개보고 테이블 1개 블락을 읽고를 7542번+1번을 하게되는 것이다. 마지막 1번은 다음에 더 이상 ‘M’이 없는지 확인하기위해서 1번 더 읽는다. 어째든 인덱스를 사용되는 것이 더욱 느리다는 것이다. 현지 EMPLOYEES 테이블은 15132건이다. 만약 이 데이터가 많아진다면 차이는 점점 더 많이 날것이다.

그러면 어느정도은 INDEX를 타고 어느 정도는 Full Table Scan이 오히려 더 좋은가?
시스템의 성능또는 데이터의 양에 따라 차이가 조금씩있으나 일반적인 기준은 있다.
아래 TYPE이라는 컬럼에 A-F까지 값을 가지고 있으며 가각 49.8%부터 0.9%까지의 분포를 가지는 값들을 가지고 있다.
SELECT TYPE , COUNT(*) CNT, ROUND(COUNT(*) / 15132,3)*100 RATIO FROM EMPLOYEES
GROUP BY TYPE;

T
--------
A
B
C
D
E
F
CNT
--------
7542
4533
1515
799
603
140
RATIO
---------
49.8
30
10
5.3
4
0.9
아래는 예제로 사용되었던 SQL과 그에 따른 응답시간을 비교한 표이다.

FTS(Full Table Scan) SQL
SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
GROUP BY GENDER;

Rows
--------
0
1
140
Row Source Operation
---------------------------------------------------
STATEMENT
1 HASH GROUP BY (cr=115 pr=0 pw=0 time=6925 us)
140 TABLE ACCESS FULL EMPLOYEES (cr=115 pr=0 pw=0 time=10323 us)
INDEX(Index Scan) SQL
SELECT /*+ INDEX(E IDX_TYPE) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
GROUP BY GENDER;

Rows
--------
0
1
140
140
Row Source Operation
---------------------------------------------------
STATEMENT
HASH GROUP BY (cr=87 pr=0 pw=0 time=3227 us) 140
TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=87 pr=0 pw=0 time=2956 us)
INDEX RANGE SCAN IDX_TYPE (cr=2 pr=0 pw=0 time=539 us)OF IDX_TYPE (NONUNIQUE)
 
결과를 보자 약 15132건의 테이블을 ACCESS하는데 10%이상이 되는 경우는 FTS이 더빠르고 10%이하인 경우는 INDEX를 타는 경우가 더 빠르다.
10% 미만일때는 INDEX를 타고 10%가 넘으면 인덱스를 안타게 할수 있는가?
결론적으로 가능하다.
아래 SQL을 보자 2개의 SQL을 UNION ALL로 결합하고 비교조건을(굵은색)을 줌으로서 논리적 비교를 통해서 실제로 FTS의 조건은 타지 않고 INDEX쪽만 수행하도록 하였다.
아래 수행결과를 보면 INDEX를 타는 곳에서만 ROWS가 나온 것을 알수있다.
SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
   AND TYPE IN ( 'A','B','C')
GROUP BY GENDER
UNION ALL
SELECT /*+ INDEX(E IDX_TYPE) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
   AND TYPE IN ( 'D','E','F')
GROUP BY GENDER;

Rows
--------
0
1
0
0
0
1
140
140
Row Source Operation
---------------------------------------------------
STATEMENT
UNION-ALL (cr=87 pr=0 pw=0 time=2743 us)
HASH GROUP BY (cr=0 pr=0 pw=0 time=235 us)
FILTER (cr=0 pr=0 pw=0 time=8 us)
TABLE ACCESS FULL EMPLOYEES (cr=0 pr=0 pw=0 time=0 us)
HASH GROUP BY (cr=87 pr=0 pw=0 time=2477 us)
TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=87 pr=0 pw=0 time=2328 us)
INDEX RANGE SCAN IDX_TYPE (cr=2 pr=0 pw=0 time=205 us)OF IDX_TYPE (NONUNIQUE)
그렇나 이렇게 프로그램을 한다면 프로그램이 힘들어 질것이다. 따라서 현재 Optimizer들은 실제 값에 따라서 FTS이 유리한지 아니면 INDEX SCAN이 유지한지 값을 보고 PLAN이 바뀌도록 되어있다. 물론 이를 위해서는 컬럼에 대한 분포도 정보를 DB가 가지고 있어야 한다. 이는 ANALYZER를 통해서 DB가 취득하게 된다.

그럼 이제 간단하다 10%이상에 데이터를 INDEX를 타면 속도가 오히려 느려지므로 10%이하의 데이터를 찾고자 할 때만 INDEX를 생성하면 간단하게 해결될것이다!
그러나 과연 그럴까?
INDEX를 생성하면 일반적으로 SELECT의 속도는 향상을 보지만 반대로 INSERT,UPDATE,DELETE는 저하되게 된다.
위에 도표를 보면 INDEX의 숫자가 증가함에 따라서 속도가 느려지는 것을 알수 있다. 즉, 인덱스의 생성으로 SELECT는 빨라질수도 있고 느려질수도 있다. 그러나 DML(INSERT,UPDATE,DELETE)는 항상 느려진다. 따라서 INDEX를 무작정 다는 것은 DML 성능을 느리게 한다.
그럼 어떤 기준으로 인덱스를 생성할지 말지를 결정할 것인가?
아래 2가지 시간을 고령하자.
이익시간 = INDEX생성으로 빨라진시간 * 수행 QUERY수
비용시간 = INDEX생성으로 느려진 INSERT시간 * INSERT수행횟수
                + INDEX생성으로 느려진 UPDATE시간 * UPDATE수행횟수
                + INDEX생성으로 느려진 DELETE시간 * DELETE수행횟수

이익시간이 비용시간 보다 크다면 인덱스를 생성하는 것이 좋을 것이다. 반대로 이익시간 < 비용시간 보다 작다면 인덱스를 만드는 것이 손해보는 경우다.
이런 경우라면 인덱스를 만들면 안되는 것이 유리하다 할수 있다. 그러나 반드시 그런 것은 아니다. 그것은 수행 시간을 고려해야한다. 낮에일반적으로 QUERY가 빠르게 수행되고 주로 밤에 BATCH에서 DML이 수행되고 있다고 가정할 때 DML이 더 느려지는 것이 그렇게 문제가 되지 않는다면 INDEX를 생성할 수도 있는것이다. 어디까지나 Application 사용의 관점에서 효율적인 것을 찾는 것이 중요하다.
인덱스를 사용하여 손해보는 경우는 아래와 같다.

  • 같은 값이 많은 컬럼
    • INDEX를 타면 10%이상 선택하는 경우
    • 예) 남녀성별등..
  • 조회보다 DML의 부담이 큰 경우
    • 이익시간 < 비용시간 경우
    • 그러나 이때도 사용환경을 고려하여 인덱스를 생성할 수 있다.
  • 데이터가 적은 테이블
    • 일반적으로 db_file_multiblock_read_count보다 적은 수의 BLOCK을 가진테이블은 INDEX를 타지 않는 것이 빠르다.
    • 그러나 integrity를 위해서 PK와 FK는 달아야 한다.

이번 회에는 INDEX를 타서 오히려 손해를 보는 경우와 그를 방지하는 방법을 보았다.
인덱스를 생성한다고 인덱스를 반드시 타는 것도 아니며 또한 인덱스를 탄다고 반드시 빠른 것도 아니다. 따라서 INDEX의 생성과사용 전략은 그렇게 쉬운 문제가 아니다. 빠른 시스템을 위해서는 고려할 점이 많다는 것이다. 물론 필자가 다룬 것은 테이블중에 일반테이블과 일반 INDEX에 대해서만 다루었기때문에 PARTITION이나 BITMAP같은 다른 구조의 인덱스에서는 다른 특성을 가진다. 그러나 이러한 것은 대용량이나 DW의 특수한 용도에 사용되므로 대부분의 경우에는 고려하지 않아도 크게 문제되지 않을 것이다.

못조록 필자의 글이 독자들에게 도움이되는 길이기를 바라면서 이글을 마무리하고자 한다.
마지막으로 당부 드리고 싶은 말은 SQL을 작성하시고 항상 PLAN을 확인하시기 바랍니다.
PLAN에 익숙해지고 OPTIMIZER를 이해할 때 비로소 OPTIMIZER가 여러분의 심부름꾼이 될수 있기때문이다.



반응형
출처: http://www.devpia.com/DevStudy/Lecture/OffLineDetail.aspx?nSemiID=1429&lectype=evt

데브피아에 DB 튜닝관련 컬럼 연재가 올라왔네요. 내용이 괜찮아서 퍼왔습니다.

  필자가 처음에 SQL을 배울 때 SQL이 상당히 이상했다. 원하는 것만 요구할 뿐 어떻게 가져오라는 정보가 SQL에는 없었기 때문이다. FILE레벨의 I/O까지 코딩에 익숙한 필자에게 절차가 없다는 것이 오희려 더 이상했던 것이다.
물론 상세한 과정이 필요하지 않으므로 편리하고 좋았다 그러나 어떻게 가져오는지는 알지못하고 단지 사용할 뿐이었다.
그러나 SQL이 PLAN이라는 실행 계획을 만들고 그에 따라 가져오게 된다는 사실은 안것은 한참 뒤에 일이었다.
결국은 내가 하지않은 일을 Optimizer라는 프로그램이 대신 해주고 있는 것이 아닌가? 그래서 정말 고마운 놈이라고 생각했었다. 그러나 밑는 도끼에 발등을 찍힌다는 말이 있지 않은가?
Plan에 index를 달아주어도 Index를 사용하지 않고 full table scan만 하고 있으니 당체 속도가 나지를 않았다.
이래저래 해서 나중에 알게되었지만 결국 컬럼의 변형을 가하면 index를 사용하지 못한다는 것이다. 우리가 직접 사용하지는 않지만 결국 우리가 SQL을 사용한다는 것은 Optimizer라는 놈에게 SQL의 수행을 부탁하는 것이다. 따라서 우리가 Optimizer에 대해서 잘 안다면 SQL을 좀더 효율적으로 수행하도록 할 수 있지 않은가!
그러면 인덱스를 달았을 때 Optimizer가 index를 사용하지 못하는 경우를 통해서 우리가 애써(?)생성한 인덱시가 무용지물이 되지 않도록 해보자.
아래예제에 사용할 TABLE LAYOUT이다.
EMPLOYEES
---------
Rows=15,132
Empty Blocks=7
Chain Count=0
Avg Space Freelist Blocks=0
Sample Size=15,132
Partitioned=NO

Blocks=121
Avg Space=885
Avg Row Length=51
Freelist Blocks=0
Last Analyze=2009/05/04
Column Name
---------------
EMP_ID
MGR_ID
LAST_NAME
FIRST_NAME
HIREDATE
JOB
SALARY

Nullable
-----------------


NOT NULL
Column Type
-----------------
VARCHAR2(40)
VARCHAR2(40)
VARCHAR2(24)
VARCHAR2(14)
DATE
VARCHAR2(24)
NUMBER(7,2)
Distinct
-----------------
15,132
679
9,443
3,579
3,903
53
3,267
Buckets
------------------
75
75
75
75
75
53
75
INDEX
--------------------------------------------------------------------------------------
IDX_GENDER : GENDER
      Type=NORMAL, Uniq=No, Distinct=2, Rows=15,132, Last Analyze=2009/05/04
IDX_HIREDAT : HIREDATE
      Type=NORMAL, Uniq=No, Distinct=3,903, Rows=15,132, Last Analyze=2009/05/04
IDX_JOB : JOB
      Type=NORMAL, Uniq=No, Distinct=53, Rows=15,129, Last Analyze=2009/05/04
IDX_SALARY : SALARY
      Type=NORMAL, Uniq=No, Distinct=3,267, Rows=15,132, Last Analyze=2009/05/04
IDX_TYPE2 : TYPE
      Type=NORMAL, Uniq=No, Distinct=6, Rows=15,132, Last Analyze=2009/05/04
PK_EMP_ID : EMP_ID
      Type=NORMAL, Uniq=No, Distinct=15,132, Rows=15,132, Last Analyze=2009/05/04
필자가 여러군데 튜닝을 하면서 가장 많이 본것중에 하나는 INDEX를 달았으나 쓰지 못하게 되는 경우이다. 대표적인 경우가 아래와 같이 날짜타입(HIREDATE)에 TO_CHAR를 씌운 경우이다.
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
물론 INDEX는 아래와 같이 생성되어있다.
CREATE INDEX IDX_HIREDATE ON EMPLOYEES(HIREDATE);
우리가 원하는 것은 INDEX를 타고 테이블을 가져오기를 바란것이었다.

그러나 실제 PLAN은 아래와 같이 나온다.
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28 Card=151 Bytes=3K)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=28 Card=151 Bytes=3K)
TABLE ACCESS (FULL) 이란 뜻은 INDEX를 타지 않고 테이블을 처음부터 끝까지 읽어서 찾는다는 뜻이다. 한마디로 10건이며 10건읽고 100만건이면 100만건을 다 읽어야 결과가 나온다는 말이다.

OPEN시에는 빠르던 시스템이 시간이 지날수록 느려지는 결정적인 역할을 하는 것이 바로 위와 같은 경우이다. 그럼 어떻게 해야 제대로 인덱스를 사용할 수 있을가?
일단 간단히 SQL의 수정으로 해결할수 있다. HIREDATE는 날짜 타입이다.
따라서 인덱스를 HIREDATE로 했을 때 인덱스를 타기위해서는 INDEX를 생성한것에 변형을 주어서는 안된다.
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE HIREDATE = TO_DATE('19980518')
따라서 간단하게 위와 같이 고치면 INDEX를 사용하게된다.
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=4 Bytes=92)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=4 Bytes=92)
2 1 INDEX (RANGE SCAN) OF 'IDX_HIREDATE' (INDEX) (Cost=1 Card=4)
물론 결과도 빠르게 나온다 그러나 중요한 점이 있다 결과가 같을까?
운이 좋으면 결과가 같을 것이고 대부분의 경우는 결과가 틀리다.
왜 그럴까?
날짜 타입은 날짜와 시분초의 정보도 가지고 있다. 따라서 TO_DATE(‘19980518’)라는 말은 정확히 1998년5월18일 0시0분0초라는 뜻이다. 그래서 우리가 원하는 1998년5월18일자와는 차이가 있다.
따라서 1998년5월18일 0시0분1초 ~ 23시59분59초까지의 데이터는 나오지 않게되는것이다.
이것은 튜닝할 때 유의할 점이다. 결과를 같게 유지해야하는것이다. 이 상황을 알고있다면 방법은 간단하다.
아래아 같이 고치면 빠른시간에 원하는 결과를 얻을 수 있을 것이다.
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE HIREDATE BETWEEN TO_DATE('19980518'||'00:00:00','YYYYMMDD HH24:MI:SS')
AND TO_DATE('19980518'||'23:59:59','YYYYMMDD HH24:MI:SS')
비슷하지만 함수의한 변형이 아닌 간단한 연산에의한 변형의 경우도 마찬가지이다.
$1000의 인센티브를 더주면 $10000이 넘는 사람을 찾는 SQL을 만들어보자.
아마 아래와 같을 것이다.
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE SALARY + 1000 > 100000;
물론 INDEX는 아래와 같이 만들었다.
CREATE INDEX IDX_SALARY ON EMPLOYEES(SALARY);
그러나 PLAN을 보자
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29 Card=757 Bytes=13K)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=29 Card=757 Bytes=13K)
인데스를 타지 못한다. 왜일까. 간단한 연산이지만 SALARY컬럼에 가공을 했기 때문에 OPTIMIZER는 인덱스를 타는 것을 포기해버린다.
따라서 우리가 기초적인 수학 실력을 발휘해서 이항을 해준다면 아래와 같은 조건이 될것이다.
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE SALARY > 100000 - 1000;
이경우에 PLAN을 보자.
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=17)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=1 Bytes=17)
2 1 INDEX (RANGE SCAN) OF 'IDX_SALARY' (INDEX) (Cost=2 Card=1)
재미 있게도 이번에 제대로 된 인덱스를 탄다. Optimizer가 바보 같다는 생각이 들지 않는가?
물론 바보같다. 그러나 OPTIMIZER나름대로 깊은 고민이 있다. 아주 잛은 시간내에 OPTIMIZER는 많은 경우의 수를 타진해야한다. 따라서 이항연산과 같은 것 까지 검토하면 너무 많은 시간을 소모하게 된다 따라서 그런부분은 포기한것이다.

또다른 경우중에 하나가 DB의 내부적인 변형이다. 이는 개발자가 의도하지 않게 문제를 야기하는 경우이다.
여기 PK 조건으로 검색하는 SQL이 있다.
SELECT LAST_NAME,FIRST_NAME
FROM EMPLOYEES
WHERE EMP_ID = 200383;
그러나 PLAN은 아래와 같이 나왔다.
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29 Card=1 Bytes=19)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=29 Card=1 Bytes=19)
분명히 아래와 같은 INDEX를 생성하였다.
CREATE INDEX PK_EMP_ID ON EMPLOYEES(EMP_ID);
왜 인덱스를 안타는 것일까?
그 이유은 OPTIMIZER의 내부 변형 규칙에 있다.
일반적으로 비교를 하려면 두개의 데이터 형이 같아야 한다.
그런데 EMP_ID는 VARCHAR2(40)이다 그리고 비교하려는 것은 200383이라는 숫자이다.
따라서 숫자와 문자는 비교할수 없기 때문에 내부적으로 변형이 이루어진다.
문자보다 숫자가 우선순위가 높아서 문자와 숫자를 비교하게되면 문자쪽이 숫자로 변형되어 비교하게 되는 것이다.
따라서 위의 SQL은 OPTIMIZER는 아래와 같은 SQL로 수행하게된다.
EMP_ID를 TO_NUMBER(EMP_ID) = 2000393과 같이 처리하게 된다.
SELECT LAST_NAME,FIRST_NAME
FROM EMPLOYEES
WHERE TO_NUMBER(EMP_ID) = 200383;
이는 처음 예제에서 날짜 컬럼에 TO_CHAR를 씌원것과 같은 효과이다. 따라서 이문제를 해결하기위해서는 반대쪽, 즉 2000293을 문자로 변환해주면 문자대 문자의 비교이므로 내부적 변형이 발생하지 않게된다.
SELECT LAST_NAME,FIRST_NAME
FROM EMPLOYEES
WHERE EMP_ID = ‘200383’;
 
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=19)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (TABLE) (Cost=2 Card=1 Bytes=19)
2 1 INDEX (RANGE SCAN) OF 'PK_EMP_ID' (INDEX) (Cost=1 Card=1)
아래 SQL을 보자 JOB에 NULL인 조건을 검색하는 것이다.
SELECT LAST_NAME,FIRST_NAME
FROM EMPLOYEES
WHERE JOB IS NULL
아래 SQL을 보자 JOB이 NULL인 조건을 검색하는 것이다.
물론 아래와 같은 JOB INDEX를 생성하였다.
CREATE INDEX IDX_JOB ON EMPLOYEES (JOB);
아래 PLAN을 보자 왜 IDX_JOB INDEX를 타지 못하는가?
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29 Card=3 Bytes=63)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=29 Card=3 Bytes=63)
이경우에는 Oracle의 경우 일반적으로 index를 생성할 때 null값은 index항목에 넣지 않는다. 따라서 null은 index에 없기 때문에 null조건을 준다면 그것은 index를 탈수 없다.
따라서 위와 같은 경우 반드시 index를 타려거든 job컬럼을 NOT NULL로 설정하고 NUL대신 특정값 (예를 들면 : ‘NOT ASSIGN’ ) 으로 설정하고 QUERY를 아래와 같이 수정한다면 인덱스를 탈수 있을 것이다.
SELECT LAST_NAME,FIRST_NAME
FROM EMPLOYEES
WHERE JOB = ‘NOT ASSIGN’;
아래 SQL를 하나 더 보자
SELECT LAST_NAME,FIRST_NAME
FROM EMPLOYEES
WHERE JOB NOT IN ( 'INSTRUCTOR','STAFF');
이번의 NULL을 비교한것도 아닌데 INDEX를 사용하지 못한다. 이것은 일반적인 INDEX가 =이나 <, > , BETWEEN조건에 만 인덱스를 탈수 있고 부정형으로 비교했을때는 인덱스를 탈수 없기때문이다.
생각해보자 어떤 것을 순서대로 정리해 놓았는데 그것이 아닌 것을 찾으려고 한다면 전체를 다 읽어봐야지만 아니것을 알수 있지 않은가?
따라서 가급적 프로그램 구성에서 부정형 조건이 들어가게 한다는 것은 성능을 저하시킬 가능성이 매우 높기 때문에 이런 조건이 되지 않도록 설계단설계부터 고려해야한다.

이상은 간단하게 INDEX를 주었을 때 일반적으로 INDEX를 타지 못하는 경우를 든것이다. 사실 위예 예처럼 실제 프로젝트에서 많은 부분이 INDEX를 생성하고도 OPTIMIZER의 특성을 몰라서 INDEX를 쓰지 못한채 APPLICATION이 돌고 있다. 이는 곧바로 자원의 과도 사용으로 나타나고 느린 응답시간으로 나타나게 된다. 항상 시스템을 OPEN하고 마음을 조리지 않으려면 내가 생성된 INDEX를 잘 탈수 있게 내가 SQL을 잘 작성했는지 검토해 보기 바란다.
아래 4개의 항목은 반드시 기억해 두기 바란다.
인덱스를 사용하지 못하는 경우는 아래와 같다.
  • 인덱스 컬럼에 변형이 일어난 경우
    • WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
    • WHERE SALARY + 1000 > 100000;
  • 내부적인 변형이 일어난 경우
    • WHERE EMP_ID = 200383;
  • NULL을 비교하였을 경우
    • WHERE JOB IS NULL;
  • 부정형으로 조건을 기술한 경우
    • WHERE JOB NOT IN ( 'INSTRUCTOR','STAFF');

물론 이 경우 이외에 Optimizer의 판단에 따라서 인덱스를 사용하지 못하는 경우도 있다. 그러나 대부분의 경우에는 위에 항목을 만족한다면 원하는 index를 타는 효율적인 sql작성에 좋은 기준이 될것이다. 마지막으로 sql을 작성한후 반드시 plan을 확인해 보기 바란다.
실제 plan이 어떻게 되는냐를 확인해보지 않으면 무심코 과거의 실수를 답습할 수 있기때문이다.



+ Recent posts