상식이 발목을 잡다
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는 필자가 눈으로 확인한 것만 링크로 나타내었다. 실제로는 이것보다 훨씬 많을 것이다.


Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.11.05 09:21  댓글주소  수정/삭제  댓글쓰기

    발상의 전환이이라 무척 힘든 얘기네요..
    테이블을 두번읽는것이 이런 이점이 있다고는 생각하지 못했습니다.
    항상 새로운 내용에 감탄합니다.

  2. 혈기린 2009.11.05 11:11  댓글주소  수정/삭제  댓글쓰기

    멋진발상의 전환이네요 같은테이블을 읽지마라 이건 sql작성할때 꼭지키기 위해 노력하던 문구였는데 그 고정관념이 파괴되는 발상의 전환이네요

    항상 좋은글 감사 드립니다 ^^

  3. 백면서생 2009.11.05 14:35  댓글주소  수정/삭제  댓글쓰기

    feelie,혈기린님 말씀 처럼 "같은 테이블(정확히 말하면 한번 읽은것이겠죠)을 반복해서 읽지 마라"는 일반적인 sql에서는 거의 변하지 않는 튜닝관점이라 생각이 듭니다.(물론 dw,mart에서는 반복해서 읽는게 성능-group by사용등등-에 엄청난 도움이 되기도 하지만요^^)
    하지만 우리가 위의 말을 "같은 테이블을 from절에 반복해서 사용하지 마라"고 오해를 해서 그런게 아닌가 생각이 듭니다.
    extremedb님이 올리신 sql도 from 절에 반복해서 사용은 되지만 반복해서 읽는 것은 아니니까요.^^


    오랜만에 들렀는데 너무 좋은 글들이 많네요.
    extremdb님의 수고에 늘 감사 드립니다.^^

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.11.05 15:03 신고  댓글주소  수정/삭제

      이글의 본래의도는 Nested Loop 조인을 사용할때 선행 집합처리를 완료하고 후행집합을 처리해야 한다는 고정관념을 깨는 것이었습니다. "후행집합의 처리가 완료되고 선행 테이블로 엑세스 할수 있다." 이런 의미였습니다. 그런데 백면서생님이 또다른 관점으로 지적을 잘해주셨습니다. "한번 scan한 블럭을 다시 scan 하지마라." 이런 의미인 것으로 판단 되네요.

  4. Favicon of http://dbguy.tistory.com BlogIcon 호야 2009.11.05 19:51  댓글주소  수정/삭제  댓글쓰기

    정말 몸에 배어나고 머리속에 깊숙이 박혀있는 테이블 한번만 읽어서 처리하기를

    이번 프로젝트에서 테이블 한번만 읽어서 처리한다는 상식을 깨지는 경험을 했습니다.

    sort 비용보다 테이블을 두번 읽어서 처리하는 비용이 더 싸다는것을 .. (모든 경우는 아닐것 같지만.. )

  5. Favicon of https://yangrak.tistory.com BlogIcon Yangrak Im 2009.11.05 21:07 신고  댓글주소  수정/삭제  댓글쓰기

    멋진 말이네요 ㅋ '모든 상식을 의심해라'

  6. hyun 2009.11.06 08:24  댓글주소  수정/삭제  댓글쓰기

    정말 멋진 발상의 전환이군요.
    상식이 상상력을 방해한 것이 아니라 상식안에 숨어 있는 고정관념을 깬 것이군요.
    from 절에 테이블을 두번 쓴 것일 뿐
    실제 테이블을 두번 읽은 것은 아니네요...
    우린 from 절에 테이블을 두번쓰면 두번 읽는다고 생각하게 되니까....

    좋은 글 잘 읽었습니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.11.06 21:20 신고  댓글주소  수정/삭제

      "상식이 상상력을 방해한 것이 아니라 상식안에 숨어 있는 고정관념을 깬 것" 이라는 말에 공감합니다.
      다른말로 나타내면 "사과가 익으면 떨어진다" 라는 말을 뉴튼은 "사과가 떨어진것이 아니라 지구가 끌어당긴것" 이라고 생각했지요. 이것도 숨어있는 고정관념을 깬것이라 할수 있죠.

  7. Favicon of http://http://blog.naver.com/amadeus__ BlogIcon baekihwan 2010.02.04 09:17  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽었습니다.
    선생님의 블로그를 rss로 걸어 놓고 편히(?) 받아 보기만 하다가 이렇게 글 올립니다.

    올려 주시는 거의 모든 글들이 저한테는 정말 많은 도움이 되고 있습니다.
    감사합니다.

  8. Favicon of https://1ststreet.tistory.com BlogIcon SITD 2011.04.06 13:33 신고  댓글주소  수정/삭제  댓글쓰기

    상식이란게 참 무서운거 같습니다.
    생각이 굳혀 버리니까요.
    좋은 글 감사드립니다 ^^
    자주자주 써먹어 봐야겠어요