SQLP 실습 문제 (1-2번)

1번 실습문제

DROP TABLE 현물종목 PURGE;

CREATE TABLE 현물종목
(
    종목ID      NUMBER          NOT NULL
  , 종료일자    VARCHAR2(8)     NOT NULL
  , 시작일자    VARCHAR2(8)     NOT NULL
  , 종목명      VARCHAR2(100)   NOT NULL
  , 종목축약명  VARCHAR2(100)
  , 종목영문명  VARCHAR2(100)
  , 메모내용    VARCHAR2(1000)
  , 등록일시    DATE
  , 시장ID      NUMBER          NOT NULL
  , 증권그룹ID  NUMBER          NOT NULL
);

 

INSERT  /*+ APPEND */
INTO    현물종목
SELECT  A.종목ID
      ,(CASE WHEN X.RNUM = 1 THEN '99991231' ELSE TO_CHAR(SYSDATE - 2 * X.RNUM + 1, 'YYYYMMDD') END) AS 종료일자
      , TO_CHAR(SYSDATE - 2 * X.RNUM, 'YYYYMMDD')                                                    AS 시작일자
      , A.종목명, A.종목축약명, A.종목영문명, A.메모내용, A.등록일시, A.시장ID, A.증권그룹ID
FROM   (
        SELECT  /*+ NO_MERGE */
                ROWNUM                          AS 종목ID
              , A.OWNER || '/' || A.OBJECT_NAME AS 종목명
              , A.OBJECT_NAME                   AS 종목축약명
              , REVERSE(A.OBJECT_NAME)          AS 종목영문명
              , RPAD(A.OBJECT_NAME, 400, '0')   AS 메모내용
              , SYSDATE                         AS 등록일시
              , MOD(ROWNUM, 3) + 1              AS 시장ID
              , MOD(ROWNUM, 100) + 1            AS 증권그룹ID
        FROM    ALL_OBJECTS A
       ) A
      ,(
        SELECT  ROWNUM AS RNUM
        FROM    DUAL
        CONNECT BY
                LEVEL <= 10
       ) X
ORDER BY
        DBMS_RANDOM.RANDOM()
;

COMMIT;

CREATE UNIQUE INDEX 현물종목_PK ON 현물종목 (종목ID, 종료일자, 시작일자) NOLOGGING;
CREATE INDEX 현물종목_X01 ON 현물종목 (종목명, 종료일자, 시작일자, 종목ID) NOLOGGING;
CREATE INDEX 현물종목_X02 ON 현물종목 (시장ID, 증권그룹ID, 종료일자, 시작일자, 종목ID) NOLOGGING;

ANALYZE TABLE 현물종목 ESTIMATE STATISTICS;

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

▣  개선 전 SQL

SELECT  *
FROM    현물종목
WHERE  (종목ID , 종료일자 || 시작일자) IN
       (
        SELECT  A.종목ID
              , MAX(A.종료일자 || A.시작일자)
        FROM    현물종목 A
        WHERE   A.시장ID     = DECODE(:시장ID    , 0, A.시장ID    , :시장ID    )
        AND     A.증권그룹ID = DECODE(:증권그룹ID, 0, A.증권그룹ID, :증권그룹ID)
        AND     A.종목ID     = DECODE(:종목ID    , 0, A.종목ID    , :종목ID    )
        AND     A.종목명     = DECODE(:종목명    ,'*',A.종목명    , :종목명    )
        GROUP BY
                A.종목ID
       )

:시장ID     = 2
:증권그룹ID = 10
:종목ID     = 0
:종목명     = *

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        3    0.594        0.597          0      38132          0        165
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5    0.594        0.597          0      38132          0        165

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
    165   TABLE ACCESS BY INDEX ROWID 현물종목 (cr=38132 pr=0 pw=0 time=599574 us)
    331    NESTED LOOPS  (cr=37967 pr=0 pw=0 time=64762631 us)
    165     VIEW  VW_NSO_1 (cr=37624 pr=0 pw=0 time=590754 us)
    165      HASH GROUP BY (cr=37624 pr=0 pw=0 time=589756 us)
   1650       CONCATENATION  (cr=37624 pr=0 pw=0 time=727505 us)
   1650        FILTER  (cr=37624 pr=0 pw=0 time=715946 us)
   1650         TABLE ACCESS FULL 현물종목 (cr=37624 pr=0 pw=0 time=701083 us)
      0        FILTER  (cr=0 pr=0 pw=0 time=11 us)
      0         TABLE ACCESS BY INDEX ROWID 현물종목 (cr=0 pr=0 pw=0 time=0 us)
      0          INDEX RANGE SCAN 현물종목_PK (cr=0 pr=0 pw=0 time=0 us)OF 현물종목_PK (UNIQUE)
    165     INDEX RANGE SCAN 현물종목_PK (cr=343 pr=0 pw=0 time=3735 us)OF 현물종목_PK (UNIQUE)

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

▣  전제 조건

    1.  [:종목ID]와 [:종목명]을 모두 입력하지 않은 경우에는, [:시장ID]와 [:증권그룹ID]를 반드시 입력해야 함
    2.  즉, 최소 조회 조건은 [:시장ID] 변수와 [:증권그룹ID] 변수 2가지를 입력하는 것임

        ========================================================================
                    :종목ID     :종목명     :시장ID     :증권그룹ID
        ------------------------------------------------------------------------
        CASE 1      입력        미입력      선택        선택
        CASE 2      입력        입력        선택        선택
        CASE 3      미입력      입력        선택        선택
        CASE 4      미입력      미입력      입력        입력
        ========================================================================

▣  문제

    1.  개선 후 SQL을 제시하시오.
        *   조회 조건에 따라 실행계획을 분리할 것
        *   단, 인덱스 변경 불가

 

---

2번 실습문제

 

DROP TABLE 계좌 PURGE;

CREATE TABLE 계좌
(
    계좌번호        NUMBER          NOT NULL
  , 개설일자        VARCHAR2(8)     NOT NULL
  , 해지일자        VARCHAR2(8)     NOT NULL
  , 고객번호        NUMBER          NULL
  , 계좌관리부서ID  NUMBER          NOT NULL
  , 계좌관리사원ID  NUMBER          NOT NULL
  , 계좌설명        VARCHAR2(1000)  NULL
  , 이체한도금액    NUMBER          NULL
);

DROP SEQUENCE 계좌_SEQ;

CREATE SEQUENCE 계좌_SEQ CACHE 10000;

INSERT  /*+ APPEND */
INTO    계좌
SELECT  계좌_SEQ.NEXTVAL                                    AS 계좌번호
      , TO_CHAR(SYSDATE - (ROWNUM / 1000) - 10, 'YYYYMMDD') AS 개설일자
      , CASE WHEN MOD(ROWNUM, 100) = 1 THEN
             TO_CHAR(SYSDATE - (ROWNUM / 1000), 'YYYYMMDD')
             ELSE '99991231'
        END                                                 AS 해지일자
      , ROUND(DBMS_RANDOM.VALUE(1, 500000))                 AS 고객번호
      , MOD(ROWNUM, 10) + 1                                 AS 계좌관리부서ID
      , MOD(ROWNUM, 500) + 1                                AS 계좌관리사원ID
      , RPAD(A.OBJECT_NAME, 400, '0')                       AS 계좌설명
      , 100000000                                           AS 이체한도금액
FROM   (
        SELECT  /*+ NO_MERGE */
                *
        FROM    ALL_OBJECTS
       ) A
      ,(
        SELECT  ROWNUM AS RNUM
        FROM    DUAL
        CONNECT BY
                LEVEL <= 100
       ) X
WHERE   ROWNUM <= 500000;

COMMIT;

CREATE UNIQUE INDEX 계좌_PK ON 계좌 (계좌번호) NOLOGGING PARALLEL 2;
ALTER INDEX 계좌_PK NOPARALLEL;

ANALYZE TABLE 계좌 ESTIMATE STATISTICS;

/******************************************************************************/

DROP TABLE 계좌별약정 PURGE;

CREATE TABLE 계좌별약정
(
    계좌번호        NUMBER          NOT NULL
  , 일련번호        NUMBER          NOT NULL
  , 약정코드        VARCHAR2(2)     NOT NULL
  , 약정가입일자    VARCHAR2(8)     NOT NULL
  , 약정해지일자    VARCHAR2(8)     NOT NULL
  , 약정금액        NUMBER          NULL
  , 약정내용설명    VARCHAR2(1000)  NULL
);

INSERT  /*+ APPEND */
INTO    계좌별약정
SELECT  A.계좌번호                                      AS 계좌번호
      , X.RNUM                                          AS 일련번호
      , X.약정코드                                      AS 약정코드
      , TO_CHAR(TO_DATE(A.개설일자, 'YYYYMMDD') + ROUND(DBMS_RANDOM.VALUE(1, 5)), 'YYYYMMDD')
                                                        AS 약정가입일자
      , CASE WHEN A.개설일자 = '99991231' AND MOD(ROWNUM, 100) <> 0 THEN '99991231'
             ELSE TO_CHAR(TO_DATE(A.개설일자, 'YYYYMMDD') + ROUND(DBMS_RANDOM.VALUE(6, 10)), 'YYYYMMDD')
        END                                             AS 약정해지일자
      , 50000000 * X.RNUM                               AS 약정금액
      , SUBSTR(A.계좌설명, 1, 100) || X.RNUM            AS 약정내용설명
FROM    계좌 A
      ,(
        SELECT  ROWNUM                      AS RNUM
              , DBMS_RANDOM.STRING('U', 1)  AS 약정코드
        FROM    DUAL
        CONNECT BY
                LEVEL <= 4
       ) X
;

COMMIT;

CREATE UNIQUE INDEX 계좌별약정_PK ON 계좌별약정 (계좌번호 , 일련번호) NOLOGGING PARALLEL 2;
CREATE INDEX 계좌별약정_X01 ON 계좌별약정 (약정가입일자) NOLOGGING PARALLEL 2;

ALTER INDEX 계좌별약정_PK NOPARALLEL;
ALTER INDEX 계좌별약정_X01 NOPARALLEL;

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

▣  개선 전 SQL

SELECT  A.*
FROM   (
        SELECT  /*+ LEADING(A) USE_NL(B) INDEX(A, 계좌별약정_X01) */
                A.*, B.계좌관리부서ID, B.개설일자, B.해지일자
        FROM    계좌별약정  A
              , 계좌        B
        WHERE   A.약정가입일자 >= TO_CHAR(SYSDATE - 30, 'YYYYMMDD')
        AND     A.계좌번호 = B.계좌번호
        ORDER BY
                A.약정가입일자, B.계좌관리부서ID, A.계좌번호
       ) A
WHERE   ROWNUM <= 11

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.141        0.143          0        174          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    5.031       14.119       3644     289569          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    5.172       14.262       3644     289743          0         11

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     11   COUNT STOPKEY (cr=289569 pr=3644 pw=0 time=14119124 us)
     11    VIEW  (cr=289569 pr=3644 pw=0 time=14119104 us)
     11     SORT ORDER BY STOPKEY (cr=289569 pr=3644 pw=0 time=14119085 us)
  93416      NESTED LOOPS  (cr=289569 pr=3644 pw=0 time=17257373 us)
  93416       TABLE ACCESS BY INDEX ROWID 계좌별약정 (cr=9319 pr=1983 pw=0 time=7030875 us)
  93416        INDEX RANGE SCAN 계좌별약정_X01 (cr=264 pr=260 pw=0 time=470477 us)OF 계좌별약정_X01 (NONUNIQUE)
  93416       TABLE ACCESS BY INDEX ROWID 계좌 (cr=280250 pr=1661 pw=0 time=7594022 us)
  93416        INDEX UNIQUE SCAN 계좌_PK (cr=186834 pr=64 pw=0 time=1874522 us)OF 계좌_PK (UNIQUE)

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

▣  전제 조건

    1.  위 SQL은 수행빈도가 가장 높은 SQL 중 하나임
    2.  해당 SQL이 최적으로 수행되도록, 물리데이터모델을 개선할 수 있음

▣  문제

    1.  개선 후 SQL을 제시하시오.
        *   인덱스 추가/변경/삭제 가능함

 

SQLP 자격검정 실전문제 풀이

 

2022.04.05 - [IT/SQLP] - [SQLP] 문제집 풀이 ( 1-3 )

[SQLP] 문제집 풀이 ( 1-3 )