[Oracle] DB에서 INDEX 제대로 사용하기

DB에서 INDEX 제대로 사용하기

작성자 : 김문규
최초 작성일 : 2009.5.18

데브피아에서 기가 막히게 좋은 전문가 글을 찾았습니다. 간단하게 정리해 봅니다.

인덱스를 사용하기를 기대하지만 그렇지 않은 기본적이고 대표적인 예입니다.

1. 인덱스 컬럼을 변형하여 비교할 때

BAD
WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
GOOD
WHERE HIREDATE = TO_DATE('19980518')

BAD
WHERE SALARY + 1000 > 100000;
GOOD
WHERE SALARY > 100000 - 1000;

비교하는 인덱스 컬럼의 형이나 값을 변경하면 발생합니다.
이 경우에는 비교값을 변경해 주어야 인덱스를 사용하게 됩니다.

2. 비교 대상의 형이 달라서 내부적으로 형변환을 하는 경우

BAD
WHERE EMP_ID = 200383;
GOOD
WHERE EMP_ID = ‘200383’;

EMP_ID가varchar라고 할 경우에 비교값이 숫자인 경우에 DB에서 자동으로 이를 숫자로 변경하고 비교하게 됩니다. 이 경우에 인덱스 컬럼에 변형이 일어났기 때문에 인덱스를 사용하지 못하게 됩니다.

3. NULL을 비교하는 경우

BAD
WHERE JOB IS NULL;

일반적으로 Oracle을 기준으로 NULL은 인덱스 대상이 아니라고 합니다. 따라서, 이를 해결하기 위해서는 NULL을 쓰지 말고 다른 정해진 값을 이용해서 비교해야 합니다. (흠..이건 좀...)

4. 부정형 조건인 경우

BAD
WHERE JOB NOT IN ( 'INSTRUCTOR','STAFF');

부정형 역시 인덱스를 사용하지 못하는 대표적인 조건 쿼리 입니다. 아닌 놈을 찾으려면 전체를 뒤지는 수 밖에요. 이를 피하기 위한 근본적인 DB 모델링이 중요합니다.


이하 원문을 그대로 가져다 붙입니다. 추가 설명이 필요하면 읽어 보세요.
http://www.devpia.com/DevStudy/Lecture/OffLineDetail.aspx?nSemiID=1429&lectype=evt

필자가 처음에 SQL을 배울 때 SQL이 상당히 이상했다. 원하는 것만 요구할 뿐 어떻게 가져오라는 정보가 SQL에는 없었기 때문이다. FILE레벨의 I/O까지 코딩에 익숙한 필자에게 절차가 없다는 것이 오희려 더 이상했던 것이다. 물론 상세한 과정이 필요하지 않으므로 편리하고 좋았다 그러나 어떻게 가져오는지는 알지못하고 단지 사용할 뿐이었다. 그러나 SQL이 PLAN이라는 실행 계획을 만들고 그에 따라 가져오게 된다는 사실은 안것은 한참 뒤에 일이었다. 저자소개: 유원석 (現) (주)위즈베이스 컨설팅본부 수석컨설턴트 프로젝트 : ㆍ하나투어 DB아키텍터 ㆍSK Telecom NGM Project 시스템 성능관리자 ㆍKT EDW Projec Main Architect 수행 ㆍLG 전자 Eagle Project 시스템 성능 개선 등 강 의 : ㆍ한국썬마이크로시스템즈 대용량데이터베이스 ㆍ한국썬마이크로시스템즈 Orange를 이용한 DB튜닝

결국은 내가 하지않은 일을 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이 어떻게 되는냐를 확인해보지 않으면 무심코 과거의 실수를 답습할 수 있기때문이다.