부제 : min/max값을 안전하고 빠르게 구하는 방법
최종일자, 최종순번을 구하기 위한 전통적인 방법은 index desc 힌트와 rownum = 1 조합이었다. 하지만 이것은 대단히 위험한 방법이다. 왜냐하면 튜닝을 하기 전에 값이 맞아야 하며, 성능튜닝은 그 이후의 문제이기 때문이다. 위의 방법은 인덱스의 구성컬럼이 변경 혹은 삭제되거나, 인덱스명이 바뀌면 max 값을 구하지 못한다. 즉 성능을 향상시키기 위해 값이 틀릴 수 있는 가능성을 열어놓은 것이다. 이런 방법은 어떤 이유로도 받아들여져서는 안 된다. 나 또한 예전에 이런 방법을 사용했지만 이는 필자의 명백한 잘못이었다.
올바른 값을 얻어야 하고 성능도 충족해야 하므로 오라클은 first_row(min/max) operation을 내놓았다. 따라서 우리는 index_desc + rownum 대신에 first_row(min/max)을 사용해야 한다. 그런데 항상 first_row(min/max)를 사용해야 할까? first_row(min/max)가 비효율적인 경우는 index_desc + rownum 조합을 생각해 볼 수 있다. 하지만 인덱스가 변경 및 삭제될 때 성능이 느려질지언정 답이 틀리면 안 된다. 만약 max가 아닌 잘못된 값으로 update 되었다고 상상해보라. 큰일이다. 원복시키기도 어렵다. update 가 여러번 되었을 수 있기 때문이다.
환경 :Oracle11g R2
CREATE INDEX ix_cust_channel_time ON SALES (CUST_ID, CHANNEL_ID, TIME_ID) ;
CREATE INDEX ix_cust_time_channel ON SALES (CUST_ID, TIME_ID, CHANNEL_ID) ;
CREATE INDEX ix_time_cust_channel ON SALES (TIME_ID, CUST_ID, CHANNEL_ID) ;
인덱스와 where 조건이 완벽할 때
SELECT /*+ gather_plan_statistics INDEX(s ix_cust_channel_time) */
MAX (time_id)
FROM sales s
WHERE cust_id = :v_cust --30777 대입
AND channel_id = 2;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 |
| 2 | FIRST ROW | | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IX_CUST_CHANNEL_TIME | 1 | 1 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)
인덱스가 CUST_ID + CHANNEL_ID + TIME_ID로 되어 있는 경우는 문제가 없다. first_row(min/max) operation을 사용할 수 있고 비효율이 없기 때문에 값이 틀려질 수 있는 index_desc + rownum을 사용해선 안 된다.
where 조건에 인덱스의 중간 컬럼이 빠졌을 때
SELECT /*+ gather_plan_statistics INDEX(S IX_CUST_TIME_CHANNEL) */
MAX (time_id)
FROM sales s
WHERE cust_id = :v_cust --30777 대입
AND channel_id = 2;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 |
| 2 | FIRST ROW | | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IX_CUST_TIME_CHANNEL | 1 | 1 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_ID"=:V_CUST)
filter("CHANNEL_ID"=2)
인덱스가 CUST_ID + TIME_ID + CHANNEL_ID 로 되어 있는 경우를 보자. 인덱스의 중간컬럼이 where절에 빠져있지만 CUST_ID의 선택도가 워낙 좋으므로 문제가 되지 않는다. first_row(min/max) operation을 그대로 사용하면 된다.
인덱스의 선두 컬럼이 where 조건에서 빠지는 경우
SELECT /*+ gather_plan_statistics INDEX(S IX_TIME_CUST_CHANNEL) */
MAX (time_id)
FROM sales s
WHERE cust_id = :v_cust --30777 대입
AND channel_id = 2;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 755 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.02 | 755 |
| 2 | FIRST ROW | | 1 | 1 |00:00:00.02 | 755 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.02 | 755 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))
인덱스가 TIME_ID + CUST_ID + CHANNEL_ID로 구성되어 있을때 인덱스의 선두 컬럼이 where 조건에서 빠졌다. 그로 인해 Index full scan이 발생하여 쓸모 없는 752블록을 Scan하였다. 즉 인덱스를 끝부분부터 계속 scan하다가 운 좋게 755 블록을 scan해보니 cust_id = 30777 와 channel_id = 2을 만족하는 값을 모두 처리한 것이다. 운이 나쁘면 인덱스를 모조리 읽어야 할 수도 있다.
서브쿼리나 인라인뷰를 이용하여 집합을 추가하자는 의견에 대해
인덱스의 선두 컬럼이 where 조건에서 빠지는 경우는 강제로 집합을 추가하자는 의견이 있다. 아래의 SQL이 그것이다.
SELECT TIME_ID
FROM ( SELECT /*+ LEADING(C) INDEX_DESC(S IX_TIME_CUST_CHANNEL) */ S.time_id
FROM sales S,
(SELECT TRUNC(SYSDATE) - LEVEL + 1 AS time_id
FROM DUAL
CONNECT BY LEVEL <= 7300 ) C
WHERE S.cust_id = :v_cust --30777
AND S.channel_id = 2
AND S.time_id = C.time_id )
WHERE ROWNUM = 1;
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 512 |
|* 1 | COUNT STOPKEY | | 1 | 1 |00:00:00.02 | 512 |
| 2 | NESTED LOOPS | | 1 | 1 |00:00:00.02 | 512 |
| 3 | VIEW | | 1 | 3484 |00:00:00.02 | 0 |
| 4 | CONNECT BY WITHOUT FILTERING| | 1 | 3484 |00:00:00.01 | 0 |
| 5 | FAST DUAL | | 1 | 1 |00:00:00.01 | 0 |
|* 6 | INDEX RANGE SCAN DESCENDING | IX_TIME_CUST_CHANNEL | 3484 | 1 |00:00:00.01 | 512 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("C"."TIME_ID") AND "S"."CUST_ID"=:V_CUST AND
"S"."CHANNEL_ID"=2)
이렇게 하니 Scan한 블럭수가 1/3 정도 줄어들었다. 하지만 불필요한 조인이 3484번이나 발생하였다. 이것이 최적은 아니다. 또한 명시적으로 max값을 보장하게 작성된 SQL도 아니다.
Index_ss 힌트를 사용했다. 하지만……
위의 예에서 보듯이 인덱스의 선두 컬럼이 조건절에 없을때 이빨이 빠진 집합을 추가하는 것과 first_row(min/max)를 사용하는 것은 둘다 비효율적이다. 그러므로 index_desc + rownum을 사용하되 값이 바뀌지 않도록 해야 한다. 그런데 인덱스의 첫 번째 컬럼이 조건 절에서 빠졌으므로 index_ss_desc + rownum을 사용해야 한다. 이것이 가능할까? SQL을 바꾸지 않으면 불가능하다. 아래의 SQL을 보자.
SELECT /*+ gather_plan_statistics INDEX_SS(s ix_time_cust_channel) */
MAX (time_id)
FROM sales s
WHERE cust_id = :v_cust --30777
AND channel_id = 2;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 755 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.02 | 755 |
| 2 | FIRST ROW | | 1 | 1 |00:00:00.02 | 755 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.02 | 755 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))
Min/Max와 Index Skip Scan을 동시에 사용할 수 없다
min 혹은 max 함수를 사용했을 때 Oracle9i 버전과는 달리 10g와 11g에서는 index skip scan을 사용할 수 없다. 힌트를 추가해도 마찬가지이다. 아래의 10053 trace를 보자.
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SALES[S]
ColGroup (#1, Index) IX_TIME_CUST_CHANNEL
Col#: 2 3 4 CorStregth: 185.95
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Table: SALES Alias: S
Card: Original: 918843.000000 Rounded: 33 Computed: 32.54 Non Adjusted: 32.54
kkofmx: index filter:"S"."CUST_ID"=:B1
kkofmx: index filter:"S"."CHANNEL_ID"=2
Access Path: index (Min/Max)
Index: IX_TIME_CUST_CHANNEL
resc_io: 3.00 resc_cpu: 21564
ix_sel: 1.000000 ix_sel_with_filters: 0.000035
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.01
***** End Logdef Adjustment ******
Cost: 5.28 Resp: 5.28 Degree: 1
Best:: AccessPath: IndexRange
Index: IX_TIME_CUST_CHANNEL
Cost: 5.28 Degree: 1 Resp: 5.28 Card: 1.00 Bytes: 0
***************************************
힌트를 사용했지만 Index Skip Scan은 고려조차 되지 않는다. 위의 Trace를 보면 "first row(Min/Max)가 가능하다면 Index Skip Scan을 고려하지 않는 로직이 10g와 11g의 옵티마이져에 존재한다” 라고 추론할 수 있다. 인덱스와 where 절이 일치하지 않는 상태라 하더라도 비효율적인 index full scan (Min/Max)에 만족할 수는 없다. 바로 이럴 때 index_desc 와 rownum 조합을 답이 틀려질 수 없도록 사용하면 된다.
아래처럼 max 함수를 제거하면 Index Skip Scan을 사용할 수는 있다.
SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */
time_id
FROM sales s
WHERE cust_id = :v_cust --30777
AND channel_id = 2
AND ROWNUM = 1;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 264 |
|* 1 | COUNT STOPKEY | | 1 | 1 |00:00:00.01 | 264 |
|* 2 | INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.01 | 264 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)
filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))
Max를 없애면 index skip scan을 사용할 수 있다. 하지만.....
블록수가 755에서 264로 1/3으로 줄어들었다. 하지만 인덱스가 수정 및 삭제되면 답이 틀릴 수 있으므로 위험하긴 마찬가지 이다. 따라서 다음의 SQL처럼 사용해야 한다.
안정적이고 성능을 고려한 SQL
SELECT MAX(time_id)
FROM ( SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */ time_id
FROM sales S
WHERE cust_id = :v_cust --30777
AND channel_id = 2
ORDER BY time_id DESC)
WHERE ROWNUM = 1;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 264 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 264 |
|* 2 | COUNT STOPKEY | | 1 | 1 |00:00:00.01 | 264 |
| 3 | VIEW | | 1 | 1 |00:00:00.01 | 264 |
|* 4 | INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.01 | 264 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
4 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)
filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))
ORDER BY를 사용했지만 인덱스의 영향으로 SORT를 하지 않으므로 성능저하도 없다. 또한 인라인뷰 내에서 ORDER BY를 사용하고 외부에서 ROWNUM을 사용했기 때문에 인덱스가 수정 및 삭제되더라도 성능이 느려질 뿐 값이 틀려질 수는 없다. 위의 SQL에서 마지막에 max 함수를 사용한 이유는 where조건에 만족하는 건수가 없더라도 null을 출력해야하기 때문이다. 앞으로 index_desc + rownum 조합을 사용할 것이라면 위의 방법을 사용하길 바란다.
결론
1. Index_desc + rownum을 사용하지 말고 first_row(min/max)를 사용하라
2. 1번이 비효율적인 경우에만 index_desc(혹은 index_ss_desc) + order by를 사용하고 뷰로 감싸라.
그리고 뷰 외부에서 rownum을 사용하라.
3. first_row(min/max)를 사용할 수 있는 환경에서는 index skip scan을 사용할 수 없다. 꼭 사용하려면 집계 함수를 제거하라.
이렇게 하면 성능과 안정성을 동시에 고려할 수 있다.
글의 배경
이런 이야기를 하는 이유는 비판적 사고의 필요성 때문이다. 개발자에게 인라인뷰와 Order by가 없는 Index_desc + Rownum의 위험성을 설명해주었더니 나에게 책을 가져온다. 나는 본적이 없지만 아주 좋은 SQL 튜닝 책이라고 한다. 그것도 어려운 영문 책이다. 열심히 공부하는 사람임에 틀림없다. 하지만 개발자의 한마디 때문에 그사람의 인상이 바뀌어 버렸다. 그 한마디는 “이 책에 Index_desc + Rownum을 사용하라고 되어있습니다.” 였다. 그것이 얼마나 위험한 것인지 여러번 증명하고 설득해 보았으나 맘을 바꾸기는 불가능 하였다. 이래서는 곤란하다. 책을 성경이나 불경처럼 여기고, 저자를 종교의 교주로 여겨서는 안 된다. 이론은 반론이 증명되면 폐기될 수 있다. 하지만 가치관이 개입된 믿음이나 신념은 좀처럼 바뀌지 않는다. 어떠한 증거를 내놓아도 그렇다. 신념은 종교생활에 사용했으면 한다.
책의 내용 중에 잘못된 것이 있으니 나쁜 책이라고 말하는게 아니다. 필자의 서적을 포함해서 모든 책의 내용은 틀릴 수 있다. 정작 나쁜 것은 책이나 저자가 종교화될 때이다. 그런 무 비판적 종교는 이공계 사람에게 치명적이다. 비판적 사고는 엔지니어와 과학자의 버팀목이자 과학기술을 발전시키는 핵심이기 때문이다. 널리 알려진 과학 논쟁인 쿤과 포퍼의 대결에서도 이러한 언급은 드러난다. 두명 모두 비판적 사고는 반드시 필요하다고 하였다. 다만 시기의 문제일 뿐이다.
답답한 마음에 글을 올려보았다. 앞으로 이런 글을 다시 쓰고 싶지 않다.
"신앙은 믿음으로 이루져야한다. 하지만 과학은 비판과 증명으로 이루어져야 한다."
'Oracle > Data Access Pattern' 카테고리의 다른 글
| 분석함수의 실행계획 - 2부 (12) | 2011.02.10 |
|---|---|
| 분석함수의 실행계획 - 1부 (12) | 2011.02.07 |
| Bloom Filter와 Group By의 관계 (10) | 2010.09.09 |
| Inlist Iterator를 사용하지 말아야 할 때 (13) | 2010.06.23 |
| 오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7) | 2010.01.12 |
