19C 기준의 실습 환경을 구성합니다.
01. ORACLE 19C Enterprise를 설치합니다.
•
설치 과정은 생략합니다.
◦
검색해보고 스스로 설치해보는 것을 권장합니다.
•
SQL BOOSTER 는 오라클 11g express 기준으로 작성되었습니다.
•
SQL BOOSTER 이어지는 이야기는 19C Enterprise를 기준으로 합니다.
02. 테이블스페이스와 사용자(스키마)를 생성합니다.
•
OS 환경에 아래의 디렉토리를 생성합니다.
◦
C:\ORADB
◦
데이터가 저장될 공간을 만들 용도입니다. 각자 다른 디렉토리를 사용해도 됩니다.
•
SQL Tool(디비버, SQL Developer 등)을 활용해 오라클에 SYS 계정(as SYSDBA)으로 접속합니다.
•
다음 스크립트를 차례대로 하나씩 실행해 테이블 스페이스와 사용자(스키마)를 생성합니다.
-- ************************************************
-- PART I - 1.1.1 SQL1, SYS USER 사용 - 테이블스페이스 생성
-- ************************************************
-- 윈도우 환경에서 아래 폴더를 먼저 만들어야 한다.
-- C:\ORADB
-- 폴더를 만든 후에, 아래 SQL을 실행.
CREATE TABLESPACE ORA_SQLBOO19C_TS DATAFILE 'C:\ORADB\SQL_SQLBOO19C.DBF' SIZE 10G AUTOEXTEND ON NEXT 500M;
-- ************************************************
-- PART I - 1.1.2 SQL1, SYS USER 사용 - 사용자 생성
-- ************************************************
-- PDB용 로컬 사용자를 만들기 위해 ORACLE_SCRIPT를 TRUE로 설정
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
CREATE USER SQLBOO IDENTIFIED BY "1qaz2wsx" DEFAULT TABLESPACE ORA_SQLBOO19C_TS;
-- ************************************************
-- PART I - 1.1.2 SQL2, SYS USER 사용 - 사용자 접속 권한 설정, TABLESPACE권한 설정
-- ************************************************
ALTER USER SQLBOO DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
ALTER USER SQLBOO ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE TO SQLBOO;
-- ************************************************
-- PART I - 1.1.2 SQL3, SYS USER 사용 - 사용자 성능 뷰 권한 설정
-- ************************************************
GRANT ALTER SYSTEM TO SQLBOO;
GRANT SELECT_CATALOG_ROLE TO SQLBOO;
GRANT EXECUTE ON SYS.DBMS_STATS TO SQLBOO;
GRANT SELECT ON SYS.DBA_SEGMENTS TO SQLBOO;
-- ************************************************
-- PART I - 1.1.2 SQL4, SYS USER 사용 - TEMP 크기 확인
-- ************************************************
SELECT T1.FILE_NAME
,(T1.BYTES / 1024 / 1024) TMP_MB
FROM DBA_TEMP_FILES T1;
-- ************************************************
-- PART I - 1.1.2 SQL5, SYS USER 사용 - TEMP 크기 변경
-- TEMPFILE 경로에 위 결과를 사용
-- ************************************************
ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP01.DBF' RESIZE 500M;
SQL
복사
03. 테이블 생성하기
•
앞에서 만든 SQLBOO 계정으로 접속한 후에 아래 SQL을 실행해 테이블을 생성합니다.
-- ************************************************
-- PART I - 1.2.1 SQL1, 여기서부터는 SQLBOO 사용자를 사용합니다.
-- 테이블 생성.
-- ************************************************
------------------------------------------------------------
-- 기준코드(공통코드, 기초코드, 그룹코드 등으로 불린다)
------------------------------------------------------------
CREATE TABLE C_BAS_CD
(
BAS_CD_DV VARCHAR2(40) NOT NULL,
LNG_CD VARCHAR2(40) NOT NULL,
BAS_CD VARCHAR2(40) NOT NULL,
BAS_CD_NM VARCHAR2(100) NULL,
SRT_OD NUMBER(18) NULL
);
CREATE UNIQUE INDEX PK_C_BAS_CD ON C_BAS_CD(BAS_CD_DV,LNG_CD,BAS_CD);
ALTER TABLE C_BAS_CD
ADD CONSTRAINT PK_C_BAS_CD PRIMARY KEY (BAS_CD_DV,LNG_CD,BAS_CD);
------------------------------------------------------------
-- 기준코드구분
------------------------------------------------------------
CREATE TABLE C_BAS_CD_DV
(
BAS_CD_DV VARCHAR2(40) NOT NULL,
BAS_CD_DV_NM VARCHAR2(100) NULL
);
CREATE UNIQUE INDEX PK_C_BAS_CD_DV ON C_BAS_CD_DV(BAS_CD_DV);
ALTER TABLE C_BAS_CD_DV
ADD CONSTRAINT PK_C_BAS_CD_DV PRIMARY KEY (BAS_CD_DV);
------------------------------------------------------------
-- 아이템 테이블.
-- 아이템 = 실제 판매가 발생하거나 재고 관리가 되는 상품 단위.
------------------------------------------------------------
CREATE TABLE M_ITM
(
ITM_ID VARCHAR2(40) NOT NULL,
ITM_NM VARCHAR2(100) NULL,
ITM_TP VARCHAR2(40) NULL,
UNT_PRC NUMBER(18,3) NULL
);
CREATE UNIQUE INDEX PK_M_ITM ON M_ITM(ITM_ID);
ALTER TABLE M_ITM
ADD CONSTRAINT PK_M_ITM PRIMARY KEY (ITM_ID);
------------------------------------------------------------
-- 아이템 단가 이력 테이블.
------------------------------------------------------------
CREATE TABLE M_ITM_PRC_HIS
(
ITM_ID VARCHAR2(40) NOT NULL,
TO_YMD VARCHAR2(8) NOT NULL,
FR_YMD VARCHAR2(8) NULL,
UNT_PRC NUMBER(18,3) NULL
);
CREATE UNIQUE INDEX PK_M_ITM_PRC_HIS ON M_ITM_PRC_HIS(ITM_ID,TO_YMD);
ALTER TABLE M_ITM_PRC_HIS
ADD CONSTRAINT PK_M_ITM_PRC_HIS PRIMARY KEY (ITM_ID,TO_YMD);
------------------------------------------------------------
-- 지역 마스터 테이블
------------------------------------------------------------
CREATE TABLE M_RGN
(
RGN_ID VARCHAR2(40) NOT NULL,
RGN_NM VARCHAR2(100) NULL,
SRT_OD NUMBER(18) NULL
);
CREATE UNIQUE INDEX PK_M_RGN ON M_RGN(RGN_ID);
ALTER TABLE M_RGN
ADD CONSTRAINT PK_M_RGN PRIMARY KEY (RGN_ID);
------------------------------------------------------------
-- 고객 마스터 테이블
------------------------------------------------------------
CREATE TABLE M_CUS
(
CUS_ID VARCHAR2(40) NOT NULL,
CUS_NM VARCHAR2(100) NULL,
MBL_NO VARCHAR2(100) NULL,
EML_AD VARCHAR2(100) NULL,
PWD VARCHAR2(200) NULL,
RGN_ID VARCHAR2(40) NULL,
ADR_TXT VARCHAR2(200) NULL,
GND_TP VARCHAR2(40) NULL,
BTH_YMD VARCHAR2(8) NULL,
CUS_GD VARCHAR2(40) NULL
);
CREATE UNIQUE INDEX PK_M_CUS ON M_CUS(CUS_ID);
ALTER TABLE M_CUS
ADD CONSTRAINT PK_M_CUS PRIMARY KEY (CUS_ID);
------------------------------------------------------------
-- 아이템 평가
-- 고객이 아이템에 평가를 수행한 기록.
------------------------------------------------------------
CREATE TABLE T_ITM_EVL
(
ITM_ID VARCHAR2(40) NOT NULL,
EVL_LST_NO NUMBER(18) NOT NULL,
CUS_ID VARCHAR2(40) NOT NULL,
EVL_DSC VARCHAR2(1000) NULL,
EVL_DT DATE NULL,
EVL_PT NUMBER(18,2) NULL
);
CREATE UNIQUE INDEX PK_T_ITM_EVL ON T_ITM_EVL(ITM_ID,EVL_LST_NO);
ALTER TABLE T_ITM_EVL
ADD CONSTRAINT PK_T_ITM_EVL PRIMARY KEY (ITM_ID,EVL_LST_NO);
------------------------------------------------------------
-- 주문
------------------------------------------------------------
CREATE TABLE T_ORD
(
ORD_SEQ NUMBER(18) NOT NULL,
CUS_ID VARCHAR2(40) NOT NULL,
ORD_DT DATE NULL,
ORD_ST VARCHAR2(40) NULL,
PAY_DT DATE NULL,
PAY_TP VARCHAR2(40) NULL,
ORD_AMT NUMBER(18,3) NULL,
PAY_AMT NUMBER(18,3) NULL
);
CREATE UNIQUE INDEX PK_T_ORD ON T_ORD(ORD_SEQ);
ALTER TABLE T_ORD
ADD CONSTRAINT PK_T_ORD PRIMARY KEY (ORD_SEQ);
------------------------------------------------------------
-- 주문상세
------------------------------------------------------------
CREATE TABLE T_ORD_DET
(
ORD_SEQ NUMBER(18) NOT NULL,
ORD_DET_NO NUMBER(18) NOT NULL,
ITM_ID VARCHAR2(40) NOT NULL,
ORD_QTY NUMBER(18) NULL,
UNT_PRC NUMBER(18,3) NULL
);
CREATE UNIQUE INDEX PK_T_ORD_DET ON T_ORD_DET(ORD_SEQ,ORD_DET_NO);
ALTER TABLE T_ORD_DET
ADD CONSTRAINT PK_T_ORD_DET PRIMARY KEY (ORD_SEQ,ORD_DET_NO);
------------------------------------------------------------
-- FOREIGN KEY설정들.
------------------------------------------------------------
ALTER TABLE C_BAS_CD
ADD (CONSTRAINT FK_C_BAS_CD_DV_1 FOREIGN KEY (BAS_CD_DV) REFERENCES C_BAS_CD_DV(BAS_CD_DV));
ALTER TABLE M_ITM_PRC_HIS
ADD (CONSTRAINT FK_M_ITM_PRC_HIS FOREIGN KEY (ITM_ID) REFERENCES M_ITM(ITM_ID));
ALTER TABLE M_CUS
ADD (CONSTRAINT FK_CUS_1 FOREIGN KEY (RGN_ID) REFERENCES M_RGN(RGN_ID));
ALTER TABLE T_ITM_EVL
ADD (CONSTRAINT FK_T_ITM_EVL_1 FOREIGN KEY (CUS_ID) REFERENCES M_CUS(CUS_ID));
ALTER TABLE T_ITM_EVL
ADD (CONSTRAINT FK_T_ITM_EVL_2 FOREIGN KEY (ITM_ID) REFERENCES M_ITM(ITM_ID));
ALTER TABLE T_ORD
ADD (CONSTRAINT FK_T_ORD_1 FOREIGN KEY (CUS_ID) REFERENCES M_CUS(CUS_ID));
ALTER TABLE T_ORD_DET
ADD (CONSTRAINT FK_T_ORD_DET_1 FOREIGN KEY (ORD_SEQ) REFERENCES T_ORD(ORD_SEQ));
SQL
복사
04. 데이터 생성하기
•
실습에 사용할 데이터를 생성합니다.
-- SQLBOO 사용자로 접속해서 실행합니다.
-- ************************************************
-- PART I - 1.2.2 SQL1
-- ************************************************
-- 아이템 데이터 생성
INSERT INTO M_ITM (ITM_ID ,ITM_NM ,ITM_TP ,UNT_PRC)
SELECT 'ITM'||LPAD(TO_CHAR(ROWNUM),3,'0') ITM_ID ,T1.ITM_NM ,T1.ITM_TP ,T1.UNT_PRC
FROM (
SELECT T1.ITM_TP||'_'||LPAD(TO_CHAR(RNO),3,'0') ITM_NM
,T1.ITM_TP
,T1.RNO * 100 UNT_PRC
FROM (
SELECT ROWNUM RNO ,'ELEC' ITM_TP FROM DUAL --가전제품
CONNECT BY ROWNUM <= 10
UNION ALL
SELECT ROWNUM RNO ,'PC' ITM_TP FROM DUAL --컴퓨터
CONNECT BY ROWNUM <= 20
UNION ALL
SELECT ROWNUM RNO ,'COOK' ITM_TP FROM DUAL ---주방용품
CONNECT BY ROWNUM <= 30
UNION ALL
SELECT ROWNUM RNO ,'CLOTHES' ITM_TP FROM DUAL --옷
CONNECT BY ROWNUM <= 40
) T1
ORDER BY T1.ITM_TP
,T1.RNO
) T1
;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL2
-- ************************************************
-- 아이템단가 이력 생성
INSERT INTO M_ITM_PRC_HIS (ITM_ID ,TO_YMD ,FR_YMD ,UNT_PRC)
SELECT T1.ITM_ID ,T2.TO_YMD ,T2.FR_YMD ,T1.UNT_PRC * T2.PRC_RT UNT_PRC
FROM M_ITM T1
,(
SELECT '20170310' TO_YMD ,'20170101' FR_YMD ,0.7 PRC_RT FROM DUAL UNION ALL
SELECT '20170320' TO_YMD ,'20170311' FR_YMD ,0.8 PRC_RT FROM DUAL UNION ALL
SELECT '99991231' TO_YMD ,'20170321' FR_YMD ,1 PRC_RT FROM DUAL
) T2
WHERE T1.ITM_ID LIKE '%9'
UNION ALL
SELECT T1.ITM_ID ,'99991231' TO_YMD ,'20170101' FR_YMD ,T1.UNT_PRC UNT_PRC
FROM M_ITM T1
WHERE T1.ITM_ID NOT LIKE '%9'
;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL3
-- ************************************************
-- 지역 데이터 생성
INSERT INTO M_RGN (RGN_ID ,RGN_NM ,SRT_OD)
SELECT T1.RGN_ID ,T1.RGN_NM ,ROW_NUMBER() OVER(ORDER BY T1.RGN_ID) SRT_OD
FROM (
SELECT CHR(ASCII('A') + (ROWNUM-1)) RGN_ID
,CHR(ASCII('A') + (ROWNUM-1)) RGN_NM
FROM DUAL
CONNECT BY ROWNUM <= 5
) T1
;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL4
-- ************************************************
-- 고객 데이터 생성
INSERT INTO M_CUS(CUS_ID ,CUS_NM ,MBL_NO ,EML_AD ,PWD ,RGN_ID ,ADR_TXT ,GND_TP ,BTH_YMD ,CUS_GD)
SELECT 'CUS_'||LPAD(TO_CHAR(T1.ID_SEQ),4,'0') CUS_ID
,'NAME_'||LPAD(TO_CHAR(T1.ID_SEQ),4,'0') CUS_NM
,LPAD(TO_CHAR(T1.ID_SEQ),4,'0')||'-'||LPAD(TO_CHAR(T1.ID_SEQ),4,'0') MBL_NO
,RPAD(TO_CHAR(T1.ID_SEQ),5,'0')||'@abc.com' EML_AD
,'******' PWD
,T1.RGN_ID
,T1.RGN_ID||' Region' ADR_TXT
,T1.GND_TP
,TO_CHAR(ADD_MONTHS(TO_DATE('19900101','YYYYMMDD'),T1.ADD_BTH_MM)+T1.ID_SEQ,'YYYYMMDD') BTH_YMD
,T1.CUS_GD
FROM (
SELECT T_RGN_ID.RGN_ID
,T_GND_TP.GND_TP
,T_CUS_GD.CUS_GD
,T_RGN_ID.SRT_OD ADD_BTH_MM
,ROW_NUMBER() OVER(ORDER BY T_GND_TP.GND_TP ,T_RGN_ID.RGN_ID ,T_CUS_GD.CUS_GD) ID_SEQ
FROM (
SELECT A.RGN_ID
,A.SRT_OD
FROM M_RGN A
,M_RGN B
WHERE B.SRT_OD >= A.SRT_OD
) T_RGN_ID
,(
SELECT 'MALE' GND_TP FROM DUAL UNION ALL
SELECT 'FEMLE' GND_TP FROM DUAL
) T_GND_TP
,(
SELECT 'A' CUS_GD FROM DUAL CONNECT BY ROWNUM <= 2 UNION ALL
SELECT 'B' CUS_GD FROM DUAL CONNECT BY ROWNUM <= 1
) T_CUS_GD
ORDER BY T_RGN_ID.RGN_ID
,T_GND_TP.GND_TP
,T_CUS_GD.CUS_GD
) T1
;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL5
-- ************************************************
-- 주문 데이터 생성
INSERT INTO T_ORD (ORD_SEQ ,CUS_ID ,ORD_DT ,ORD_ST ,PAY_DT ,PAY_TP ,ORD_AMT ,PAY_AMT)
SELECT T4.RNO ORD_SEQ ,T4.CUS_ID ,T4.ORD_DT
,CASE WHEN MOD(T4.RNO, 10) = 1 THEN 'WAIT' ELSE 'COMP' END ORD_ST
,CASE WHEN MOD(T4.RNO, 10) = 1 THEN NULL ELSE T4.ORD_DT END PAY_DT
,CASE WHEN MOD(T4.RNO, 10) = 1 THEN NULL
WHEN MOD(T4.RNO, 10) IN (3,4,5) THEN 'BANK'
ELSE 'CARD' END PAY_TP
,NULL ORD_AMT
,NULL PAY_AMT
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY T3.ORD_DT ,T3.CUS_ID) RNO
,T3.CUS_ID ,T3.ORD_DT
FROM (
SELECT T1.CUS_ID ,TO_DATE(T2.YMD,'YYYYMMDD') ORD_DT
FROM M_CUS T1
,(
SELECT TO_CHAR(TO_DATE('20161231','YYYYMMDD') + ROWNUM,'YYYYMMDD') YMD
,ROWNUM RNK
FROM DUAL T1
CONNECT BY ROWNUM <= 365
) T2
WHERE SUBSTR(T1.CUS_ID,-1,1) = SUBSTR(TO_CHAR(T2.RNK),-1,1)
AND NOT(T2.YMD LIKE '201701%' AND T1.CUS_ID LIKE '%1') --일부 고객 제외 처리.
AND NOT(T2.YMD LIKE '201702%' AND (T1.CUS_ID LIKE '%2' OR T1.CUS_ID LIKE '%3'))
AND NOT(T2.YMD LIKE '201704%' AND (T1.CUS_ID LIKE '%4' OR T1.CUS_ID LIKE '%5'))
AND NOT(T2.YMD LIKE '201703%' AND (T1.CUS_GD = 'B'))
ORDER BY T2.YMD
,T1.CUS_ID
) T3
) T4
;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL6
-- ************************************************
-- 주문 디테일 데이터 생성
INSERT INTO T_ORD_DET (ORD_SEQ ,ORD_DET_NO ,ITM_ID ,ORD_QTY ,UNT_PRC)
SELECT T0.ORD_SEQ ,T0.ORD_DET_NO ,T0.ITM_ID ,T0.ORD_QTY ,T9.UNT_PRC
FROM (
SELECT T1.ORD_SEQ ,1 ORD_DET_NO ,T2.ITM_ID ,1 ORD_QTY,T1.ORD_DT
FROM T_ORD T1
,(
SELECT A.UNT_PRC ,A.ITM_ID
,ROW_NUMBER() OVER(ORDER BY A.ITM_ID) RNK
FROM M_ITM A
WHERE A.ITM_ID <> 'ITM100'
AND A.ITM_TP <> 'PC'
) T2
WHERE MOD(T1.ORD_SEQ,(SELECT COUNT(*) FROM M_ITM C WHERE C.ITM_ID <> 'ITM100')) + 1 = T2.RNK
UNION ALL
SELECT T1.ORD_SEQ ,2 ORD_DET_NO ,T2.ITM_ID ,1 ORD_QTY,T1.ORD_DT
FROM T_ORD T1
,(
SELECT A.UNT_PRC ,A.ITM_ID
,ROW_NUMBER() OVER(ORDER BY A.ITM_ID DESC) RNK
FROM M_ITM A
WHERE A.ITM_ID NOT IN ('ITM100','ITM099')
AND A.ITM_TP <> 'PC'
) T2
WHERE MOD(T1.ORD_SEQ,
(SELECT COUNT(*) FROM M_ITM C WHERE C.ITM_ID NOT IN ('ITM100','ITM099'))) + 1 = T2.RNK
AND MOD(T1.ORD_SEQ,10) IN (1,3,5)
) T0
,M_ITM_PRC_HIS T9
WHERE T0.ITM_ID = T9.ITM_ID
AND TO_CHAR(T0.ORD_DT,'YYYYMMDD') BETWEEN T9.FR_YMD AND T9.TO_YMD
;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL7
-- ************************************************
-- 주문 금액 업데이트
MERGE INTO T_ORD T1
USING (
SELECT A.ORD_SEQ ,SUM(A.ORD_QTY * A.UNT_PRC) ORD_AMT
FROM T_ORD_DET A
GROUP BY A.ORD_SEQ
) T2
ON (T1.ORD_SEQ = T2.ORD_SEQ)
WHEN MATCHED THEN UPDATE SET T1.ORD_AMT = T2.ORD_AMT
,T1.PAY_AMT = CASE WHEN T1.ORD_ST = 'COMP' THEN T2.ORD_AMT ELSE NULL END;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL8
-- ************************************************
-- 아이템 평가 데이터 생성
INSERT INTO T_ITM_EVL(ITM_ID ,EVL_LST_NO ,CUS_ID ,EVL_DSC ,EVL_DT ,EVL_PT)
SELECT T0.ITM_ID
,ROW_NUMBER() OVER(PARTITION BY T0.ITM_ID ORDER BY T0.ORD_DT) EVL_LST_NO
,T0.CUS_ID
,CASE WHEN T0.EVL_PT >= 4 THEN 'Great'
WHEN T0.EVL_PT >= 2 THEN 'Not bad'
ELSE 'Bad' END EVL_DSC
,T0.ORD_DT EVL_DT
,T0.EVL_PT
FROM (
SELECT T2.ITM_ID ,T1.CUS_ID ,T1.ORD_DT
,MOD(TO_NUMBER(SUBSTR(T1.CUS_ID,-2,2)) + TO_NUMBER(SUBSTR(T2.ITM_ID,-2,2)),5) + 1 EVL_PT
FROM T_ORD T1
,T_ORD_DET T2
WHERE T1.ORD_SEQ = T2.ORD_SEQ
AND TO_CHAR(T1.ORD_DT,'YYYYMMDD') NOT LIKE '%2'
AND T1.CUS_ID LIKE '%3'
UNION ALL
SELECT T2.ITM_ID ,T1.CUS_ID ,TO_DATE('20170301','YYYYMMDD') ORD_DT
,1 EVL_PT
FROM M_CUS T1
,M_ITM T2
WHERE T1.CUS_ID LIKE '%1'
AND T2.ITM_ID LIKE '%1'
) T0
;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL9
-- ************************************************
-- 기준코드구분 데이터 생성
INSERT INTO C_BAS_CD_DV(BAS_CD_DV ,BAS_CD_DV_NM)
SELECT 'LNG_CD' BAS_CD_DV ,'언어코드' BAS_CD_DV_NM FROM DUAL UNION ALL
SELECT 'ITM_TP' BAS_CD_DV ,'아이템유형' BAS_CD_DV_NM FROM DUAL UNION ALL
SELECT 'ORD_ST' BAS_CD_DV ,'주문상태' BAS_CD_DV_NM FROM DUAL UNION ALL
SELECT 'PAY_TP' BAS_CD_DV ,'지불유형' BAS_CD_DV_NM FROM DUAL UNION ALL
SELECT 'GND_TP' BAS_CD_DV ,'성별구분' BAS_CD_DV_NM FROM DUAL UNION ALL
SELECT 'CUS_GD' BAS_CD_DV ,'고객등급' BAS_CD_DV_NM FROM DUAL;
COMMIT;
-- ************************************************
-- PART I - 1.2.2 SQL10
-- ************************************************
-- 기준코드 데이터 생성
INSERT INTO C_BAS_CD (BAS_CD_DV ,LNG_CD ,BAS_CD ,BAS_CD_NM ,SRT_OD)
SELECT 'LNG_CD' BAS_CD_DV ,'KO' LNG_CD ,'KO' BAS_CD ,'한국어' BAS_CD_NM ,10 SRT_OD FROM DUAL UNION ALL
SELECT 'LNG_CD' ,'KO' ,'EN' ,'영어' ,20 FROM DUAL UNION ALL
SELECT 'LNG_CD' ,'KO' ,'CN' ,'중국어' ,30 FROM DUAL UNION ALL
SELECT 'ITM_TP' ,'KO' ,'ELEC' ,'가전제품' ,10 FROM DUAL UNION ALL
SELECT 'ITM_TP' ,'KO' ,'PC' ,'컴퓨터' ,20 FROM DUAL UNION ALL
SELECT 'ITM_TP' ,'KO' ,'COOK' ,'주방도구' ,30 FROM DUAL UNION ALL
SELECT 'ITM_TP' ,'KO' ,'CLOTHES' ,'옷' ,40 FROM DUAL UNION ALL
SELECT 'ORD_ST' ,'KO' ,'WAIT' ,'대기' ,10 FROM DUAL UNION ALL
SELECT 'ORD_ST' ,'KO' ,'COMP' ,'완료' ,20 FROM DUAL UNION ALL
SELECT 'PAY_TP' ,'KO' ,'BANK' ,'계좌이체' ,10 FROM DUAL UNION ALL
SELECT 'PAY_TP' ,'KO' ,'CARD' ,'카드' ,20 FROM DUAL UNION ALL
SELECT 'GND_TP' ,'KO' ,'FEMLE' ,'여성' ,10 FROM DUAL UNION ALL
SELECT 'GND_TP' ,'KO' ,'MALE' ,'남성' ,20 FROM DUAL UNION ALL
SELECT 'CUS_GD' ,'KO' ,'A' ,'A' ,10 FROM DUAL UNION ALL
SELECT 'CUS_GD' ,'KO' ,'B' ,'B' ,20 FROM DUAL;
COMMIT;
-- ************************************************
-- 인덱스 성능 TEST 테이블
-- ************************************************
-- 테스트를 위한 테이블 만들기
CREATE TABLE T_ORD_BIG AS
SELECT T1.* ,T2.RNO ,TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD
FROM T_ORD T1
,(SELECT ROWNUM RNO
FROM DUAL CONNECT BY ROWNUM <= 10000) T2
;
CREATE INDEX X_T_ORD_BIG_TEST ON T_ORD_BIG(ORD_SEQ);
CREATE INDEX X_T_ORD_BIG_1 ON T_ORD_BIG(ORD_YMD);
CREATE INDEX X_T_ORD_BIG_2 ON T_ORD_BIG(RNO);
CREATE INDEX X_T_ORD_BIG_3 ON T_ORD_BIG(ORD_YMD, CUS_ID);
CREATE INDEX X_T_ORD_BIG_4 ON T_ORD_BIG(CUS_ID, ORD_YMD, ORD_ST);
-- 아래는 T_ORD_BIG 테이블의 통계를 생성하는 명령어다.
-- BEGIN부터 END까지 블록으로 지정해 한번에 실행해야 한다.
-- 첫 번째 파라미터에는 테이블 OWNER를, 두 번째 파라미터에는 테이블 명을 입력한다.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SQLBOO','T_ORD_BIG');
END;
-- ************************************************
-- JOIN 성능 TEST 테이블
-- ************************************************
-- T_ORD_JOIN 테이블을 만드는 SQL
CREATE TABLE T_ORD_JOIN AS
SELECT ROW_NUMBER() OVER(ORDER BY T1.ORD_SEQ, T2.ORD_DET_NO, T3.RNO) ORD_SEQ
,T1.CUS_ID ,T1.ORD_DT ,T1.ORD_ST ,T1.PAY_TP
,T2.ITM_ID ,T2.ORD_QTY ,T2.UNT_PRC ,TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD
FROM T_ORD T1
,T_ORD_DET T2
,(SELECT ROWNUM RNO
FROM DUAL CONNECT BY ROWNUM <= 1000
) T3
WHERE T1.ORD_SEQ = T2.ORD_SEQ;
CREATE UNIQUE INDEX PK_T_ORD_JOIN ON T_ORD_JOIN(ORD_SEQ);
ALTER TABLE T_ORD_JOIN ADD CONSTRAINT PK_T_ORD_JOIN PRIMARY KEY(ORD_SEQ);
CREATE INDEX X_T_ORD_JOIN_1 ON T_ORD_JOIN(CUS_ID);
CREATE INDEX X_T_ORD_JOIN_2 ON T_ORD_JOIN(CUS_ID, ORD_YMD);
CREATE INDEX X_T_ORD_JOIN_3 ON T_ORD_JOIN(ORD_YMD);
CREATE INDEX X_T_ORD_JOIN_4 ON T_ORD_JOIN(ITM_ID,ORD_YMD);
-- 아래는 T_ORD_BIG 테이블의 통계를 생성하는 명령어다.
-- BEGIN부터 END까지 블록으로 지정해 한번에 실행해야 한다.
-- 첫 번째 파라미터에는 테이블 OWNER를, 두 번째 파라미터에는 테이블 명을 입력한다.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SQLBOO','T_ORD_JOIN');
END;
SQL
복사