세상을 시뮬이이션하고픈 실천가

ROP TABLE 종목별거래상세 PURGE;

CREATE TABLE 종목별거래상세
(
종목코드 VARCHAR2(5) NOT NULL
, 거래번호 NUMBER NOT NULL
, 대량거래유형코드 VARCHAR2(1) NOT NULL
, 거래일자 VARCHAR2(8) NOT NULL
, 시작가격 NUMBER
, 종료가격 NUMBER
, 거래수량 NUMBER
, 회원번호 VARCHAR2(10)
, 계좌번호 VARCHAR2(20)
, 적요 VARCHAR2(1000)
)
PARTITION BY RANGE (거래일자)
(
PARTITION PR_20160501 VALUES LESS THAN ('20160502')
, PARTITION PR_20160502 VALUES LESS THAN ('20160503')
, PARTITION PR_20160503 VALUES LESS THAN ('20160504')
, PARTITION PR_20160504 VALUES LESS THAN ('20160505')
, PARTITION PR_20160505 VALUES LESS THAN ('20160506')
, PARTITION PR_20160506 VALUES LESS THAN ('20160507')
, PARTITION PR_20160507 VALUES LESS THAN ('20160508')
, PARTITION PR_20160508 VALUES LESS THAN ('20160509')
, PARTITION PR_20160509 VALUES LESS THAN ('20160510')
, PARTITION PR_20160510 VALUES LESS THAN ('20160511')
, PARTITION PR_20160511 VALUES LESS THAN ('20160512')
, PARTITION PR_20160512 VALUES LESS THAN ('20160513')
, PARTITION PR_20160513 VALUES LESS THAN ('20160514')
, PARTITION PR_20160514 VALUES LESS THAN ('20160515')
, PARTITION PR_20160515 VALUES LESS THAN ('20160516')
, PARTITION PR_20160516 VALUES LESS THAN ('20160517')
, PARTITION PR_20160517 VALUES LESS THAN ('20160518')
, PARTITION PR_20160518 VALUES LESS THAN ('20160519')
, PARTITION PR_20160519 VALUES LESS THAN ('20160520')
, PARTITION PR_20160520 VALUES LESS THAN ('20160521')
, PARTITION PR_20160521 VALUES LESS THAN ('20160522')
, PARTITION PR_20160522 VALUES LESS THAN ('20160523')
, PARTITION PR_20160523 VALUES LESS THAN ('20160524')
, PARTITION PR_20160524 VALUES LESS THAN ('20160525')
, PARTITION PR_20160525 VALUES LESS THAN ('20160526')
, PARTITION PR_20160526 VALUES LESS THAN ('20160527')
, PARTITION PR_20160527 VALUES LESS THAN ('20160528')
, PARTITION PR_20160528 VALUES LESS THAN ('20160529')
, PARTITION PR_20160529 VALUES LESS THAN ('20160530')
, PARTITION PR_20160530 VALUES LESS THAN ('20160531')
, PARTITION PR_20160531 VALUES LESS THAN ('201606')
, PARTITION PR_99991231 VALUES LESS THAN ( MAXVALUE )
);

DECLARE
l_거래일자 VARCHAR2(8);
BEGIN
FOR i IN 0..2 LOOP

SELECT TO_CHAR(TO_DATE('20160501','YYYYMMDD') + i, 'YYYYMMDD')
INTO l_거래일자
FROM DUAL;

INSERT
INTO 종목별거래상세
SELECT A.종목코드
, B.거래번호
,(CASE WHEN MOD(ROWNUM, 10000) = 0 THEN 'X' ELSE 'A' END) AS 대량거래유형코드
, l_거래일자
, ROWNUM AS 시작가격
, ROWNUM + 1 AS 종료가격
, ROWNUM * 10 AS 거래수량
, LPAD(ROWNUM, 10, '0') AS 회원번호
, LPAD(ROWNUM, 20, '0') AS 계좌번호
, LPAD(ROWNUM, 200, '0') AS 적요
FROM (
SELECT 'A' || LPAD(ROWNUM, 4, '0') AS 종목코드
FROM DUAL
CONNECT BY
LEVEL <= 250
UNION ALL
SELECT 'B' || LPAD(ROWNUM, 4, '0') AS 종목코드
FROM DUAL
CONNECT BY
LEVEL <= 250
) A
,(
SELECT ROWNUM AS 거래번호
FROM DUAL
CONNECT BY
LEVEL <= 1000
) B
ORDER BY
DBMS_RANDOM.RANDOM();

COMMIT;

END LOOP;
END;
/

CREATE UNIQUE INDEX 종목별거래상세_PK ON 종목별거래상세 (종목코드, 거래번호, 대량거래유형코드, 거래일자) LOCAL UNUSABLE;

ALTER INDEX 종목별거래상세_PK REBUILD PARTITION PR_20160501 PARALLEL 2 NOLOGGING;
ALTER INDEX 종목별거래상세_PK REBUILD PARTITION PR_20160502 PARALLEL 2 NOLOGGING;
ALTER INDEX 종목별거래상세_PK REBUILD PARTITION PR_20160503 PARALLEL 2 NOLOGGING;

ALTER INDEX 종목별거래상세_PK NOPARALLEL;


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

▣ 개선 전 SQL

SELECT *
FROM 종목별거래상세 A
WHERE 거래일자 = '20160501'
AND 대량거래유형코드 = 'X'
ORDER BY
거래번호, 종목코드;


50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 55875824

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

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

| 0 | SELECT STATEMENT | | 49 | 28665 | 5988 (13)| 00:01:12 | | |

| 1 | SORT ORDER BY | | 49 | 28665 | 5988 (13)| 00:01:12 | | |

| 2 | PARTITION RANGE SINGLE| | 49 | 28665 | 5987 (13)| 00:01:12 | 1 | 1 |

|* 3 | TABLE ACCESS FULL | 종목별거| 49 | 28665 | 5987 (13)| 00:01:12 | 1 | 1 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("거래일자"='20160501' AND "대량거래유형코드"='X')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19242 consistent gets
0 physical reads
0 redo size
14501 bytes sent via SQL*Net to client
553 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50 rows processed

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

▣ 전제 조건
1. 대량거래유형코드 = 'X' 조건에 해당하는 데이터는 극소수임
* 일별 데이터 50만 건 중 수십 건 이하임


▣ 문제
1. 개선 후 SQL을 제시하시오.
* 단, 인덱스 변경 불가

2. 오늘 데이터를 포함하여 과거 1달 이상의 넓은 범위를 액세스하는 SQL을 효율적으로 실행할 수 있는 솔루션을 제시하시오.
* 단, 과거 데이터에는 변경이 발생하지 않음
* 종목별거래상세 테이블 및 인덱스 구조를 변경할 수 없음
* 대신, 다른 오브젝트를 마음대로 추가/변경/삭제할 수 있음