이 스크립트는 {*}김강환{*}님께서 제공해주셨습니다.
set timing off
set autotrace off
set linesize 1000
set pagesize 1000
prompt 0. 기존 테이블 삭제
drop table SALE_SUMM cascade constraints purge;
drop table TEST1 cascade constraints purge;
drop table TEST2 cascade constraints purge;
prompt 1. 매출집계 테이블 생성
CREATE TABLE SALE_SUMM (
YYMM VARCHAR2 (6),
SAUP VARCHAR2 (10),
AMT NUMBER (9)) ;
INSERT INTO SALE_SUMM VALUES ('199612', '1공장', 322794590);
INSERT INTO SALE_SUMM VALUES ('199701', '1공장', 312045670) ;
INSERT INTO SALE_SUMM VALUES ('199702', '1공장', 381020000) ;
INSERT INTO SALE_SUMM VALUES ('199703', '1공장', 412080000) ;
INSERT INTO SALE_SUMM VALUES ('199704', '1공장', 392010000) ;
INSERT INTO SALE_SUMM VALUES ('199705', '1공장', 428000020) ;
INSERT INTO SALE_SUMM VALUES ('199706', '1공장', 415020000) ;
INSERT INTO SALE_SUMM VALUES ('199707', '1공장', 394092000) ;
INSERT INTO SALE_SUMM VALUES ('199708', '1공장', 420010060) ;
INSERT INTO SALE_SUMM VALUES ('199709', '1공장', 431000000) ;
INSERT INTO SALE_SUMM VALUES ('199710', '1공장', 462000000) ;
INSERT INTO SALE_SUMM VALUES ('199711', '1공장', 428010000) ;
INSERT INTO SALE_SUMM VALUES ('199712', '1공장', 472860000) ;
prompt 2. 전월과 당월의 매출을 비교하여 증감률(증감액) 나타내기
conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
conn scott/loveora77
set timing on
set autotrace on
SELECT RPAD(mon,2)||'mon' sec,
SUM(now_mm) now_mm,
SUM(now_mm - bef_mm) rate_m,
TO_CHAR(SUM(DECODE(now_mm,0,NULL, ROUND((now_mm - bef_mm)*100/now_mm,2))), '990.90' ) rate_p
FROM (SELECT DECODE(NO, 2, MM+1, MM+0) mon,
NVL(SUM(DECODE(NO,1,AMT)), 0) now_mm,
NVL(SUM(DECODE(NO,2,AMT)), 0) bef_mm
FROM(SELECT DECODE(YYMM, '199612', '00',SUBSTR(YYMM,5,2)) MM,
SUM(AMT) AMT
FROM SALE_SUMM
WHERE SAUP= '1공장'
AND YYMM BETWEEN '199612' AND '199712'
GROUP BY DECODE(YYMM, '199612', '00', SUBSTR(YYMM,5,2))) X, COPY_T Y
WHERE NO between decode(MM,'00',2,1) and decode(MM,'12',1,2)
group by DECODE(NO,2,MM+1,MM+0)
UNION ALL
SELECT NO mon, 0 now_mm, 0 bef_mm
FROM COPY_T
WHERE NO <= 12)
GROUP BY mon ;
set timing off
set autotrace off
prompt 2개의 응용문제는 교재에는 없는 문제입니다.
prompt 응용문제1)
prompt 데이타를 출력하되 출력로우의 CODE1값이 이전 로우의 CODE1값과 같다면 출력하지 마라.
prompt 단, LAG()함수를 사용하지 말 것
prompt 단,테이블은 한번만 읽을 것
prompt 힌트-로우 복제를 이용하되 선택적 복제+복제위치 지정을 이용하라
conn scott/loveora77
CREATE TABLE TEST1(
CODE1 VARCHAR2(10),
CNT NUMBER,
AMT NUMBER);
INSERT INTO TEST1 VALUES('A',1,10);
INSERT INTO TEST1 VALUES('A',1,10);
INSERT INTO TEST1 VALUES('A',1,10);
INSERT INTO TEST1 VALUES('A',1,10);
INSERT INTO TEST1 VALUES('A',1,10);
INSERT INTO TEST1 VALUES('B',1,10);
INSERT INTO TEST1 VALUES('B',1,10);
INSERT INTO TEST1 VALUES('B',1,10);
INSERT INTO TEST1 VALUES('B',1,10);
INSERT INTO TEST1 VALUES('C',1,10);
prompt 다음과 같이 출력되어야함
prompt CODE1 CCC CNT AMT
prompt
conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
conn scott/loveora77
set timing on
set autotrace on
prompt SELECT DECODE(CODE1,CODE2,'',CODE1) CODE1, CCC, CNT, AMT
prompt FROM(SELECT RM
prompt ,MIN(DECODE(NO,1,CODE1)) CODE1
prompt ,MIN(DECODE(NO,1,CNT)) CNT
prompt ,MIN(DECODE(NO,1,AMT)) AMT
prompt ,MIN(DECODE(NO,2,CODE1)) CODE2
prompt ,MIN(CCC) CCC
prompt FROM(SELECT DECODE(NO,1,RM+0,RM+1) RM
prompt ,A.CODE1,A.CNT,A.AMT,B.NO,C.CNT CCC
prompt FROM(SELECT ROWNUM RM,CODE1,CNT,AMT
prompt FROM TEST1) A, COPY_T B
prompt ,(SELECT CODE1,COUNT(*) CNT
prompt FROM TEST1 GROUP BY CODE1) C
prompt /*복제를 하되 첫번째 복제는 RM 1~10,두번째 복제는 RM 1~9까지 되어야 한다*/
prompt WHERE A.CODE1=C.CODE1
prompt AND B.NO BETWEEN 1 AND DECODE(RM,10,1,2))
prompt GROUP BY RM);
SELECT DECODE(CODE1,CODE2,'',CODE1) CODE1, CCC, CNT, AMT
FROM(SELECT RM
,MIN(DECODE(NO,1,CODE1)) CODE1
,MIN(DECODE(NO,1,CNT)) CNT
,MIN(DECODE(NO,1,AMT)) AMT
,MIN(DECODE(NO,2,CODE1)) CODE2
,MIN(CCC) CCC
FROM(SELECT DECODE(NO,1,RM+0,RM+1) RM
,A.CODE1,A.CNT,A.AMT,B.NO,C.CNT CCC
FROM(SELECT ROWNUM RM,CODE1,CNT,AMT
FROM TEST1) A, COPY_T B
,(SELECT CODE1,COUNT(*) CNT
FROM TEST1 GROUP BY CODE1) C
WHERE A.CODE1=C.CODE1
AND B.NO BETWEEN 1 AND DECODE(RM,10,1,2))
GROUP BY RM);
set timing off
set autotrace off
prompt 응용문제2) 위와 똑같은 문제이다. 단, CODE1,CODE2로 두개가 된다
conn scott/loveora77
CREATE TABLE TEST2(
CODE1 VARCHAR2(10),
CODE2 VARCHAR2(10),
QTY NUMBER,
AMT NUMBER);
INSERT INTO TEST2 VALUES('A','AAA',1,10);
INSERT INTO TEST2 VALUES('A','AAA',1,10);
INSERT INTO TEST2 VALUES('A','AAA',1,10);
INSERT INTO TEST2 VALUES('A','AAB',1,10);
INSERT INTO TEST2 VALUES('A','AAC',1,10);
INSERT INTO TEST2 VALUES('B','BBB',1,10);
INSERT INTO TEST2 VALUES('B','BBB',1,10);
INSERT INTO TEST2 VALUES('B','BBB',1,10);
INSERT INTO TEST2 VALUES('B','BBA',1,10);
INSERT INTO TEST2 VALUES('C','CCC',1,10);
INSERT INTO TEST2 VALUES('A','AAB',1,10);
prompt 다음과 같이 출력되어야함
prompt CODE1 CODE1_CNT CODE2 CODE2_CNT QTY AMT
prompt
conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
conn scott/loveora77
set timing on
set autotrace on
SELECT DECODE(COL1,COL5,NULL,COL1) CODE1
,CODE1_CNT
,DECODE(COL1,COL5,DECODE(COL2,COL6,NULL,COL2),COL2) CODE2
,CODE2_CNT
,COL3 QTY
,COL4 AMT
FROM(SELECT DECODE(NO,1,RM+0,2,RM+1) RM
,MIN(DECODE(NO,1,CODE1)) COL1
,MIN(DECODE(NO,1,CODE2)) COL2
,MIN(DECODE(NO,1,QTY)) COL3
,MIN(DECODE(NO,1,AMT)) COL4
,MIN(DECODE(NO,2,CODE1)) COL5
,MIN(DECODE(NO,2,CODE2)) COL6
FROM(SELECT ROWNUM RM,CODE1,CODE2,QTY,AMT
FROM(SELECT CODE1,CODE2,QTY,AMT
FROM TEST2
ORDER BY CODE1,CODE2)) A,COPY_T B
WHERE B.NO BETWEEN 1 AND DECODE(RM,11,1,2)
GROUP BY DECODE(NO,1,RM+0,2,RM+1))T1
,(SELECT CODE1,COUNT(*) CODE1_CNT
FROM TEST2
GROUP BY CODE1) T2
,(SELECT CODE1,CODE2,COUNT(*) CODE2_CNT
FROM TEST2
GROUP BY CODE1,CODE2) T3
WHERE T1.COL1=T2.CODE1
AND T1.COL1=T3.CODE1
AND T1.COL2=T3.CODE2;
set timing off
set autotrace off
spool off
{column}
- 응용문제 1. 쿼리 실행 결과와 실행계획
{section}{column:width=30%}{code:SQL}
CODE1 CCC CNT AMT
----- ---- ---- -----
A 5 1 10
B 4 1 10
5 1 10
5 1 10
5 1 10
4 1 10
5 1 10
4 1 10
C 1 1 10
4 1 10
10 rows selected.
Elapsed: 00:00:00.17
{column}{column:width=50%}
Execution Plan
----------------------------------------------------------
Plan hash value: 2856455899
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 1173 | 14 (36)| 00:00:01 |
| 1 | HASH GROUP BY | | 17 | 1173 | 14 (36)| 00:00:01 |
| 2 | MERGE JOIN | | 17 | 1173 | 13 (31)| 00:00:01 |
| 3 | SORT JOIN | | 10 | 660 | 9 (34)| 00:00:01 |
|* 4 | HASH JOIN | | 10 | 660 | 8 (25)| 00:00:01 |
|* 5 | VIEW | | 10 | 460 | 3 (0)| 00:00:01 |
| 6 | COUNT | | | | | |
| 7 | TABLE ACCESS FULL| TEST1 | 10 | 330 | 3 (0)| 00:00:01 |
| 8 | VIEW | | 10 | 200 | 4 (25)| 00:00:01 |
| 9 | HASH GROUP BY | | 10 | 70 | 4 (25)| 00:00:01 |
| 10 | TABLE ACCESS FULL| TEST1 | 10 | 70 | 3 (0)| 00:00:01 |
|* 11 | SORT JOIN | | 33 | 99 | 4 (25)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | COPY_T | 33 | 99 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."CODE1"="C"."CODE1")
5 - filter(DECODE("RM",10,1,2)>=1)
11 - access(INTERNAL_FUNCTION("B"."NO")<=DECODE("RM",10,1,2))
filter(INTERNAL_FUNCTION("B"."NO")<=DECODE("RM",10,1,2))
12 - filter("B"."NO">=1)
{column}{section}
Elapsed: 00:00:00.14
{column}
{column:width=50%}
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 101 | 19 (37) | 00:00:01 | |
| HASH JOIN | 1 | 101 | 19 (37) | 00:00:01 | |
| HASH JOIN | 1 | 81 | 15 (40) | 00:00:01 | |
| 3 | VIEW | 11 | 297 | 4 (25) | 00:00:01 | |
| 4 | HASH GROUP BY | 11 | 154 | 4 (25) | 00:00:01 | |
| 5 | TABLE ACCESS FULL | TEST2 | 11 | 154 | 3 (0) | 00:00:01 |
| 6 | VIEW | 18 | 972 | 10 (40) | 00:00:01 | |
| 7 | HASH GROUP BY | 18 | 1008 | 10 (40) | 00:00:01 | |
| 8 | MERGE JOIN | 18 | 1008 | 9 (34) | 00:00:01 | |
| 9 | SORT JOIN | 11 | 583 | 5 (40) | 00:00:01 | |
| VIEW | 11 | 583 | 4 (25) | 00:00:01 | |
| 11 | COUNT | |||||
| 12 | VIEW | 11 | 440 | 4 (25) | 00:00:01 | |
| 13 | SORT ORDER BY | 11 | 440 | 4 (25) | 00:00:01 | |
| 14 | TABLE ACCESS FULL | TEST2 | 11 | 440 | 3 (0) | 00:00:01 |
| SORT JOIN | 33 | 99 | 4 (25) | 00:00:01 | |
| TABLE ACCESS FULL | COPY_T | 33 | 99 | 3 (0) | 00:00:01 |
| 17 | VIEW | 11 | 220 | 4 (25) | 00:00:01 | |
| 18 | HASH GROUP BY | 11 | 77 | 4 (25) | 00:00:01 | |
| 19 | TABLE ACCESS FULL | TEST2 | 11 | 77 | 3 (0) | 00:00:01 |
Predicate Information (identified by operation id):
{column}{section}