쿼리 튜닝 실습(stopkey, paging, 게시판 쿼리)

※ 문제

/*

페이징 처리 SQL을 튜닝하시오. (인덱스 생성 가능/불필요한 인덱스는 감점요소임)

 1) T_USR60 (사용자)

    - 1만건

 2) T_BBM60 (게시판)

    - 100만건

    - BBM_TYPE = 'KOR' AND REG_DTM BETWEEN ~~~ 결과 600건

    - 동일 사용자가 게시한 글이 거의 없다는 전제

*/

SELECT BBM_NO, BBM_TITL, BBM_CONT, REG_NM, PYMT_YN, REG_DTM

FROM  (SELECT BBM_NO, BBM_TITL, BBM_CONT, REG_NM, PYMT_YN, REG_DTM, ROWNUM RNUM

       FROM (SELECT BBM_NO, BBM_TITL, BBM_CONT, FN_GETREGNM(REG_NO) REG_NM, REG_DTM, PYMT_YN

             FROM T_BBM60

             WHERE BBM_TYP = 'KOR'

              AND  REG_DTM BETWEEN TO_DATE('20200101', 'YYYYMMDD')

                                          AND            TO_DATE('20200131 235959', 'YYYYMMDD HH24MISS')

              ORDER BY PYMT_YN, REG_DTM

            )

      )    

WHERE RNUM BETWEEN 11 AND 20

ORDER BY RNUM;

 

T_BBM60 테이블을 FULL SCAN을 하고 있어 인덱스 생성을 해주어야 합니다. 

FN_GETREGNM 사용 시 Context Switching이 발생하여 비효율적입니다.

RNUM BETWEEN 11 AND 20 조건을 보면 전체가 아닌 특정 구간의 데이터만 필요한 상황입니다.

 

 

※ 정답 

-- 인덱스 생성

CREATE INDEX IX_T_BBM60_01 ON T_BBM60(BBM_TYP, PYMT_YN, REG_DTM);

-- 통계 정보 오픈

ALTER SESSION SET STATISTICS_LEVEL=ALL;

SELECT BBM_NO, BBM_TITL, BBM_CONT, FN_GETREGNM(REG_NO), PYMT_YN, REG_DTM

FROM  (SELECT BBM_NO, BBM_TITL, BBM_CONT, REG_NO, PYMT_YN, REG_DTM, ROWNUM RNUM

       FROM (SELECT BBM_NO, BBM_TITL, BBM_CONT, REG_NO, REG_DTM, PYMT_YN

             FROM T_BBM60

             WHERE BBM_TYP = 'KOR'

              AND  REG_DTM BETWEEN TO_DATE('20200101', 'YYYYMMDD')

              AND                  TO_DATE('20200131 235959', 'YYYYMMDD HH24MISS')

              ORDER BY PYMT_YN, REG_DTM

            )

        WHERE ROWNUM <= 20

     ) A

WHERE RNUM BETWEEN 11 AND 20

ORDER BY RNUM;

-- 통계 정보 조회

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'IOSTATS LAST -ROWS'));

T_BBM60 의 WHERE 조회 조건에 따라 인덱스를 생성해주었고 실행계획을 보면 인덱스를 이용한 것을 확인할 수 있습니다. 

WHERE ROWNUM <= 20이란 STOPKEY를 선언해주어 불필요한 탐색을 줄였습니다.

FN_GETREGNM을 데이터가 가장 적게 조회되는 상단으로 옮겨 수행하여 효율을 높였습니다. 

 

 

※ 문제 실습 환경 (테이블 생성, 데이터 입력, 세션 설정 변경, 함수)

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

ALTER SESSION SET SORT_AREA_SIZE = 2000000000;

DROP TABLE  DEV.T_BBM60;

CREATE TABLE DEV.T_BBM60

 (

    BBM_NO       NUMBER                         NOT NULL,

    HI_BBM_NO    NUMBER         DEFAULT 0       NOT NULL,

    BBM_TYP      VARCHAR2(3)                            ,  -- 'KOR'

    BBM_TITL     VARCHAR2(200)                  NOT NULL,

    BBM_CONT     VARCHAR2(4000)                 NOT NULL,

    BBM_HIT      NUMBER         DEFAULT 0       NOT NULL,

    PYMT_YN      VARCHAR2(1)    DEFAULT 'N'     NOT NULL, -- 'N'

    REG_NO       VARCHAR2(10)                   NOT NULL,

    REG_DTM      DATE           DEFAULT SYSDATE NOT NULL,

    MDF_NO       VARCHAR2(7)                            ,

    MDF_DTM      DATE                                   ,

    CONSTRAINT PK_T_BBM60 PRIMARY KEY (BBM_NO)

 ) NOLOGGING;

DROP TABLE DEV.T_USR60;

CREATE TABLE DEV.T_USR60

  (

    USRNO VARCHAR2(10),

    USRNM VARCHAR2(20),

    CONSTRAINT PK_T_USR60 PRIMARY KEY(USRNO)

  )NOLOGGING;

CREATE OR REPLACE PUBLIC SYNONYM T_BBM60     FOR DEV.T_BBM60;

CREATE OR REPLACE PUBLIC SYNONYM T_USR60     FOR DEV.T_USR60;

CREATE OR REPLACE PUBLIC SYNONYM FN_GETREGNM FOR DEV.FN_GETREGNM;

INSERT /*+ APPEND */ INTO T_USR60

SELECT 'U' || LPAD(TO_CHAR(ROWNUM), 9, '0')      USERNO,

       TO_CHAR(ROWNUM)                           USERNM

FROM DUAL

CONNECT BY LEVEL <= 10000

ORDER BY DBMS_RANDOM.RANDOM()

;

COMMIT;

INSERT /*+ APPEND */ INTO T_BBM60

SELECT BBM_NO, HI_BBM_NO, BBM_TYPE, BBM_TITL, BBM_CONT, BBM_HIT,

       CASE WHEN BBM_TYPE = 'KOR' AND MOD(ROWNUM, 5) = 0 THEN 'N' ELSE 'Y' END PYMT_YN,

       REG_NO, REG_DTM, MDF_NO, MDF_DTM

FROM (

        SELECT ROWNUM                                                                   BBM_NO   ,

               ROWNUM                                                                   HI_BBM_NO,

               CASE WHEN TRUNC(DBMS_RANDOM.VALUE(1, 50)) = 1 THEN 'KOR'                          

                    ELSE TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1,999)))                                

               END                                                                      BBM_TYPE ,

               'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                             BBM_TITL ,

               'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                             BBM_CONT ,

               0                                                                        BBM_HIT  ,

               A.USRNO                                                                  REG_NO   ,

               TO_DATE('20200101', 'YYYYMMDD') + TRUNC(DBMS_RANDOM.VALUE(1, 1000))      REG_DTM  ,

               '1234567'                                                                MDF_NO   ,

               SYSDATE + TRUNC(DBMS_RANDOM.VALUE(1, 1000))                              MDF_DTM

        FROM (SELECT USRNO FROM T_USR60) A,

             (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100)

     )

ORDER BY DBMS_RANDOM.RANDOM()

;

COMMIT;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('DEV', 'T_BBM60');

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('DEV', 'T_USR60');

CREATE OR REPLACE FUNCTION DEV.FN_GETREGNM ( V_ID IN T_USR60.USRNO%TYPE)

RETURN T_USR60.USRNM%TYPE IS RESULT T_USR60.USRNM%TYPE;

BEGIN

    SELECT A.USRNM  INTO RESULT

    FROM T_USR60 A

    WHERE A.USRNO = V_ID;

   

    RETURN(RESULT);

   

    EXCEPTION

    WHEN NO_DATA_FOUND THEN RESULT := '';

    RETURN(RESULT);

   

    WHEN OTHERS THEN RETURN(NULL);

END FN_GETREGNM;

/

ALTER SESSION SET WORKAREA_SIZE_POLICY = AUTO;