첨부파일의 일부 인덱스명에 오타가 있으니 댓글을 참조하기 바람(2009.11.05)

SQL
튜닝을 할 때 가장 기본적으로 익혀야 하는 것을 꼽으라면 무엇일까?

첫 번째, Data Access Pattern

두 번째, Join Method

위의 두 가지는 기본 중에 기본이다. 이 두 가지는 너무도 중요하기 때문에 튜닝을 시작하는 사람 혹은 튜너를 지망하는 사람들은 보고 또 보아야 한다. 실제로 이두가지를 모두 정복한다면 SQL 튜닝중의 많은 부분을 커버할수 있다. 이것은 비단 오라클 SQL 튜닝만이 아니라 모든 DBMS가 동일 할 것이다.

오늘은 두가지 중에 첫 번째의 Data Access Pattern의 개념에 대해서 이야기 해보자.

 

Data Access Pattern을 최대한 간단하게 표현하면 아래와 같다.

 

Index Scan       : 인덱스 사용

Full Table Scan  : 인덱스 미사용

Rowid            : Rowid로 테이블 엑세스

 

하지만 위의 3가지는 너무나 추상적이다.
위의 3가지를 좀더 상세히 나타내 보자.


Rowid

Index Unique Scan    

Index Range Scan

Index Inlist Iterator

Index Skip Scan 

Index Full Scan

Index Fast Full Scan 

Bit Map Index Combination

Full Table Scan        

 

3가지가 9가지가 되었다. 이제 제법 실전의 Plan에서 볼 수 있는 모습이 나왔다고 생각할 것이다.

하지만 아직 많이 부족하다.
실전의 Plan에는 위의 모습 보다 더욱 상세한 것이 필요하기 때문이다.

 

Rowid

Index Unique Scan    

Index Range Scan

Index Range Scan Descending

Index Range Scan (min/max)    

Index Inlist Iterator

Index Inlist Iterator Descending

Index Skip Scan 

Index Skip Scan Descending

Index Full Scan

Index Full Scan Descending 

Index Full Scan (min/max)   

Index Fast Full Scan 

BIT MAP OR           

BIT MAP AND          

BIT MAP MINUS        

BIT MAP MERGE

Full Table Scan        

 

드디어 3가지가 18가지가 되었고 실전적인 모습이 되었다.

위의 18가지 중에 한가지라도 찜찜한것이 있으면 첨부파일을 다운받아서 개념을 익히길 바란다.
파일을 배포 할 경우  출처를 밝혀주기 바란다.
파일 3개를 모두 다운 받은후 압축을 풀면 된다.

사용자 삽입 이미지

사용자 삽입 이미지



PS :
여기에 나오지 않은것들이 몇가지 있다. Partition Access Pattern은 이문서에서 언급되지 않았다.
다음 기회에..
Index Join 은 Data Access Pattern에 속하는 것이 아니고 Join method 에 포함되어야 한다.

신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.10.29 09:51 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 컨텐츠를 제공해 주셔서 감사드립니다.
    동규님의 열정은 따라갈수가 없군요... ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.29 12:16 신고  댓글주소  수정/삭제

      열정이라고 하시니 부끄럽군요.
      스터디는 요즘도 하시나요?

    • Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.10.29 12:44 신고  댓글주소  수정/삭제

      네.. ^^
      요즘은 비투엔에서 나온 '오라클 성능 고도화 원리와 해법 I'을 보고 있습니다.

      internal한 내용에 대해 조시형님이 잘 설명해 놓으셔서 읽기 좋더라구요..

      아래는 저희 스터디 위키 url입니다.

      http://wiki.oracleclub.com/display/CORE/Home

      동규님 책도 기다리고 있으니 출판되시면 알려주세요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.29 14:07 신고  댓글주소  수정/삭제

      이미 많은 수의 책을 스터디 하셨군요.
      부럽습니다.

  2. feelie 2009.10.29 12:53 신고  댓글주소  수정/삭제  댓글쓰기

    바쁘신데 정리된 자료까지 정말 감사합니다.
    가끔 공부에 대한 열정이 식으려고할때 올려주시는 자료가 다시 열정을 일으키게해주십니다.

  3. Favicon of http://lemonfish.egloos.com BlogIcon killy 2009.10.29 23:40 신고  댓글주소  수정/삭제  댓글쓰기

    아흑! 좋은 자료 너무 감사합니다.
    3가지 -> 9가지 -> 18가지 3단콤보~ 극적전개에 떡실신입니다.
    역시 기본이 중요하군요~

    더불어 위에 댓글에 강정식님께도 감사드려요~ 스터디 위키에 금쪽같은 자료들이네요~ 잘보겠습니다.

  4. 타락천사 2009.11.04 14:30 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 항상 감사드립니다.
    자~알 보겠습니다.
    제 블로그에 출처 밝히고 올리겠습니다.^^

  5. 2009.11.05 13:17  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  6. 타락천사 2009.11.05 13:17 신고  댓글주소  수정/삭제  댓글쓰기

    비밀글로 달았더니, 저도 안보여서 ^^;
    다시 올립니다.

    1. 오타
    특징
    - 첫번째 인덱스와 두번째 인덱스를 이용하여 두집합간에
    AND, OR, MINUS, MERGE 연산을 하여 데이터를 엑세스한다.
    - 하지만 하나의 조건만 Filter 되어 나오는 결과건수와 두개의 조건으로 Filter되어
    나오는 결과건수가 비슷하다면 비효율이 발생함으로 무조건 적용해서는 안됨

    index_combine( emp IX_EMP_N2 IX_EMP_N3) <== 2번째 인덱스명이 모두 잘못표기됨

    2. 아래 부분은 그대로 테스트 해봤는데. 실행계획이 BIT Map Minus 로 풀리지 않더라구요
    select /*+ index_combine(emp IX_EMP_N2 IX_EMP_N3 ) */
    empno, mgr, deptno
    from emp
    where not(mgr=7698)
    and deptno = 20

  7. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.11.05 13:54 신고  댓글주소  수정/삭제  댓글쓰기

    인덱스명이 잘못된것이 있군요.
    minus 가 풀리지 않는 문제는 원인 이 여러가지 입니다.
    일단 아래의 스크립트를 그대로 따라해보시기 바랍니다.
    그래도 안된다면 dbms_xplan.display_cursor(null,null,'advanced allstats last') 의 결과물을 저에게 주시기 바랍니다,

    --CONNECT SCOTT/TIGER
    DROP TABLE EMP;
    DROP TABLE DEPT;
    CREATE TABLE DEPT
    (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) ) ;
    CREATE TABLE EMP
    (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
    INSERT INTO DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES
    (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES
    (40,'OPERATIONS','BOSTON');
    INSERT INTO EMP VALUES
    (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
    INSERT INTO EMP VALUES
    (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
    INSERT INTO EMP VALUES
    (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
    INSERT INTO EMP VALUES
    (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
    INSERT INTO EMP VALUES
    (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
    INSERT INTO EMP VALUES
    (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
    INSERT INTO EMP VALUES
    (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
    INSERT INTO EMP VALUES
    (7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy')-85,3000,NULL,20);
    INSERT INTO EMP VALUES
    (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
    INSERT INTO EMP VALUES
    (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
    INSERT INTO EMP VALUES
    (7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy')-51,1100,NULL,20);
    INSERT INTO EMP VALUES
    (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
    INSERT INTO EMP VALUES
    (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
    INSERT INTO EMP VALUES
    (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
    DROP TABLE BONUS;
    CREATE TABLE BONUS
    (
    ENAME VARCHAR2(10) ,
    JOB VARCHAR2(9) ,
    SAL NUMBER,
    COMM NUMBER
    ) ;
    DROP TABLE SALGRADE;
    CREATE TABLE SALGRADE
    ( GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER );
    INSERT INTO SALGRADE VALUES (1,700,1200);
    INSERT INTO SALGRADE VALUES (2,1201,1400);
    INSERT INTO SALGRADE VALUES (3,1401,2000);
    INSERT INTO SALGRADE VALUES (4,2001,3000);
    INSERT INTO SALGRADE VALUES (5,3001,9999);
    COMMIT;

    create bitmap index IX_EMP_N2 on emp (mgr);
    create bitmap index IX_EMP_N3 on emp (deptno);

    select /*+ GATHER_PLAN_STATISTICS index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */
    empno, mgr, deptno
    from emp
    where NOT( mgr = 7698 )
    and deptno = 20 ;

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

  8. 타락천사 2009.11.06 15:36 신고  댓글주소  수정/삭제  댓글쓰기

    잘되네요..
    흠 저랑 샘플 테이블 차이는 Relation 이 있는것 뿐인데..
    제 실수였나보군요..
    감사드립니다.
    뒤에 인덱스명이 틀려도 잘 풀리네요 ^^;

  9. 서은아빠 2010.01.08 16:25 신고  댓글주소  수정/삭제  댓글쓰기

    해당문서에 index range scan 의 경우 기존 index 힌트로 사용가능하다는 부분이 있습니다.
    10.2.0.3부터 range scan 의 경우 INDEX_RS_ASC, INDEX_RS_DESC 의 힌트가 추가되었습니다.

    참고하시기 바랍니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.08 16:52 신고  댓글주소  수정/삭제

      말씀하신것 처럼 힌트가 추가되었습니다.
      하지만 공식적인 힌트가 아니므로 조심해서 사용하시기 바랍니다. 11gR2 메뉴얼에도 나오지 않습니다.메타링크에 버그도 보고되고 있습니다.

  10. 초보자 2011.05.26 15:35 신고  댓글주소  수정/삭제  댓글쓰기

    첨부파일이 손상되었다고 하면서 pdf파일이 열리지가 않습니다..

    괜찮으시면 taek58@gmail.com으로 파일 좀 첨부해서 보내주시면 안될까요?

    이제 막 오라클 공부하는 초보로서 여러가지 배울게 너무 많네요..^^

일자 데이터 타입이란 YYYYMMDD 형(시/분/초 제외)을 이야기 하는 것이다. 이때 DATE 타입을 선택할 것인가 아니면 VRACHR2(8)을 선택할 것인가의 문제이다. 이것은 성능 문제이기도 하지만 물리 모델링, 개발효율성, 데이터 품질 등을 같이 생각 해야 한다. 물리모델링 시에 많은 모델러들이 일자 데이터 타입과 관련하여 이구동성으로 이야기 하는 것이 아래의 SQL 이다.

 

SELECT ...                     

  FROM ...                     

 WHERE 기준일자 = TO_DATE('20091021', 'YYYYMMDD') ;

 

위의 SQL 에서 일자 컬럼에 시//초가 포함되어 있다면 조회가 되지 않는다.

그렇다고 SQL 을 아래처럼 작성하는 것은 개발효율성이 떨어지고 성능에도 이롭지 못하다.

 

SELECT ...                     

  FROM ...                     

 WHERE 기준일자 BETWEEN TO_DATE('20091021','YYYYMMDD') AND TO_DATE('200910212359', 'YYYYMMDDHH24MISS') ;

 

아니 땐 굴뚝에 연기가 날까?

VARCHAR2(8)을 선호하는 사람들이 주로 이 문제를 제기한다. DATE 타입은 시//초가 들어감으로써 세가지 문제(데이터가 조회되지 않을 수 있고, 개발효율성과 성능이 떨어짐)가 발생함으로  VARCHAR2(8)을 사용해야 한다는 것이다.

 

하지만 과연 이 말이 사실일까? 모든 문제는 모델러가 시//초가 들어갈 수 있게 설계를 했기 때문이다. 왜 그런지 아래 스크립트를 보고 증명해보자

 

-- DATE 형과 VARCHAR2 형을 동시에 가진 테이블 생성                           

CREATE TABLE DT                                                              

( V_DT  VARCHAR2(8 BYTE),                                                    

  D_DT  DATE ) ;                                                                                                                                    

                                                                              

--일자 타입이 date 인 경우에 시//초가 입력될 경우 걸러내는 Constraint.        

--ex) //초를 포함하는 SYSDATE를 입력하면 에러를 발생시키기 위함.                        

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.

ALTER TABLE DT                                                               

ADD CONSTRAINT D_DT_CHK                                                      

CHECK (D_DT = TRUNC(D_DT) OR D_DT IS NULL) ;        

            

이제 DATE 타입에 정상적인 데이터와 걸러져야 하는 데이터를 INSERT 해보자                            

 

--NULL을 대입해도 에러 발생하지 않음                                                                              

INSERT INTO DT (V_DT, D_DT) VALUES(NULL, NULL) ;                                                  

 

--SYSDATE는 시//초가 들어감으로 INSERT 되면 안됨                                                                              

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, SYSDATE) ;                          

ORA-02290: 체크 제약조건(D_DT_CHK)이 위배되었습니다                   

                   

--에러 발생하지 않음                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, TRUNC(SYSDATE)) ;            

 

Constraint는 데이터 품질을 보장해준다

위에서 보는 바와 같이 Constraint는 시//초가 들어가지 않도록 보장해준다.

이것은 성능이 느린 VARCHAR2 타입을 사용하지 말아야 하는 이유가 될 수 있다.

 

어쩔 수 없이 VARCHAR2(8)을 사용하더라도 Constraint를 사용하라

일자 데이터 타입에 VARCHAR2(8)을 사용할 때 날짜가 아닌 데이터가 들어가는 문제도 마찬가지로 해결할 수 있다. 아래처럼 Constraint를 사용하면 된다.

 

--일자 타입이 VARCHAR2 인 경우에 잘못된 데이터를 걸러내는 Constraint .        

--ex) ‘20090230’을 걸러낸다.

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.                                                  

ALTER TABLE DT                                                               

ADD CONSTRAINT V_DT_CHK                                                      

CHECK (V_DT = TO_CHAR(TO_DATE(V_DT,'YYYYMMDD'), 'YYYYMMDD') OR V_DT IS NULL) ;

 

이제 VARCHAR2(8)에 일자가 아닌 데이터를 넣어보자

 

--230일은 일자가 아니므로 INSERT 되면 안됨                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES ('20090230', NULL) ;                       

ORA-01839: 지정된 월에 대한 날짜가 부적합합니다

 

--에러 발생하지 않음                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES ('20090228', NULL) ;  

 

성공적으로 일자가 아닌 String 을 걸러 내었다.

 

결론

이제 알 것이다.  Constraint가 데이터 품질을 향상시키고 개발효율성을 높이며 성능에 까지 영향을 끼친다는 사실을

반박하라. //초가 들어감으로 VARCHAR2(8)을 사용해야 한다는 주장을

사용하라. 성능과 데이터 품질을 향상 시키는 DATE 타입을

오라클에서 시/분/초는 제외하고 일자만 저장되는 데이터 타입을 제공한다면 하는 아쉬움이 진하게 남는다.  미래의 버젼에 이러한 요구사항을 해결한 데이터 타입이 나오길 기대한다.


PS

DATE 타입을 성급히 적용하면 안 된다. 기존의 시스템은 일자가 아닌 데이터도 문제 없이 처리가 되었을 것이다. 하지만 DATE 타입으로 바꾸면 에러로 떨어진다. 따라서 에러의 처리정책과 처리Logic 등이 세워진 이후에 적용하라. 이런 골치 아픈 문제 때문에 VARCHAR2(8)을 사용하는 것은 말이 안 된다. 원칙적으로 에러로 떨어지는 것이 정당한 것이고 데이터를 수정하고 다시 처리하는 것이 옳다.

  

아래는 DATE 타입과 VARCHAR2 타입의 장단점 이므로 참고하기 바란다.

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



성능상으로는 DATE 형이 우월하다

여기에는 두 가지 이유가 있다.

 

첫 번째, DATE 타입은 옵티마이져가 날짜임을 인식한다.

 

SELECT ...                     

  FROM ...

 WHERE 기준일자 BETWEEN to_date('20091020', 'yyyymmdd') and to_date('20091021', 'yyyymmdd)

 

기준일자는 DATE 타입이다. 위의 조건대로라면 옵티마이져는 정확히 이틀간(10 20일부터 21일까지)이라는 것을 인지한다. 따라서 옵티마이져는 인덱스로 SCAN 할 것인지 아니면 FULL TABLE SCAN 할 것인지 판단할 수 있다.

 

하지만 기준일자가 아래처럼 VARCHAR2(8) 타입이라면 달라진다.

 

SELECT ...                      

  FROM ...

 WHERE 기준일자 BETWEEN '20091020' and '20091021'

 

옵티마이져는 '20091020'가 일자인지 인식 하지 못한다. 따라서 이틀 치의 데이터를 조회한다는 사실을 인식하지 못한다. String 타입이므로 이것은 당연한 것이다.

 

두 번째, DATE 타입은 7 byte를 차지하고 VARCHAR2(8) 8 byte를 차지한다.

 

두 가지 이유에 의해서 성능은 DATE 타입이 조금이라도 우월함을 알 수 있다.  

 

그럼에도 불구하고 DATE 타입을 사용하지 않는 가장 큰 이유는 무엇일까? 크게 3가지 이유로 요약된다.

 

첫 번째, DATE 타입의 문제점은 sysdate 등을 입력할 경우 시//초 가 들어감으로 SQL을 실행하면 결과값이 나오지 않는다.(이 문제는 위에서 이미 언급되었음)

 

두 번째, 년도나 월 데이터를 조회할 때 LIKE를 사용하지 못하고 BETWEEN 을 사용해야 한다는 것이다. 이것은 큰 문제라고 보지 않는다. 조건절이 조금 길어질 뿐 INDEX RANGE SCAN 이라는 점은 같기 때문이다.

 

세 번째, SYSDATE 등을 INSERT할 때 TRUNCT 등의 함수를 사용하여 시//초 등을 잘라내야 한다.

                

그렇다면 VARCHAR2 타입의 문제점은 없는가?
크게 3가지의 문제점이 있다.

 

첫 번째, 성능문제(옵티마이져가 일자인지 알 수 없음)

이 문제는 ORACLE 11g를 사용하면 더욱 심각한 차이가 발생할 수 있다.

왜냐하면 Bind Aware 기능이 강화되었기 때문에 변수를 마치 상수처럼 취급할 것이고 이에 따라 DATE 타입이 성능 면에서 훨씬 우월해 질것이다. 

                 

두 번째, VARCHAR2 타입의 문제점은 날짜가 아닌 데이터가 들어갈 수 있다는 것이다. 예를 들면 'ABCDEFGH' 혹은 '20090231' 등의 잘못된 데이터가 입력될 수 있다. 이것은 데이터 품질에 치명적이다. 혹자는 'DATE 타입도 시//초가 들어가므로 마찬가지 아니냐' 라고 생각할 수 있지만 근본적으로 다르다. DATE 타입을 사용하면 시//초는 TRUNC 등의 함수를 사용하여 Cleansing할 수 있지만 VARCHAR2는 그렇게 할 수 없다.

 

예를 들어 '20091032' 라는 데이터를 Cleansing 해야 한다고 치면 10 31일로 할 것인가? 아니면 11 1일로 할 것인가? 이것은 함부로 판단할 수 없는 것이다. 혹자는 'CHECK 기능을 추가하여 모든 프로그램에서 일자가 아닌 것을 CHECK 하면 되지 않냐?' 라고 할 수 있다.

 

맞는 말이다. 하지만 프로그램을 사용하지 않고 직접 DB KEY IN 하여 INSERT 할 수도 있기 때문에 원천적으로 원인을 제거한다고 볼 수 없다.(급한 경우에는 이렇게 하기도 한다)

 

세 번째, 성능문제에도 불구하고 개발편의성을 위해 VARCHAR2를 사용하였지만 일자연산이 발생하면 오히려 개발생산성이 저하된다.

 

예를 들면 일자끼리 빼서 차이를 본다든지 아니면 일자에 며칠을 더해서 본다든지 일자에 연산이 일어날 경우 오히려 TO_DATE 등의 함수를 사용해야 한다. 이런 경우는 자주 발생되는 편이다.

 

이상으로 DATE 타입과 VARCHAR2 타입의 장단점을 살펴보았다.

이 글과 관련된 POST 도 참고하기 바란다.

http://ukja.tistory.com/265

신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.10.21 19:14 신고  댓글주소  수정/삭제  댓글쓰기

    궁금한 내용중하나였는데 감사합니다.
    최근에 로그성 테이블을 만들면서 timestamp로 할것인지, varchar2로 할것인지를 생각하다가 조건에 사용되는 날짜는 varchar2로 해야 한다는 말이 생각나서 여러가지를 고려하지 않고 varchar2로 했습니다.
    로그성 테이블을 timestamp로 사용한 이유는 초까지 중복이 발생하는 경우가 많아서입니다.
    내용은 date type에 대한 내용인데요 내용상 timestamp도 동일하게 생각하면 되는겁니까?
    그리고 시분초를 포함해야 하는 경우에는 date, timestamp가 옳은 결정인가요? (문자열 조회조건, 간혹 시간차도 필요)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.21 23:12 신고  댓글주소  수정/삭제

      TIMESTAMP 의 성능은 제가 사용해보지 않았기 때문에 뭐라고 말씀드릴수가 없네요.
      하지만 시분초까지만 필요하시다면 DATE 타입을 사용하시느것이 좋을것입니다.

    • Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.10.22 13:26 신고  댓글주소  수정/삭제

      TIMESTAMP는 DATE의 다음 버전, 더 정확하게 말하면 ANSI 버전으로 생각하면 될 것 같습니다. 국제표준에 맞는 시간표현 데이터 타입이라고 보면 되지 않을까요? TIMESTAMP와 더불어 INTERVAL이 추가되면서 시간 계산에 있어 거의 무한한 자유가 주어졌죠. TIMESTAMP(0)으로 하면 DATE와 동일하니까 시분초까지만 필요한 경우에도 사용할 수 있구요.

      개인적으로 TIMESTAMP에서 제가 가장 유용하다고 생각하는 것은 시간 연산의 결과가 더 직관적이라는 것입니다. 아래 결과를 보면 TIMESTAMP를 사용한 경우 시간의 차이가 역시 시간으로 표현되기 때문에 훨씬 직관적이죠. 반면에 DATE는 1일 기준의 초로 표현되기 때문에 역으로 연산을 해야하는 단점이 있습니다.

      UKJA@ukja1106> create table t1(c1 date, c2 date, c3 timestamp(0), c4 timestamp(0));

      Table created.

      Elapsed: 00:00:00.03
      UKJA@ukja1106>
      UKJA@ukja1106> insert into t1(c1, c3) values(sysdate, systimestamp);

      1 row created.

      Elapsed: 00:00:00.00
      UKJA@ukja1106>
      UKJA@ukja1106> exec dbms_lock.sleep(10);

      PL/SQL procedure successfully completed.

      Elapsed: 00:00:10.01
      UKJA@ukja1106>
      UKJA@ukja1106> update t1 set c2 = sysdate, c4 = systimestamp;

      1 row updated.

      Elapsed: 00:00:00.00
      UKJA@ukja1106>
      UKJA@ukja1106> select c2 - c1, c4 - c3 from t1;

      C2-C1
      ----------
      C4-C3
      ---------------------------------------------------------------------------
      .000115741
      +000000000 00:00:10


      UKJA@ukja1106> select .000115741*24*60*60 from dual;

      .000115741*24*60*60
      -------------------
      10.0000224

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.22 13:34 신고  댓글주소  수정/삭제

      그렇네요. 직관적이고 초로 표현되므로 역으로 환산할 필요도 없구요. 덕분에 몰랐던것을 알게 되었습니다. 고맙습니다.
      DBMS 종류별(sql server, oracle, db2)로 timestamp ,datetime 그리고 date 등을 한번 정리해야 할 필요가 있을거 같습니다. 이부분이 많이 혼동되는 것이 사실입니다.

  2. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.10.22 08:47 신고  댓글주소  수정/삭제  댓글쓰기

    깔끔합니다! 이런 접근이 필요했습니다. ^^

  3. 마늘장아찌 2009.10.23 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    좋은정보 감사합니다.
    =================
    실무에서 항상 고민해 봤었던 내용인데 명쾌하게 정리를 해주셨네여.
    과거 informix DB에서는 Date 와 Datetime으로 구분이 가능했고 interval이나 timestamp
    개념이 있었던 기억이 새록새록 납니다. 그게 벌써 10년도 더된 이야기네요...

  4. 만두사랑 2009.10.28 13:57 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 감사합니다.
    도움이 많이 되네요^^

  5. 김시연 2009.10.28 16:41 신고  댓글주소  수정/삭제  댓글쓰기

    오랜만에 방문해서 그동안 쓰신글을 한번 쭉 보고 갑니다~! 올때마다 새로운것을 알고 가니 참 좋습니다. 요즘은 필드를 거의 안나가서 직접적으로 오라클을 할일이 없긴한데, 필드에 나가게 되면 응용해보도록 하겠습니다.

    그리고 집필활동은 잘 되고 계신가요? 시간을 쪼개서 책을 쓰시는것이 참 어려운일일텐데... 건강에 유의하시고요.

    그럼 다음에 또 들르겠습니다.

    PS) 혹시 JVM에 관심있으시면 http://wiki.ex-em.com/index.php/Java_Performance_Fundamental 여기한번 들러보세요~!

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.28 17:45 신고  댓글주소  수정/삭제

      오랜만입니다.
      저번에 프로젝트 5박 6일 이었던가요? 엑셈블로그에서 잘읽었습니다.
      엑셈에서 JVM 책을 발간하셨네요.
      JAVA 는 잘 모르지만 JDBC 쪽은 꼭보도록 하겠습니다.
      그리고 집필작업은 예정되로 진행되고 있습니다.
      성원에 감사드립니다.

  6. 2009.10.29 15:51  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  7. 2010.06.28 17:13  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  8. Ejql 2010.07.26 15:43 신고  댓글주소  수정/삭제  댓글쓰기

    한번씩 쭉 읽고 있습니다. 이번글은.. 가장 작은 전쟁중에 하나인것 같은데.. 정리를 잘 해 주셨습니다.
    감사합니다.

  9. Favicon of http://ccmpraise.tistory.com BlogIcon 주한길 2013.01.23 00:53 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 출처를 표시하고 블로그에 담아갑니다. ^^

“SQL 작성시 같은 테이블을 반복해서 사용하지 마라

위와 같은 말을 많이 들어보았을 것이다. 이런 말들은 개발자에게 마치 격언, 명언처럼 취급된다. 오늘도 개발자, DBA, 컨설턴트등 모든 사람들이 위의 명언에 너무도 충실하여 반복되는 테이블을 제거하기 위해 SQL을 재작성 하고 있다. 하지만 이런 말들이 명언이나 격언이 될 수 있을까? 이제는 격언이나 훈수, 명언이라고 생각하는 말도 최소한 상황에 따라 가려서 해야 한다. 왜 그럴까? 아래의 SQL을 보자. 

 

SELECT /*+ use_hash(c s)  */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 9 ;

 

위의 SQL은 대용량 테이블인 판매 테이블(sales)을 비효율적으로 2 Scan할 것으로 예상된다. 하지만 아래의 Plan을 보라. 과연 그렇게 수행되는가?

-----------------------------------------------------------+-----------------------
| Id | Operation                       | Name              | Rows  | Bytes | Cost |
-----------------------------------------------------------+-----------------------
| 0  | SELECT STATEMENT                |                   |       |       |   495|
| 1  |  HASH JOIN                      |                   |  449K |   20M |   495|
| 2  |   VIEW                          | VW_JF_SET$0A277F6D|     2 |    50 |     2|
| 3  |    UNION-ALL                    |                   |       |       |      |
| 4  |     TABLE ACCESS BY INDEX ROWID | CHANNELS          |     1 |    13 |     1|
| 5  |      INDEX UNIQUE SCAN          | CHANNELS_PK       |     1 |       |     0|
| 6  |     TABLE ACCESS BY INDEX ROWID | CHANNELS          |     1 |    13 |     1|
| 7  |      INDEX UNIQUE SCAN          | CHANNELS_PK       |     1 |       |     0|
| 8  |   PARTITION RANGE ALL           |                   |  897K |   18M |   489|
| 9  |    TABLE ACCESS FULL            | SALES             |  897K |   18M |   489|
-----------------------------------------------------------+-----------------------

Predicate Information:
----------------------
1 - access("ITEM_1"="S"."CHANNEL_ID")
5 - access("C"."CHANNEL_ID"=3)
7 - access("C"."CHANNEL_ID"=9)

 

이제는 Transformer 가 튜너이다.

환상적이지 않은가? channels 테이블을 정확히 2건만 Scan 하였고 sales 테이블은 1번만 Full Scan 하였다. 오라클 Transformer SQL을 아래처럼 재작성 한 것이다.

SELECT s.prod_id prod_id, s.cust_id cust_id, s.quantity_sold,

       s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

  FROM (SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 3

        UNION ALL

        SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 9) vw_jf_set$0a277f6d,

       sales s

 WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id ;

 

위와 같은 상황에서 SQL을 재작성 하는 기능을 JF(Join Factorization)라고 부른다. VW_JF_SET으로 시작되는 인라인뷰 명(Plan 상의 파란색 부분) JF가 수행되었음을 나타내는 것이다. 이것은 Oracle11g R2 에서 새로 추가된 대표적인 CBQT(Cost Based Query Transformation)기능 이다.

 

항상 수행되지는 않는다

CBQT Cost SQL튜닝을 수행할 것인지 아닌지를 판단한다. 그런데 복잡한 SQL의 경우에는 Cost Estimator가 판단을 잘못하여 JF를 수행하는 것이 비용이 더 비싸다고 판단할 수 있다. 이런 경우에는 Transformer SQL을 튜닝(재작성) 하지 않는다. 이럴 때는 아래와 같이 힌트를 사용해야 한다.

SELECT /*+ use_hash(c s) FACTORIZE_JOIN(@SET$1(S@SEL$1 S@SEL$2)) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 9 ;

 

쿼리블럭명 SET$1은 전체에 해당하는 쿼리블럭이고 SET$1 의 내부에 또 다른 쿼리블럭인 SEL$1(Union All로 분리된 것 중의 윗부분), SEL$2(Union All로 분리된 것 중의 아랫부분) 이 존재한다. 만약 Union All 이 하나 더 있다면 쿼리블럭명은 SEL$3가 될 것이다.

 

JF를 자세히 분석 하려면 10053 Event Trace를 이용하라

먼저 10053 의 용어 설명부분에 JF 가 아래처럼 추가 되었다.

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

중간생략

JF - join factorization


아래는 JF 부분에 해당하는 10053 Trace 정보이다.

***********************************

Cost-Based Join Factorization     

***********************************

Join-Factorization on query block SET$1 (#1)

JF: Using search type: exhaustive

JF: Generate basic transformation units

이후생략

 

결론

이제 격언이나 명언이라고 생각되는 말들도 상황을 가려서 해야 한다는 것을 알겠는가?. 그렇지 않으면 똑똑한 개발자에게 오히려 다음과 같은 말을 들을 것이다. “지금 말씀 하신 것은 예전 이야기 입니다. 요즘은 트랜스포머가 알아서 해줍니다.”  적어도 튜닝의 세계에서는 그렇다.

 

새로운 패러다임

JFTransformer가 수행하는 SQL튜닝의 하나일 뿐이다. Oracle11g R2 기준으로 SQL 튜닝(Query Transformation)의 종류는 필자의 짧은 지식으로도 70개 이상일 것으로 판단 된다. Oracle이 발전해 가면서 SQL튜닝은 사람이 관여하는 것에서 오라클이 자동으로 SQL을 변경해주는 것으로 많은 부분이 바뀌었고 앞으로 이런 추세는 점점 강화될 것이다. Query Transformation 은 단순한 Optimizer의 기능이 아니라 SQL 튜닝의 새로운 패러다임인 것이다. 이제는 직접 튜닝 하는 것에서 벗어나 Transformer가 실수하는 경우 새로운 길을 열어주는 것이 튜너가 가야할 길이 아닌가?


신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.10.15 19:44 신고  댓글주소  수정/삭제  댓글쓰기

    잘 봤습니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.15 21:27 신고  댓글주소  수정/삭제

      한번도 안거르고 열심히 하시네요.
      반드시 좋은 성과 있을것 입니다.
      좋은밤 되세요.

    • feelie 2009.10.16 09:04 신고  댓글주소  수정/삭제

      항상 좋은 내용 잘봅니다.
      열심히 할려고는하는데 볼때마다 새롭고 지난내용들도
      봤는데 다시볼때마다 생소한 느낌이 많이 드네요.
      아직 내공이 많이 부족한 모양이지요?
      아무튼 열심히 할렴니다..

  2. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.10.16 09:15 신고  댓글주소  수정/삭제  댓글쓰기

    11g에서 또한번 CBQT가 진일보되었나 보군여. ^^ 좋은 내용 감사합니다.

    "이제는 직접 튜닝 하는 것에서 벗어나 Transformer가 실수하는 경우 새로운 길을 열어주는 것이 튜너가 가야할 길이 아닌가?
    " 이 내용에 대해 저 또한 100% 공감합니다. 모든 SQL들에 대해 총괄감독(?) 아닌 조력자로 가야하지 않을까라고 말이죠.

  3. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.10.16 09:21 신고  댓글주소  수정/삭제  댓글쓰기

    11gR2에 관련된 내용이 본격적으로 다루어지네요. 내년에 R2가 얼마나 채택될지 기대됩니다.(경기가 좀 좋아져야겠지만... ^^)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.16 09:54 신고  댓글주소  수정/삭제

      11g R2 내용을 많이 다루려고 하고 있습니다.
      내년에 본격적으로 프로젝트가 시작되면 11g 관련 성능문제가 많이 이슈화 되겠네요. 기대됩니다.

  4. 혈기린 2009.10.16 09:28 신고  댓글주소  수정/삭제  댓글쓰기

    11g 에서 한층더 발전했네요 정말 이러다 나중에는 오라클이 다 알아서 해주는건 아닐지 ㅎㅎ
    좋은내용 감사 드립니다 ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.16 09:55 신고  댓글주소  수정/삭제

      말씀 하신것처럼 앞으로는 많은 부분을 Transformer 가 튜너의 일을 대신하게 될거 같습니다. 진정한 튜너만이 살아 남겠죠.

  5. 김봉호 2009.10.16 17:37 신고  댓글주소  수정/삭제  댓글쓰기

    이해는 잘 안가지만..
    가장궁금한건 트랜스포머가 무엇을 지칭하는건지요 -0-?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.17 23:38 신고  댓글주소  수정/삭제

      오라클에서 트랜스포머는 성능향상을 위해서 SQL 을 튜닝(재작성) 하는 역활을 담당합니다. 과거에는 튜너가 이런 역활을 했습니다만 이제는 트랜스포머가 많은 부분을 커버합니다.
      아래의 글을 참조하세요.
      http://scidb.tistory.com/entry/다단계-쿼리변환-Muti-Phase-Query-Transformation

오라클의 Regular Expressions을 정의 하면 아래와 같다
  • 유닉스의 정규식과 같음.
  • 강력한 Text 분석도구로서 Like 의 한계를 극복함.
  • Pattern-Matching-Rule의 정의가 자유로움.
  • 다양한 메타문자 제공.
Regular Expressions의 중요성은 아래와 같은 작업시 매우 증대된다
When
  • ETL/전환/이행.
  • Data Mining.
  • Data Cleansing.
  • 데이터 검증.
Regular Expressions의 기능은 다음과 같다
What
  • Text에서 특정 중복 단어의 확인
  • 특별한 상태에서 공백의 제거
  • 특정 문자의 파싱(parsing)
  • Text 에서 전화 번호, 우편 번호, 이메일 주소, 주민등록번호, IP 주소, 파일 이름, 경로 이름 등을 검증 및 추출이 가능
  • HTML 태그, 숫자, 날짜, 기타 특정 텍스트 데이터와 일치하는 패턴을 확인하고 다른 패턴으로 대체하는 것이 가능
  • Constraints 로 사용가능
이렇게 중요한 정규식(Regular Expressions)을 잘 모르거나 익숙치 못한 사람들을 위하여 개인적으로 정리한 파일을 올리니 이번기회에 반드시 정복하길 바란다.  의외로 고급 개발자들 중에 Regular Expressions의 사용을 꺼려하는 사람들이 많이 있는것 같다. 이 기능을 써야하는 경우임에도 쓰지 않는다면 코드는 길어질 것이며 유지보수 또한 힘들어 질것이다.
 
첨부파일에 정리된 내용은 다음과 같다 
  • 정규식 기본 Syntax.
  • 함수 사용법.
  • 정규식 고급 Syntax.
  • 11g New Features

주의 사항 :

  • 첨부된 파일에는 Oracle11g R1 까지의 내용을 정리한 것임.
  • 배포시에는 반드시 출처를 밝힐것.
  • Upload 시 200K 용량제한 때문에 파일을 2개로 분할 압축 하였으므로 모두 다운 받은후 압축을 풀면 됨.

오라클 Regular Expresssions 완전정복.alz

오라클 Regular Expresssions 완전정복1





오라클 Regular Expresssions 완전정복.a00

오라클 Regular Expresssions 완전정복2




 
사용자 삽입 이미지



신고
Posted by extremedb

댓글을 달아 주세요

  1. 백면서생 2009.10.07 09:21 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 공유해주셔서 감사합니다.

  2. 혈기린 2009.10.08 09:19 신고  댓글주소  수정/삭제  댓글쓰기

    매번 좋은자료 좋은글 감사드립니다 ^^
    오늘도 많이 배우고 갑니다

  3. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.10.08 09:55 신고  댓글주소  수정/삭제  댓글쓰기

    저 역시도 Regular Expression을 즐겨 사용하고 있습니다. 고맙죠. 오라클이 이런 기능을 내장해준다는 게. :)

    그리고 Google Document나 http://www.scribd.com/, http://www.slideshare.net/ 같은 곳을 이용해서 문서를 공유하는게 어떨까 합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.08 10:01 신고  댓글주소  수정/삭제

      그런 사이트가 있었군요.
      좋은 정보 감사합니다.
      영문 ppt 만 올려야 하나요?
      한글을 거기서 사용할수 있는지 궁금하네요.

  4. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.10.08 10:43 신고  댓글주소  수정/삭제  댓글쓰기

    Google Document는 확실히 지원이 되는데 다른 경우에는 해봐야 알겠네요. 요즘은 국제화 지원이 보편적이라서 문제가 없을거 같긴 하지만 확인이 필요합니다.

  5. 만두돌이 2009.10.08 13:37 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다. 정말 좋은 자료네요.^^
    열공 하겠습니다.

  6. 바우 2009.10.08 15:00 신고  댓글주소  수정/삭제  댓글쓰기

    오동규 수석님 한가지 여쭤보겠습니다.
    데이터를 다음과 같이 만들고
    create table test as
    select '111-3333' data from dual union all
    select '11a-88gd' from dual union all
    select '031-469-8834' from dual union all
    select '(041) 11a 88gd' from dual union all
    select '045.3344.312' from dual union all
    select '111-234' from dual union all
    select '11-23' from dual union all
    select 'aaa-bvd' from dual union all
    select '041 867 1385' from dual union all
    select '011-9956-8209' from dual union all
    select '010-789-0987' from dual

    select *
    from test
    where regexp_like(data,'..-')
    이렇게 쿼리를 날리면 앞에 두자리가 있고 대시가 나오는 것 즉 11-23만 나와야 할 것 같은데 결과가 그렇게 나오지 않습니다. 제가 .의 용도를 잘 못 알고 있는것인지요. 따라해보면서 체득하려고 했는데 처음부터 막히네요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.10.08 15:19 신고  댓글주소  수정/삭제

      '..-' 를 like 로 사용하게되면 숫자나 문자가 연달아 2번 오고 그다음에 - 가 오는 패턴을 포함하는것을 모두 가져옵니다.
      즉 111-222 라는 표현은 '..-' 조건을 만족 하지요.
      문자 두개가 연달아 있고 - 가 그다음에 있는 패턴이기 때문에 결과가 그렇게 나온것입니다. 원하는 결과가 나오게 하려면 시작문자 ^ 와 종료문자 $를 사용하시면 됩니다.
      select *
      from test
      where regexp_like(data,'^..-..$');

  7. 바우 2009.10.08 15:03 신고  댓글주소  수정/삭제  댓글쓰기

    9페이지에 쿼리는 3자리,3자리 혹은 3자리,3자리,4자리 이렇게 구성된 것을 찾는 것으로 보이는데
    가이딩 메시지에는 xxx-xxx-xxx 혹은 xxx-xxxx로 구성된다고 되어 있습니다.
    xxx-xxx-xxx가 xxx-xxx-xxxx로 바뀌어야 하는 것 같은데 그게 맞는 건가요?

  8. feelie 2009.10.09 09:42 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용감사합니다.
    정규식 관련해서 정리하기가 무척 힘들었었는데 이번에 확실히 정리할수 있겠네요...

  9. Favicon of http://sehaeng.tistory.com BlogIcon 쌩이~ 2009.10.13 17:55 신고  댓글주소  수정/삭제  댓글쓰기

    내공이 많으신글들 감사합니다.

    시간없다는핑계로 어설프게본 정규식을

    이렇게 정리해서 공유해주시다니..

  10. 그와함께 2009.10.23 16:12 신고  댓글주소  수정/삭제  댓글쓰기

    자료 공유에 관한 말씀이 있으시길래 알려드립니다.
    요즘 네이버 블로그는 포스트당 최대 10MB의 파일을 첨부할 수 있습니다.

    좋은 글에 늘 감사드리며.

  11. baind 2010.03.29 12:21 신고  댓글주소  수정/삭제  댓글쓰기

    진심으로..감사드립니다....ㅠ_ㅠ