모든 상식을 의심하라

상식이 발목을 잡다
SQL 튜닝을 해본 사람들은 "같은 테이블을 반복해서 사용하지 말라" 라는 보편화된 상식을 가지고 있다. 위와 같은 말들은 상창력(상상력 + 창의력)이 필요한 튜닝의 세계에서 오히려 그것을 발휘하지 못하게 발목을 잡을 수 있다. 상식을 비판 없이 따르는 것이 얼마나 위험한지 증명해보자.

 

환경 Oracle 11.1.0.6

--테스트를 위한 회원탈퇴고객 테이블 생성

CREATE TABLE SH.WITHDRAWAL_CUST AS
SELECT * FROM sh.CUSTOMERS WHERE rownum <= 5000;

 

--UNIQUE INDEX 생성

CREATE UNIQUE INDEX SH.PK_WITHDRAWAL_CUST ON SH.WITHDRAWAL_CUST (CUST_ID);

 

--SALES 테이블에 PROD_ID를 선두로 하는 결합인덱스 생성

CREATE INDEX SH.SALES_IDX_02 ON SH.SALES (PROD_ID, CUST_ID);

 

dbms_stats.gather_table_stats('SH','SALES',cascade=>true);

 

dbms_stats.gather_table_stats('SH','WITHDRAWAL_CUST',cascade=>true); 

 

후행테이블에서 건수가 줄어드는 경우

회원탈퇴고객 테이블(WITHDRAWAL_CUST)을 생성 하였고 SALES 테이블에 결합인덱스를 생성 하였다. 이제 모든 준비가 끝났으므로 상품번호 144을 구입한 고객 중 회원탈퇴고객을 조회해 보자.  

 

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */

             s.cust_id, w.cust_first_name, w.cust_last_name,

             s.prod_id,  s.time_id, s.channel_id, s.quantity_sold

   FROM sh.sales s, sh.withdrawal_cust w

 WHERE s.prod_id = 144

      AND s.cust_id = w.cust_id ; 

  

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ;  

상식적인 수준에서 SQL이 작성되었다. 아래의 Plan을 보자.  

---------------------------------------------------------------------------------------------------

| Id  | Operation                            | Name               | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------------

|   1 |  NESTED LOOPS                        |                    |    423 |00:00:00.11 |    3708 |

|   2 |   NESTED LOOPS                       |                    |    423 |00:00:00.11 |    3285 |

|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES              |   4091 |00:00:00.07 |    3209 |

|*  4 |     INDEX RANGE SCAN                 | SALES_IDX_02       |   4091 |00:00:00.01 |      17 |

|*  5 |    INDEX UNIQUE SCAN                 | PK_WITHDRAWAL_CUST |    423 |00:00:00.02 |      76 |

|   6 |   TABLE ACCESS BY INDEX ROWID        | WITHDRAWAL_CUST    |    423 |00:00:00.01 |     423 |

---------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("S"."PROD_ID"=144)

   5 - access("S"."CUST_ID"="W"."CUST_ID")

최적의 SQL인가?  

Plan을 보면 SALES 테이블(테이블 건수: 약 92만건)의 SALES_IDX_02 인덱스를 이용하여 4091건을 Select 하였고 연이어 SALES 테이블을 같은 건수만큼 Select 하였다. 그런 후에 WITHDRAWAL_CUST 테이블의 Unque 인덱스를 엑세스 하여 건수가 423 건으로 거의 10분의 1 수준으로 감소하였다. 건수가 줄어드는 이유는 전체고객에 비하여 회원탈퇴고객이 많지 않은 까닭이다. 많은 사람들이 위의 Plan은 현재 상태에서 최적이라고 생각할 것이다. 아래의 SQL은 결과는 같지만 SALES 테이블을 2번 사용하는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) LEADING(S1 W S) */

             s.cust_id, w.cust_first_name, w.cust_last_name,

             s.prod_id,  s.time_id, s.channel_id, s.quantity_sold

  FROM sh.sales s1, sh.withdrawal_cust w, sh.sales s

 WHERE s1.prod_id = 144

     AND s1.cust_id = w.cust_id

     AND s1.rowid = s.rowid ;   

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ; 

위의 SQL을 보면 SALES 테이블을 두 번 사용하였다. 상식 대로라면 같은 테이블의 중복사용으로 Read 한 Block 의 개수(Buffers 항목)가 원본 SQL보다 증가하여야 한다. 과연 그런가? 아래의 Plan을 보자.

  

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                 |                    |    423 |00:00:00.08 |     842 |
|   2 |   NESTED LOOPS                |                    |    423 |00:00:00.07 |     516 |
|*  3 |    INDEX RANGE SCAN           | SALES_IDX_02       |   4091 |00:00:00.02 |      17 |
|   4 |    TABLE ACCESS BY INDEX ROWID| WITHDRAWAL_CUST    |    423 |00:00:00.04 |     499 |
|*  5 |     INDEX UNIQUE SCAN         | PK_WITHDRAWAL_CUST |    423 |00:00:00.01 |      76 |
|   6 |   TABLE ACCESS BY USER ROWID  | SALES              |    423 |00:00:00.01 |     326 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S1"."PROD_ID"=144)
   5 - access("S1"."CUST_ID"="W"."CUST_ID")

 

오히려 성능이 향상 되었다
원본 SQL에 비하여 Block 의 I/O 개수가 4배 이상 줄어들었다. 대단하지 않은가? ID 기준으로 3 에서 SALES 테이블의 SALES_IDX_02 인덱스만 엑세스 하고 SALES 테이블(Alias 로는 S1)은 엑세스 하지않는다. 연이어 WITHDRAWAL_CUST 테이블과 조인함으로써 건수를 423으로 줄여 버린다. 건수를 10분의 1로 줄인 후에 마지막으로 Rowid를 이용하여 SALES 테이블(Alias 로는 S)에 S1의 Rowid로 엑세스 함으로서 성능향상이 가능한 것이다.

 

언제 같은 테이블을 두번 사용해야 하나?
Nested Loop 조인을 사용할때 선행 테이블의 인덱스 Scan후 곧장 선행 테이블로 엑세스 할때 Filter 가 없거나 Filter의 효과가 약하여 건수를 줄여주지 못하고 오히려 후행 테이블에서 건수를 획기적으로 줄여주는 경우가 있다. 이런 경우는 예외없이 같은 테이블을 2번 사용하여야 한다. 물론 Rowid를 이용해야 하는것은 당연하다. 여러분도 SQL 튜닝시 이런 상황을 많이 만났을 것이고 앞으로도 자주 만나게 될것이다. 이런 상황에서는 꼭 위의 방법을 사용하기 바란다.

 

지식의 저주

상창력을 이용해야 하는 경우는 상식이 오히려 방해가 될 수 있다. 이것은 지식의 저주라 불린다. 경험이 많거나 아는것이 많을수록 지식의 저주에 걸리게 될 확률은 높아진다. 누구라도 이런것이 조금씩은 있기 마련이다. 하지만 이런 종류의 저주를 많이 혹은 오래 받은 사람은 발전이 없거나 더디게 마련이다.

 
회피하기
지식의 저주를 받지 않은 사람은 역사를 바꾸기도 하였다. 갈릴레이의 지동설, 다윈의 진화론, 에디슨의 여러가지 발명품 등이 그것이다. 발상을 전환하기, 생각을 뒤집어 보기, 다른 방법으로 접근하기, 의도적으로 엉뚱한 생각하기, 동료와 BrainStorming 하기 등등 저주를 피할 방법은 많다. 이 개념을 SQL튜닝에만 한정시키면 곤란하다. 모든 분야에 사용할수 있다. 누가 알겠는가? 이러한 방법을 사용하여 여러분이 역사를 바꿀지……


PS
이렇게 상식을 뒤집는 케이스는 생각보다 많다. 아래에 링크된 Post 가 그런 경우이다.

Transformer - SQL 튜닝의 새로운 패러다임

Top SQL-( Rownum 의 정확한 사용법 )

위의 Link는 필자가 눈으로 확인한 것만 링크로 나타내었다. 실제로는 이것보다 훨씬 많을 것이다.