클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
로우 패턴매칭
- 데이터의 특정 패턴이 존재할 경우, 이를 검색해 줌
- 일/월/분기별 등 특정 기간 동안의 데이터 추이에서 특정 패턴 검색에 유리함(예 : 주식시황, 기간별 매출추이 등)
- 참고URL : https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1
DROP TABLE sales_history PURGE;
CREATE TABLE sales_history (
id NUMBER,
product VARCHAR2(20),
tstamp TIMESTAMP,
units_sold NUMBER,
CONSTRAINT sales_history_pk PRIMARY KEY (id)
);
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';
INSERT INTO sales_history VALUES ( 1, 'TWINKIES', '01-OCT-2014', 17);
INSERT INTO sales_history VALUES ( 2, 'TWINKIES', '02-OCT-2014', 19);
INSERT INTO sales_history VALUES ( 3, 'TWINKIES', '03-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 4, 'TWINKIES', '04-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 5, 'TWINKIES', '05-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 6, 'TWINKIES', '06-OCT-2014', 10);
INSERT INTO sales_history VALUES ( 7, 'TWINKIES', '07-OCT-2014', 14);
INSERT INTO sales_history VALUES ( 8, 'TWINKIES', '08-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 9, 'TWINKIES', '09-OCT-2014', 15);
INSERT INTO sales_history VALUES (10, 'TWINKIES', '10-OCT-2014', 17);
INSERT INTO sales_history VALUES (11, 'TWINKIES', '11-OCT-2014', 23);
INSERT INTO sales_history VALUES (12, 'TWINKIES', '12-OCT-2014', 30);
INSERT INTO sales_history VALUES (13, 'TWINKIES', '13-OCT-2014', 31);
INSERT INTO sales_history VALUES (14, 'TWINKIES', '14-OCT-2014', 29);
INSERT INTO sales_history VALUES (15, 'TWINKIES', '15-OCT-2014', 25);
INSERT INTO sales_history VALUES (16, 'TWINKIES', '16-OCT-2014', 21);
INSERT INTO sales_history VALUES (17, 'TWINKIES', '17-OCT-2014', 35);
INSERT INTO sales_history VALUES (18, 'TWINKIES', '18-OCT-2014', 46);
INSERT INTO sales_history VALUES (19, 'TWINKIES', '19-OCT-2014', 45);
INSERT INTO sales_history VALUES (20, 'TWINKIES', '20-OCT-2014', 30);
COMMIT;
-- 한국인 입장으로 다시 표기
ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD';
SET PAGESIZE 50
COLUMN product FORMAT A10
COLUMN tstamp FORMAT A11
COLUMN graph FORMAT A50
-- 데이터를 출력한다.
SELECT id,
product,
tstamp,
units_sold,
RPAD('#', units_sold, '#') AS graph
FROM sales_history
ORDER BY id;
ID PRODUCT TSTAMP UNITS_SOLD GRAPH
---------- ---------- ----------- ---------- --------------------------------------------------
1 TWINKIES 2014-10-01 17 #################
2 TWINKIES 2014-10-02 19 ###################
3 TWINKIES 2014-10-03 23 #######################
4 TWINKIES 2014-10-04 23 #######################
5 TWINKIES 2014-10-05 16 ################
6 TWINKIES 2014-10-06 10 ##########
7 TWINKIES 2014-10-07 14 ##############
8 TWINKIES 2014-10-08 16 ################
9 TWINKIES 2014-10-09 15 ###############
10 TWINKIES 2014-10-10 17 #################
11 TWINKIES 2014-10-11 23 #######################
12 TWINKIES 2014-10-12 30 ##############################
13 TWINKIES 2014-10-13 31 ###############################
14 TWINKIES 2014-10-14 29 #############################
15 TWINKIES 2014-10-15 25 #########################
16 TWINKIES 2014-10-16 21 #####################
17 TWINKIES 2014-10-17 35 ###################################
18 TWINKIES 2014-10-18 46 ##############################################
19 TWINKIES 2014-10-19 45 #############################################
20 TWINKIES 2014-10-20 30 ##############################
20 rows selected.
COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp FORMAT A11
COLUMN end_tstamp FORMAT A11
SELECT *
FROM sales_history MATCH_RECOGNIZE (
PARTITION BY product -- 데이터의 그룹화 과정, 여기서는 product 별로 패턴을 찾기로 함
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp, -- 패턴매칭으로 가져올 컬럼명을 새로 지정함
LAST(UP.tstamp) AS peak_tstamp,
LAST(DOWN.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno -- 패턴을 만족한 개수
ONE ROW PER MATCH -- 패턴을 만족하는 ROW 1개만 보여줌(중복제거 효과), ALL ROWS : 패턴을 만족하는 과정의 모든 ROW를 보여줌
AFTER MATCH SKIP TO LAST DOWN -- 현재 패턴을 만족 하는 마지막 DOWN 시작점부터 다시 패턴을 찾기 시작함
PATTERN (STRT UP+ FLAT* DOWN+) -- 패턴을 정의 : UP이 1개 이상, FLAT은 0개 이상, DOWN은 1개 이상인 경우
DEFINE -- 패턴의 변수를 정의 : STRT의 경우 하단에 정의하지 않았으므로 모든 row에 매칭된다.
UP AS UP.units_sold > PREV(UP.units_sold), -- UP : 이전 units_sold 보다 큰 경우
FLAT AS FLAT.units_sold = PREV(FLAT.units_sold), -- FLAT : 이전 units_sold와 같은 경우
DOWN AS DOWN.units_sold < PREV(DOWN.units_sold) -- DOWN : 이전 units_sold와 작은 경우
) MR
ORDER BY MR.product, MR.start_tstamp;
PRODUCT START_TSTAM PEAK_TSTAMP END_TSTAMP MNO
---------- ----------- ----------- ----------- ----------
TWINKIES 01-OCT-2014 03-OCT-2014 06-OCT-2014 1
TWINKIES 06-OCT-2014 08-OCT-2014 09-OCT-2014 2
TWINKIES 09-OCT-2014 13-OCT-2014 16-OCT-2014 3
TWINKIES 16-OCT-2014 18-OCT-2014 20-OCT-2014 4
4 rows selected.
-- ONE ROW PER MATCH를 ALL ROWS PER MATCH로 변경
COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp FORMAT A11
COLUMN end_tstamp FORMAT A11
COLUMN final_peak_tstamp FORMAT A11
COLUMN final_end_tstamp FORMAT A11
COLUMN cls FORMAT A5
SELECT *
FROM sales_history MATCH_RECOGNIZE (
PARTITION BY product
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
LAST(UP.tstamp) AS peak_tstamp,
LAST(DOWN.tstamp) AS end_tstamp,
FINAL LAST(UP.tstamp) AS final_peak_tstamp, -- Final : 가장 마지막 패턴 매칭값이 나옴
FINAL LAST(DOWN.tstamp) AS final_end_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls -- 분류자(패턴 정의시 사용한 변수)
ALL ROWS PER MATCH -- 패턴을 만족하는 모든 ROW가 나타남
AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT UP+ FLAT* DOWN+)
DEFINE
UP AS UP.units_sold > PREV(UP.units_sold),
DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
) MR
ORDER BY MR.product, MR.mno, MR.tstamp;
PRODUCT TSTAMP START_TSTAM PEAK_TSTAMP END_TSTAMP FINAL_PEAK_ FINAL_END_T MNO CLS ID UNITS_SOLD
---------- -------------------- ----------- ----------- ----------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES 2014-10-01 2014-10-01 2014-10-03 2014-10-06 1 STRT 1 17
TWINKIES 2014-10-02 2014-10-01 2014-10-02 2014-10-03 2014-10-06 1 UP 2 19
TWINKIES 2014-10-03 2014-10-01 2014-10-03 2014-10-03 2014-10-06 1 UP 3 23
TWINKIES 2014-10-04 2014-10-01 2014-10-03 2014-10-03 2014-10-06 1 FLAT 4 23
TWINKIES 2014-10-05 2014-10-01 2014-10-03 2014-10-05 2014-10-03 2014-10-06 1 DOWN 5 16
TWINKIES 2014-10-06 2014-10-01 2014-10-03 2014-10-06 2014-10-03 2014-10-06 1 DOWN 6 10
TWINKIES 2014-10-07 2014-10-07 2014-10-08 2014-10-09 2 STRT 7 14
TWINKIES 2014-10-08 2014-10-07 2014-10-08 2014-10-08 2014-10-09 2 UP 8 16
TWINKIES 2014-10-09 2014-10-07 2014-10-08 2014-10-09 2014-10-08 2014-10-09 2 DOWN 9 15
TWINKIES 2014-10-10 2014-10-10 2014-10-13 2014-10-16 3 STRT 10 17
TWINKIES 2014-10-11 2014-10-10 2014-10-11 2014-10-13 2014-10-16 3 UP 11 23
TWINKIES 2014-10-12 2014-10-10 2014-10-12 2014-10-13 2014-10-16 3 UP 12 30
TWINKIES 2014-10-13 2014-10-10 2014-10-13 2014-10-13 2014-10-16 3 UP 13 31
TWINKIES 2014-10-14 2014-10-10 2014-10-13 2014-10-14 2014-10-13 2014-10-16 3 DOWN 14 29
TWINKIES 2014-10-15 2014-10-10 2014-10-13 2014-10-15 2014-10-13 2014-10-16 3 DOWN 15 25
TWINKIES 2014-10-16 2014-10-10 2014-10-13 2014-10-16 2014-10-13 2014-10-16 3 DOWN 16 21
TWINKIES 2014-10-17 2014-10-17 2014-10-18 2014-10-20 4 STRT 17 35
TWINKIES 2014-10-18 2014-10-17 2014-10-18 2014-10-18 2014-10-20 4 UP 18 46
TWINKIES 2014-10-19 2014-10-17 2014-10-18 2014-10-19 2014-10-18 2014-10-20 4 DOWN 19 45
TWINKIES 2014-10-20 2014-10-17 2014-10-18 2014-10-20 2014-10-18 2014-10-20 4 DOWN 20 30
SELECT *
FROM sales_history MATCH_RECOGNIZE (
PARTITION BY product
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
FINAL LAST(UP.tstamp) AS peak_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT UP+ DOWN{1} UP+) -- 패턴 : UP 1회 이상, DOWN 1회, UP 1회 이상
DEFINE
UP AS UP.units_sold > PREV(UP.units_sold),
DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
) MR
ORDER BY MR.product, MR.tstamp;
PRODUCT TSTAMP START_TSTAM PEAK_TSTAMP MNO CLS ID UNITS_SOLD
---------- -------------------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES 2014-10-06 2014-10-06 2014-10-13 1 STRT 6 10
TWINKIES 2014-10-07 2014-10-06 2014-10-13 1 UP 7 14
TWINKIES 2014-10-08 2014-10-06 2014-10-13 1 UP 8 16
TWINKIES 2014-10-09 2014-10-06 2014-10-13 1 DOWN 9 15
TWINKIES 2014-10-10 2014-10-06 2014-10-13 1 UP 10 17
TWINKIES 2014-10-11 2014-10-06 2014-10-13 1 UP 11 23
TWINKIES 2014-10-12 2014-10-06 2014-10-13 1 UP 12 30
TWINKIES 2014-10-13 2014-10-06 2014-10-13 1 UP 13 31
8 rows selected.
-- 패턴 매칭을 시작점을 정의
-- AFTER MATCH SKIP TO NEXT ROW : 패턴을 만족시키는 ROW 바로 뒤 ROW부터 다시 패턴매칭을 시작함
-- AFTER MATCH SKIP PAST LAST ROW : 현재 패턴을 만족시키는 마지막 ROW 부터 패턴매칭을 다시 시작함
-- AFTER MATCH SKIP TO FIRST pattern_variable : 첫번째 pattern_variable 부터 다시 패턴매칭을 시작함
-- AFTER MATCH SKIP TO LAST pattern_variable : 마지막 pattern_variable 부터 다시 패턴매칭을 시작함
-- AFTER MATCH SKIP TO pattern_variable : AFTER MATCH SKIP TO LAST pattern_variable 와 동일 기능
SELECT *
FROM sales_history MATCH_RECOGNIZE (
PARTITION BY product
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
LAST(UP.tstamp) AS peak_tstamp,
LAST(DOWN.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW -- 패턴을 만족시키는 ROW 바로 뒤 ROW부터 다시 패턴매칭을 시작함
PATTERN (STRT UP+ FLAT* DOWN+)
DEFINE
UP AS UP.units_sold > PREV(UP.units_sold),
FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
) MR
ORDER BY MR.product, MR.start_tstamp;
PRODUCT START_TSTAM PEAK_TSTAMP END_TSTAMP MNO
---------- ----------- ----------- ----------- ----------
TWINKIES 2014-10-01 2014-10-03 2014-10-06 1
TWINKIES 2014-10-02 2014-10-03 2014-10-06 2
TWINKIES 2014-10-06 2014-10-08 2014-10-09 3
TWINKIES 2014-10-07 2014-10-08 2014-10-09 4
TWINKIES 2014-10-09 2014-10-13 2014-10-16 5
TWINKIES 2014-10-10 2014-10-13 2014-10-16 6
TWINKIES 2014-10-11 2014-10-13 2014-10-16 7
TWINKIES 2014-10-12 2014-10-13 2014-10-16 8
TWINKIES 2014-10-16 2014-10-18 2014-10-20 9
TWINKIES 2014-10-17 2014-10-18 2014-10-20 10
10 rows selected.
-- 새로운 형태의 데이터 생성
CREATE TABLE sales_audit (
id NUMBER,
product VARCHAR2(20),
tstamp TIMESTAMP,
CONSTRAINT sales_audit_pk PRIMARY KEY (id)
);
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';
INSERT INTO sales_audit VALUES ( 1, 'TWINKIES', '01-OCT-2014 12:00:01');
INSERT INTO sales_audit VALUES ( 2, 'TWINKIES', '01-OCT-2014 12:00:02');
INSERT INTO sales_audit VALUES ( 3, 'DINGDONGS', '01-OCT-2014 12:00:03');
INSERT INTO sales_audit VALUES ( 4, 'HOHOS', '01-OCT-2014 12:00:04');
INSERT INTO sales_audit VALUES ( 5, 'HOHOS', '01-OCT-2014 12:00:05');
INSERT INTO sales_audit VALUES ( 6, 'TWINKIES', '01-OCT-2014 12:00:06');
INSERT INTO sales_audit VALUES ( 7, 'TWINKIES', '01-OCT-2014 12:00:07');
INSERT INTO sales_audit VALUES ( 8, 'DINGDONGS', '01-OCT-2014 12:00:08');
INSERT INTO sales_audit VALUES ( 9, 'DINGDONGS', '01-OCT-2014 12:00:09');
INSERT INTO sales_audit VALUES (10, 'HOHOS', '01-OCT-2014 12:00:10');
INSERT INTO sales_audit VALUES (11, 'HOHOS', '01-OCT-2014 12:00:11');
INSERT INTO sales_audit VALUES (12, 'TWINKIES', '01-OCT-2014 12:00:12');
INSERT INTO sales_audit VALUES (13, 'TWINKIES', '01-OCT-2014 12:00:13');
INSERT INTO sales_audit VALUES (14, 'DINGDONGS', '01-OCT-2014 12:00:14');
INSERT INTO sales_audit VALUES (15, 'DINGDONGS', '01-OCT-2014 12:00:15');
INSERT INTO sales_audit VALUES (16, 'HOHOS', '01-OCT-2014 12:00:16');
INSERT INTO sales_audit VALUES (17, 'TWINKIES', '01-OCT-2014 12:00:17');
INSERT INTO sales_audit VALUES (18, 'TWINKIES', '01-OCT-2014 12:00:18');
INSERT INTO sales_audit VALUES (19, 'TWINKIES', '01-OCT-2014 12:00:19');
INSERT INTO sales_audit VALUES (20, 'TWINKIES', '01-OCT-2014 12:00:20');
COMMIT;
COLUMN tstamp FORMAT A20
COLUMN start_tstamp FORMAT A20
COLUMN end_tstamp FORMAT A20
COLUMN cls FORMAT A10
SELECT *
FROM sales_audit MATCH_RECOGNIZE (
--PARTITION BY product -- 생략 가능함
ORDER BY tstamp -- 생략 가능함
MEASURES FIRST(TWINKIES.tstamp) AS start_tstamp,
FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST TWINKIES
PATTERN(TWINKIES{2,} DINGDONGS{2} HOHOS{1} TWINKIES{3,}) -- TWINKIES가 2번 이상, DINGDONGS가 2번, HOHOS가 1번, TWINKIES가 3번 이상
DEFINE
TWINKIES AS TWINKIES.product='TWINKIES',
DINGDONGS AS DINGDONGS.product='DINGDONGS',
HOHOS AS HOHOS.product='HOHOS'
) MR
ORDER BY MR.mno, MR.tstamp;
TSTAMP START_TSTAMP END_TSTAMP MNO CLS ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
2014-10-01 12:00:12 2014-10-01 12:00:12 2014-10-01 12:00:20 1 TWINKIES 12 TWINKIES
2014-10-01 12:00:13 2014-10-01 12:00:12 2014-10-01 12:00:20 1 TWINKIES 13 TWINKIES
2014-10-01 12:00:14 2014-10-01 12:00:12 2014-10-01 12:00:20 1 DINGDONGS 14 DINGDONGS
2014-10-01 12:00:15 2014-10-01 12:00:12 2014-10-01 12:00:20 1 DINGDONGS 15 DINGDONGS
2014-10-01 12:00:16 2014-10-01 12:00:12 2014-10-01 12:00:20 1 HOHOS 16 HOHOS
2014-10-01 12:00:17 2014-10-01 12:00:12 2014-10-01 12:00:20 1 TWINKIES 17 TWINKIES
2014-10-01 12:00:18 2014-10-01 12:00:12 2014-10-01 12:00:20 1 TWINKIES 18 TWINKIES
2014-10-01 12:00:19 2014-10-01 12:00:12 2014-10-01 12:00:20 1 TWINKIES 19 TWINKIES
2014-10-01 12:00:20 2014-10-01 12:00:12 2014-10-01 12:00:20 1 TWINKIES 20 TWINKIES
SELECT *
FROM sales_audit MATCH_RECOGNIZE (
--PARTITION BY product
ORDER BY tstamp
MEASURES FIRST(TWINKIES.tstamp) AS start_tstamp,
FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST TWINKIES
PATTERN(TWINKIES+ (DINGDONGS | HOHOS){3} TWINKIES+) -- TWINKIES 1번 이상, DINGDONGS 또는 HOHOS가 3회, TWINKIES 가 1회 이상
DEFINE
TWINKIES AS TWINKIES.product='TWINKIES',
DINGDONGS AS DINGDONGS.product='DINGDONGS',
HOHOS AS HOHOS.product='HOHOS'
) MR
ORDER BY MR.mno, MR.tstamp;
TSTAMP START_TSTAMP END_TSTAMP MNO CLS ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
2014-10-01 12:00:01 2014-10-01 12:00:01 2014-10-01 12:00:07 1 TWINKIES 1 TWINKIES
2014-10-01 12:00:02 2014-10-01 12:00:01 2014-10-01 12:00:07 1 TWINKIES 2 TWINKIES
2014-10-01 12:00:03 2014-10-01 12:00:01 2014-10-01 12:00:07 1 DINGDONGS 3 DINGDONGS
2014-10-01 12:00:04 2014-10-01 12:00:01 2014-10-01 12:00:07 1 HOHOS 4 HOHOS
2014-10-01 12:00:05 2014-10-01 12:00:01 2014-10-01 12:00:07 1 HOHOS 5 HOHOS
2014-10-01 12:00:06 2014-10-01 12:00:01 2014-10-01 12:00:07 1 TWINKIES 6 TWINKIES
2014-10-01 12:00:07 2014-10-01 12:00:01 2014-10-01 12:00:07 1 TWINKIES 7 TWINKIES
2014-10-01 12:00:12 2014-10-01 12:00:12 2014-10-01 12:00:20 2 TWINKIES 12 TWINKIES
2014-10-01 12:00:13 2014-10-01 12:00:12 2014-10-01 12:00:20 2 TWINKIES 13 TWINKIES
2014-10-01 12:00:14 2014-10-01 12:00:12 2014-10-01 12:00:20 2 DINGDONGS 14 DINGDONGS
2014-10-01 12:00:15 2014-10-01 12:00:12 2014-10-01 12:00:20 2 DINGDONGS 15 DINGDONGS
2014-10-01 12:00:16 2014-10-01 12:00:12 2014-10-01 12:00:20 2 HOHOS 16 HOHOS
2014-10-01 12:00:17 2014-10-01 12:00:12 2014-10-01 12:00:20 2 TWINKIES 17 TWINKIES
2014-10-01 12:00:18 2014-10-01 12:00:12 2014-10-01 12:00:20 2 TWINKIES 18 TWINKIES
2014-10-01 12:00:19 2014-10-01 12:00:12 2014-10-01 12:00:20 2 TWINKIES 19 TWINKIES
2014-10-01 12:00:20 2014-10-01 12:00:12 2014-10-01 12:00:20 2 TWINKIES 20 TWINKIES
16 rows selected.
Oracle Native Left Outer Join 문법개선
- 12c 이전에는 한 번에 한 테이블에만 outer 조인이 가능했으나 12c부터는 2개 이상의 복수개의 테이블로 outer 조인이 가능하다.
-- 11g에서 실행할 경우 에러가 발생
SELECT COUNT(*)
FROM SCOTT.EMP A, SCOTT.DEPT B, SCOTT.BONUS C
WHERE B.DEPTNO = A.DEPTNO(+)
AND C.ENAME = A.ENAME(+);
ERROR AT LINE 3:
ORA-01417: a tabe may be outer joined to at most one other table
-- 12c에서는 오류 없이 쿼리가 실행된다.
SELECT COUNT(*)
FROM SCOTT.EMP A, SCOTT.DEPT B, SCOTT.BONUS C
WHERE B.DEPTNO = A.DEPTNO(+)
AND C.ENAME = A.ENAME(+);
COUNT(*)
----------
0
실행계획 :
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
|* 2 | HASH JOIN OUTER | | 1 | 40 | 7 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 20 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | BONUS | 1 | 7 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 4 | 52 | 2 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| PK_DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 280 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Top-n 쿼리의 새로운 기능 (row limiting)
select count(*) from emp;
COUNT(*)
----------
14
select empno, hiredate from emp order by hiredate asc;
EMPNO HIREDATE
---------- -------------------
7369 1980-12-17 00:00:00
7499 1981-02-20 00:00:00
7521 1981-02-22 00:00:00
7566 1981-04-02 00:00:00
7698 1981-05-01 00:00:00
7782 1981-06-09 00:00:00
7844 1981-09-08 00:00:00
7654 1981-09-28 00:00:00
7839 1981-11-17 00:00:00
7900 1981-12-03 00:00:00
7902 1981-12-03 00:00:00
7934 1982-01-23 00:00:00
7788 1987-04-19 00:00:00
7876 1987-05-23 00:00:00
14 rows selected.
-- 8번째 row를 건너뛰고 조회됨
select empno, hiredate from emp order by hiredate offset 8 rows;
EMPNO HIREDATE
---------- -------------------
7839 1981-11-17 00:00:00
7900 1981-12-03 00:00:00
7902 1981-12-03 00:00:00
7934 1982-01-23 00:00:00
7788 1987-04-19 00:00:00
7876 1987-05-23 00:00:00
6 rows selected.
-- 10개의 row를 가져옴
select empno, hiredate from emp
order by hiredate fetch first 10 rows only;
EMPNO HIREDATE
---------- -------------------
7369 1980-12-17 00:00:00
7499 1981-02-20 00:00:00
7521 1981-02-22 00:00:00
7566 1981-04-02 00:00:00
7698 1981-05-01 00:00:00
7782 1981-06-09 00:00:00
7844 1981-09-08 00:00:00
7654 1981-09-28 00:00:00
7839 1981-11-17 00:00:00
7900 1981-12-03 00:00:00
10 rows selected.
-- 25%만큼의 데이터만 가져옴
select empno, hiredate from emp
order by hiredate fetch first 25 percent rows only;
EMPNO HIREDATE
---------- -------------------
7369 1980-12-17 00:00:00
7499 1981-02-20 00:00:00
7521 1981-02-22 00:00:00
7566 1981-04-02 00:00:00
select empno, hiredate from emp
order by hiredate fetch next 25 percent rows only; -- first / next는 서로 동일한 기능을 하였다.
EMPNO HIREDATE
---------- -------------------
7369 1980-12-17 00:00:00
7499 1981-02-20 00:00:00
7521 1981-02-22 00:00:00
7566 1981-04-02 00:00:00
-- 4개의 row를 건너뛰고, 5번째 row부터 25% 가져옴
select empno, hiredate from emp
order by hiredate offset 4 rows fetch next 25 percent rows only;
EMPNO HIREDATE
---------- -------------------
7698 1981-05-01 00:00:00
7782 1981-06-09 00:00:00
7844 1981-09-08 00:00:00
7654 1981-09-28 00:00:00
-- with ties절은 마지막 row가 서로 동률일 경우 동률인 row를 모두 가져옴
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS ONLY;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
132 Olson 2100
128 Markle 2200
136 Philtanker 2200
127 Landry 2400
135 Gee 2400
119 Colmenares 2500
6 rows selected.
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS WITH TIES;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
132 Olson 2100
128 Markle 2200
136 Philtanker 2200
127 Landry 2400
135 Gee 2400
119 Colmenares 2500
131 Marlow 2500
140 Patel 2500
144 Vargas 2500
182 Sullivan 2500
191 Perkins 2500
11 rows selected.
union, union all 분기문장 동시실행
create table big_table
as
select object_id, object_name
from (select object_id, object_name from dba_objects where rownum <=1000) a
, (select level from dual connect by level <= 1000) b
;
create index big_table_idx1 on big_table(object_id asc);
analyze table big_table compute statistics;
select count(*) cnt from big_table where object_id = 885;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| BIG_TABLE_IDX1 | 1000 | 3000 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------
select count(*) cnt from big_table where object_id = 885
union all
select count(*) cnt from big_table where object_id = 237
union all
select count(*) cnt from big_table where object_id = 420
union all
select count(*) cnt from big_table where object_id = 557
union all
select count(*) cnt from big_table where object_id = 880
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6 | 10 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | SORT AGGREGATE | | 1 | 3 | | |
|* 3 | INDEX RANGE SCAN| BIG_TABLE_IDX1 | 1000 | 3000 | 5 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 3 | | |
|* 5 | INDEX RANGE SCAN| BIG_TABLE_IDX1 | 1000 | 3000 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4257
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.