반응형
출처: 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이 어떻게 되는냐를 확인해보지 않으면 무심코 과거의 실수를 답습할 수 있기때문이다.



반응형
 
C++은 상용화 프로그래밍을 하기에 가장 오랫동안 활발히 사용된 언어 이면서 애플리케이션의 기능에서 이용되는 모든 윈도즈 리소스를 세밀하게 제어할 수 있는 유일한 언어 입니다. 이러한 C++을 지원하는 대표적인 툴인 Visual C++을 이용한 개발은 초창기 윈도우 버전 때부터 최신 버전의 윈도우를 모두 지원해 줄 수 있습니다.
Visual C++ 개발환경의 최신 버전인 Visual Studio 2008을 이용하면 네이티브 환경뿐 아니라 최근까지 꾸준히 적용 범위를 늘려가고 있는 .NET 환경에 기존 프로그래밍 경험을 활용하는 유연한 가교 역할까지 해줄 수 있습니다.
Visual C++을 이용한 프로그래밍은 다른 언어를 이용한 방법보다 높은 진입장벽을 가지고 있습니다. 그리고 언어의 중요성에 비해 세미나등의 진입장벽을 넘는데 도움을 줄 수 있는 기회가 적었습니다. Ivor Horton의 Beginning Visual C++.NET 2008은 Visual C++을 이용한 네이티브 애플리케이션과 매니지드 환경에서의 애플리케이션 작성을 위한 기본기를 다지기 충분한 책이며, 더불어 출간을 기념하여 Visual C++을 이용한 상용 애플리케이션 개발 경험이 풍부한 개발자로부터 개발 노하우를 전달해 드리는 세미나 자리를 마련했습니다.
 
인터넷 서점 보다 저렴한 가격으로 현장 판매를 합니다. 15% 할인된 가격으로 3만 8천원에 판매하는 이벤트가 있으니 관심 있으신 분은 구입하시길 바랍니다.
 
주 제  Beginning Windows Programming with Visual C++
개최일시  2009년 04월 11일 15:00~18:00
장소  한국마이크로소프트 5층 교육장
참가대상  - 윈도즈 프로그래밍에 관심이 많은 분
 - Visual C++을 이용한 프로그래밍에 관심이 많은 학생
 - 윈도즈 프로그래밍에 대한 간접적인 실무 경험이 필요하신 분
참가자의
선수 학습내용
 - 기본적인 C++ 프로그래밍
 - 기본적인 윈도즈 프로그래밍
 - 윈도즈에 대한 전반적인 사용 경험
내용수준  초급
가 격  무료
 
[Session 1] Advanced Windows Programming with Visual C++ (권용휘)
Win32 API의 동작 원리
Advanced Windows Programming
Debugging the world
Windows Kernel Mode Programming
[Session 2] 윈도즈 애플리케이션 프로그래밍 이센셜 (김용현)
Windows, Visual Studio, SDK
ASSERT, Standard Annotation Language
애플리케이션의 하위호환성과 범용성 지원
임시방편에 의지하지 마세요.
최적화
[Session 3] C++/CLI와 Visual C++ Next (염원영)
Native C++과 C++/CLI
C++/CLI 기본과 활용
C++0x 미리보기
Visual C++ Next
 
시 간 Session 강 좌 제 목
15:00 ~ 15:50 50분 Session 1  Advanced Windows Programming with
 Visual C++ (권용휘)
15:50 ~ 16:00 10분 -  휴식
16:00 ~ 16:50 50분 Session 2  윈도즈 애플리케이션 프로그래밍 이센셜
 (김용현)
16:50 ~ 17:00 10분 -  휴식
17:00 ~ 17:50 50분 Session 3  C++/CLI와 Visual C++ Next (염원영)
17:50 ~ 18:00 10분 -  경품 추첨
* 각 섹션의 쉬는 시간은 유연성 있게 조절합니다.
 
권 용 휘 (http://rodream.net)
악성코드 제거기 울타리와 컴퓨터 최적화 프로그램인 클릭 투 트윅을 배포하고 있다. 2008년 부터 Visual C++분야에서 Microsoft MVP로 활동하고 있으며 데브피아 Visual C++ 분야의 시삽을 맡고 있다.
김 용 현 (http://www.YHKim.com)
Software Architecture와 Design Pattern에 관심이 많으며 다양한 분야의 윈도 애플리케이션 및 유틸리티 작성의 경험이 있다. 2006년 부터 Microsoft MVP로 활동하고 있으며 데브피아 Architecture와 Visual C++시삽을 맡고 있다.
염 원 영 (http://blog.daum.net/clark75)
다양한 윈도 응용 애플리케이션을 개발했으며, 현재 SQI소프트 부설 연구소에서 RIA를 개발하고 있다. 다양한 언어를 이용한 프로그래밍에 관심이 많고, 특히 최근에는 RIA에서 사용되는 선언형언어에 흥미를 가지고 있다.
 
 
- 세미나 당일 주차는 지원되지 않습니다. 가급적이면 대중교통을 이용해 주시기 바랍니다.
 
- 사이텍 미디어 IT 관련 인기 서적 3권
- 키보드 마우스 세트
 
 


오랫만에 올리는 주말 세미나 정보네요. 세미나 참석은 언제나 즐거운거 같습니다.

딱히 세미나 도중 제제가 있는 것도 아니고 다른 개발자 분들을 볼수도 있고 무엇보다 경품!!!!

전 아직까지 경품 득하지 못했지만 이전 무료세미나에 가서 엎드려 잠만 자던 친구가 경품 득했습니다.

너무 억울했지만 ㅠㅠ 달라고 해도 주지 않더 군요.. 소위 날로 먹었습니다.

자바 관련 일을 하지만 요즘 들어 C계열이 다시 땡기기 시작하네요.. ^^;

언어는 중요하지 않는것 같습니다. 각 환경에 맞는 언어 선택이 중요하다고 하죠.

하지만 해당언어에 대한 충분한 이해를 하는게 중요하리라 생각합니다. 각 언어에도 철학이 있다고 하죠.^^

므튼 초급개발자인 저는 세미나라고 하면 이곳저곳 기웃거리지만 이번 세미나는 프로젝트 일정상 패스 ㅠㅠ
반응형
 
실제 요구 사항을 맞추기 위하여 .net framework를 통하여 소프트웨어 개발을 진행하다 보면 숱하게 부딪히고 어려움을 겪게 되는 부분이 바로 비 관리 코드들과의 상호 연동에 관한 문제들입니다. 물론 전지전능한 msdn 라이브러리와 google의 힘을 빌어 손쉽게 해결하시는 분들도 계십니다. 하지만 생각보다 그렇게 답을 얻어내는 것은 쉽기는커녕 “고행”이라고 부를 정도입니다.
이러한 주제를 정형화하거나 완벽한 답이 무엇이라고 이야기하기는 힘들지만, 적어도 여러 가지 정보와 노하우를 풀어놓고 실전에서 겪었던 다양한 문제점들을 전하고 전달받는 그런 시간을 마련하고자 이번 세미나를 기획하게 되었습니다.
 
주 제  platform integration strategies and know-how for .net developers
개최일시  2009년 02월 21일 14:00~18:00
장소  한국마이크로소프트 5층 교육장
참가대상  c/c++과 c#을 동시에 사용하시는 분들, 윈도 api 응용이 힘드신 분들,
 .net 바깥의 다른 환경들과의 호환성 문제로 힘든 일정을 보내시는 분들
내용수준  중/고급 수준 - c# 이외에 c/c++ 프로그래밍 언어에 대하여 관심이
 있으시거나 한 번 이상 다루어보셨던 분들
가 격  무료
 
시 간 session 강 좌 제 목
14:00 ~ 14:50 50분 session 1  c 언어 연동에 대한 고민
15:00 ~ 15:50 50분 session 2  win32 api api 연동에 대한 고민
16:00 ~ 16:50 50분 session 3  activex 연동에 대한 고민
17:00 ~ 17:30 50분 session 4  unsafe 코드에 대한 고민
17:30 ~ 17:50 20분 -  질문과 답변
* 각 섹션의 쉬는 시간은 유연성 있게 조절합니다.
 
남 정 현 ㈜코아뱅크 / 산업기능요원 근무 중
- 데브피아 c# 마을 시삽 / microsoft mvp (visual c#)
- 남정현의 닷넷 블로그 (http://rkttu.com)
 
[session 1] c 언어 연동에 대한 고민
플랫폼 호출 서비스에 대한 이해
marshaling에 대한 이해
system.runtime.interopservices 네임스페이스의 주요 클래스들과 기능 둘러 보기
demo : heap memory allocation
demo : printf / scanf에 대한 사례와 한계
xplatform에 대한 이야기 #1 : c 언어 호환성
 
[session 2] win32 api api 연동에 대한 고민
dll 모듈 별 의미와 기능 이해
암시적 연결과 명시적 연결, 빠른 바인딩과 지연 바인딩
demo : window enumeration
demo : graceful window trace and handling
xplatform에 대한 이야기 #2 : win32 api 호환성
쉬어가기 : linux와 mono에서의 xplatform
 
[session 3] activex 연동에 대한 고민
com과 .net
activex와 .net #1 : gui 형태의 activex와 windows forms
activex와 .net #2 : component 형태의 activex와 activex wrapper
activex와 .net #3 : 컨테이너로서의 windows forms web browser control
demo : microsoft excel 편집 예제
demo : 한/글 컨트롤 사용 예제
 
[session 4] unsafe 코드에 대한 고민
unsafe 코드 문법과 기능상의 특성
demo : 배열 탐색의 새로운 방법
demo : unmanaged memory 영역 탐색을 통한 c 언어 스타일의 구조체 핸들링
 
 
- 세미나 당일 주차는 지원되지 않습니다. 가급적이면 대중교통을 이용해 주시기 바랍니다.
- 문 의 : 02-511-4824 (#132)
- 강의자료 : 다운로드
- 세미나 참가 시 활동왕 점수가 지급됩니다.
 
 

+ Recent posts