-- 아래는 실기2번과 비슷한 유형으로 중요하다고 생각되는 부분만 재현한 쿼리임
/*
SALES_P -> 월별 파티션테이블
ORDER_DATE VARCHAR(8)
*/
CREATE TABLE SALES_P(
ORDER_DATE VARCHAR2(8),
ORDER_MODE VARCHAR2(6),
CUSTOMER_ID VARCHAR2(21),
EMPLOYEE_ID VARCHAR2(13),
PRODUCT_ID VARCHAR2(13),
ORDER_QTY NUMBER,
ORDER_AMT NUMBER
)
PARTITION BY RANGE(ORDER_DATE) (
PARTITION P1 VALUES LESS THAN (DATE '2012-02-01') ,
PARTITION P2 VALUES LESS THAN (DATE '2012-03-01') ,
PARTITION P3 VALUES LESS THAN (DATE '2012-04-01') ,
PARTITION P4 VALUES LESS THAN (DATE '2012-05-01') ,
PARTITION P5 VALUES LESS THAN (DATE '2012-06-01') ,
PARTITION P6 VALUES LESS THAN (DATE '2012-07-01') ,
PARTITION P7 VALUES LESS THAN (DATE '2012-08-01') ,
PARTITION P8 VALUES LESS THAN (DATE '2012-09-01') ,
PARTITION P9 VALUES LESS THAN (DATE '2012-10-01') ,
PARTITION P10 VALUES LESS THAN (DATE '2012-11-01') ,
PARTITION P11 VALUES LESS THAN (DATE '2012-12-01') ,
PARTITION PMAX VALUES LESS THAN (MAXVALUE)
) ;
SELECT '일별' 구분
, ORDER_DATE ORDATE
, SUM(ORDER_AMT) ORAMT
FROM SALES_P
WHERE ORDER_DATE BETWEEN 20120301 AND 20120331
GROUP BY ORDER_DATE
UNION ALL
SELECT '월별' 구분
, SUBSTR(ORDER_DATE, 1, 6) ORDATE
, SUM(ORDER_AMT) ORAMT
FROM SALES_P
WHERE ORDER_DATE BETWEEN 20120301 AND 20120331
GROUP BY SUBSTR(ORDER_DATE, 1, 6)
ORDER BY 1, 2
; -- 32
SQL_ID 21ndvm228700r, child number 1
-------------------------------------
Plan hash value: 230622046
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4392 (100)| | | | 32 |00:00:00.30 | 15652 | | | |
| 1 | SORT ORDER BY | | 1 | 32 | 608 | 4391 (1)| 00:00:01 | | | 32 |00:00:00.30 | 15652 | 2048 | 2048 | 2048 (0)|
| 2 | UNION-ALL | | 1 | | | | | | | 32 |00:00:00.30 | 15652 | | | |
| 3 | PARTITION RANGE ALL | | 1 | 31 | 589 | 2196 (1)| 00:00:01 | 1 | 12 | 31 |00:00:00.15 | 7826 | | | |
| 4 | HASH GROUP BY | | 12 | 31 | 589 | 2196 (1)| 00:00:01 | | | 31 |00:00:00.15 | 7826 | 1214K| 1214K| 1374K (0)|
|* 5 | TABLE ACCESS FULL | SALES_P | 12 | 104K| 1936K| 2191 (1)| 00:00:01 | 1 | 12 | 104K|00:00:00.14 | 7826 | | | |
| 6 | HASH GROUP BY | | 1 | 1 | 19 | 2196 (1)| 00:00:01 | | | 1 |00:00:00.15 | 7826 | 1160K| 1160K| 504K (0)|
| 7 | PARTITION RANGE ALL| | 1 | 104K| 1936K| 2191 (1)| 00:00:01 | 1 | 12 | 104K|00:00:00.14 | 7826 | | | |
|* 8 | TABLE ACCESS FULL | SALES_P | 12 | 104K| 1936K| 2191 (1)| 00:00:01 | 1 | 12 | 104K|00:00:00.14 | 7826 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter((TO_NUMBER("ORDER_DATE")>=20120301 AND TO_NUMBER("ORDER_DATE")<=20120331))
8 - filter((TO_NUMBER("ORDER_DATE")>=20120301 AND TO_NUMBER("ORDER_DATE")<=20120331))
SELECT DECODE(B.NO, 1 ,'일별', 2, '월별') 구분
, DECODE(B.NO, 1, ORDER_DATE, 2, SUBSTR(ORDER_DATE, 1, 6) ) ORDATE
, SUM(ORDER_AMT) ORDER_AMT
FROM (
SELECT /*+ FULL(S)*/ORDER_DATE
, ORDER_AMT
FROM SALES_P S
WHERE ORDER_DATE BETWEEN '20120301' AND '20120331' --> 형변환되지 않게 문자열처리 (날짜가 varchar2 타입이므로) -> 파티션푸루닝 유도
) A , ( SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2) B --> 같은테이블을 반복으로 읽지않기 위해 복제이용)
GROUP BY DECODE(B.NO, 1 ,'일별', 2, '월별')
, DECODE(B.NO, 1, ORDER_DATE, 2, SUBSTR(ORDER_DATE, 1, 6) )
ORDER BY 1, 2
; -- 32
SQL_ID 21ndvm228700r, child number 1
-------------------------------------
Plan hash value: 230622046
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4392 (100)| | | | 32 |00:00:00.30 | 15652 | | | |
| 1 | SORT ORDER BY | | 1 | 32 | 608 | 4391 (1)| 00:00:01 | | | 32 |00:00:00.30 | 15652 | 2048 | 2048 | 2048 (0)|
| 2 | UNION-ALL | | 1 | | | | | | | 32 |00:00:00.30 | 15652 | | | |
| 3 | PARTITION RANGE ALL | | 1 | 31 | 589 | 2196 (1)| 00:00:01 | 1 | 12 | 31 |00:00:00.15 | 7826 | | | |
| 4 | HASH GROUP BY | | 12 | 31 | 589 | 2196 (1)| 00:00:01 | | | 31 |00:00:00.15 | 7826 | 1214K| 1214K| 1374K (0)|
|* 5 | TABLE ACCESS FULL | SALES_P | 12 | 104K| 1936K| 2191 (1)| 00:00:01 | 1 | 12 | 104K|00:00:00.14 | 7826 | | | |
| 6 | HASH GROUP BY | | 1 | 1 | 19 | 2196 (1)| 00:00:01 | | | 1 |00:00:00.15 | 7826 | 1160K| 1160K| 504K (0)|
| 7 | PARTITION RANGE ALL| | 1 | 104K| 1936K| 2191 (1)| 00:00:01 | 1 | 12 | 104K|00:00:00.14 | 7826 | | | |
|* 8 | TABLE ACCESS FULL | SALES_P | 12 | 104K| 1936K| 2191 (1)| 00:00:01 | 1 | 12 | 104K|00:00:00.14 | 7826 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Oracle' 카테고리의 다른 글
| ITL Wait 재현 (0) | 2021.05.16 |
|---|---|
| SQLD, SQLP 자격 접수 시작 (0) | 2021.05.04 |
| SQLD 자격검정 핵심노트 (0) | 2020.12.29 |
| SQL 선택적 조인으로 튜닝하기 (0) | 2020.12.28 |
| oracle-base > index-monitoring (0) | 2020.12.04 |