긍적적인 사고, 음식의 절제, 규칙적인 운동

예제파일 다운로드

/*
SQLLDR 테스트
*/

/*
Case #1 : 가변길이 자료

가장 기본적인 Case 이다. 콘트롤 파일에 로딩할 자료를 포함하고 있다.
자료는 길이가 고정되어 있지 않고 가변적이다.
필드는 기본적으로 콤마(,)로 구분되고, 필요에 따라서 이중따옴표(")로 묶여 있다.
*/

-- 테이블 구조
CREATE TABLE DEPT_NOM(
DEPTNO NUMBER(2),
DNAME CHAR(14),
LOC CHAR(13)
);


-- 컨트롤 파일 ( ulcase1.ctl )
LOAD DATA
INFILE *
INTO TABLE DEPT_NOM
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ""
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH, "SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"

-- 실행
D:\>sqlldr userid=hr/hr control=ulcase1.ctl log=ulcase1.log

그림 001


그림 002 - INSERT 시 해당 테이블에 데이터가 있으면 안된다.



-- TRUNCATE TABLE DEPT_NOM;
SELECT * FROM DEPT_NOM;

/*
Case #2 : 고정포맷 자료

로딩할 자료가 고정된 포맷(모든 레코드의 각 필드가 같은 Byte 위치에서 시작)인 경우에 사용한다.
필드가 시작하는 위치를 정확히 계산해야 한다. 특히, Tab문자는 1 Byte로 계산해야 한다.
또한, 한 Line에 여러 레코드가 있어서는 안된다.
*/

-- 테이블
CREATE TABLE EMP_NOM(
empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)

)

-- 컨트롤 파일 ( ulcase2.ctl )
LOAD DATA
INFILE 'ulcase2.dat'
INTO TABLE EMP_NOM
(EMPNO POSITION(01:04)INTEGER EXTERNAL,
ENAME POSITION(06:15)CHAR,
JOB POSITION(17:25)CHAR,
MGR POSITION(27:30)INTEGER EXTERNAL,
SAL POSITION(32:39)DECIMAL EXTERNAL,
COMM POSITION(41:48)DECIMAL EXTERNAL,
DEPTNO POSITION(50:51)INTERGER EXTERNAL)

-- 로딩할 DATA FILE 지정
-- 데이타를 Byte 단위로 정확히 잘라서 로딩할 때, POSITION(from:to) 키워드를 사용하며,
-- (from:to)는 DATA FILE에서 해당 필드의 Byte (시작:종료) 위치를 가리킨다.

-- 데이터 파일 ( ulcase2.dat )
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20

-- 로딩할 자료가 공백인 경우, 자동으로 NULL이 입력된다.
--※ EXTERNAL 키워드는 숫자형 데이타 타입(INTEGER, FLOAT, DECIMAL, ZONED)에 따라붙는데,
-- 이는 문자(Character)로 표시된 숫자를 읽을 때 사용한다.
-- 반대로 숫자가 binary 형태인 경우는 INTEGER, DECIMAL 등을 사용한다.


-- 실행
D:\>sqlldr userid=hr/hr control=ulcase2.ctl log=ulcase2.log

그림 003



-- TRUNCATE TABLE EMP_NOM;
SELECT * FROM EMP_NOM;


/*
Case #3 : 구분자, Date, Sequence 사용

다음은 특정 구분자(':')로 구분된 경우와 Date Tyype의 처리, SEQUENCE 함수의 사용 예이다.
*/

-- 테이블
CREATE TABLE EMP_NOM2(
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
PROJNO NUMBER,
LOADSEQ NUMBER
);

-- 컨트롤 파일 ( ulcase3.ctl )
LOAD DATA
INFILE *
APPEND
INTO TABLE EMP_NOM2
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE(20) "DD-Month-YYYY",
SAL,
COMM,
DEPTNO CHAR TERMINATED BY ":",
PROJNO,
LOADSEQ SEQUENCE(MAX, 1)
)
BEGINDATA
7782,"Clark","Manager",7839,09-4월-1981,2572.50,,10:101
7839,"King","President",,17-11월-1981,5500.00,,10:102
7934,"Miller","Clerk",7782,23-1월-1982,920.00,,10:102
7566,"Jones","Manager",7839,02-3월-1981, 3123.75,,20:101
7499,"Allen","Salesman",7698,20-2월-1981,1600.00,300.00,30:103
7654,"Martin","Salesman",7698,28-12월-1981,1312.50,1400.00,30:103
7658,"Chan","Analyst",7566,03-5월-1982,3450,,20:101

-- 날짜 TYPE을 시스템 날짜에 맞추어야 입력 가능하다.
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD-Month-YYYY') "TODAY" FROM DUAL;

/*
- DATA Type을 지정하지 않은 곳은 Default로 char(255)가 된다.
- : 문자를 만날 때까지의 문자열 필드
- loadseq 필드의 최대값을 구한 후, 1씩 증가시킨 값을 데이타로 사용한다.
*/

-- 실행
D:\>sqlldr userid=hr/hr control=ulcase3.ctl log=ulcase3.log

-- TRUNCATE TABLE EMP_NOM2;
SELECT * FROM EMP_NOM2;

/*
※ 데이타 없이 SQL*Loader 자체적으로 로딩할 자료를 만들수 있는 지시어
CONSTANT : 상수를 지정하여 특정 컬럼에 지정된 상수를 로딩한다.
RECNUM : 로딩되는 레코드의 현재 Count를 특정 컬럼에 로딩한다.
SYSDATE : 특정 컬럼에 현재 날짜를 로딩한다.
SEQUENCE(start,inc) : start부터 inc만큼씩 증가하는 수를 로딩한다.
- start에는 MAX, COUNT와 같은 Keyword를 사용할 수 있다.
- MAX는 현재 컬럼에 존재하는 값들 중 가장 큰 값에 inc를 더한 값이다.
- COUNT는 로딩할 테이블에 이미 존재하는 레코드건수에 inc를 더한 값이다.
*/


/*
Case #4 : 한 레코드가 여러줄로 구성

하나의 레코드가 데이타파일에서 여러줄로 구성되어 있는 경우 continueif 문을 사용하는 예이다.
*/

-- 테이블
CREATE TABLE EMP_NOM3(
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

CREATE UNIQUE INDEX EMPIX ON EMP_NOM3(EMPNO);

-- 콘트롤 파일( ulcase4.ctl )
LOAD DATA
INFILE "ulcase4.dat"
DISCARDFILE "ulcase4.dis"
DISCARDMAX 999
REPLACE
CONTINUEIF(1)='*'
INTO TABLE EMP_NOM3
(
EMPNO POSITION(01:04)INTEGER EXTERNAL,
ENAME POSITION(06:15)CHAR,
JOB POSITION(17:25)CHAR,
MGR POSITION(27:30)INTEGER EXTERNAL,
SAL POSITION(32:39)DECIMAL EXTERNAL,
COMM POSITION(41:48)DECIMAL EXTERNAL,
DEPTNO POSITION(50:51)INTEGER EXTERNAL,
HIREDATE POSITION(52:60)INTEGER EXTERNAL
)

/*
- Discard 파일을 ulcase4.dis 로 생성한다.
Discard 되는 레코드가 999 건 까지는 허용하고, 그보다 많으면, 로딩을 중단한다.
단, 이번 예제에는 WHEN 조건이 없어서 Discard 파일은 실제로 생성되지 않는다.
- Replace 옵션은 로딩할 테이블의 기존 데이타를 모두 삭제하고 로딩한다.
- 라인의 첫번째 컬럼에 '*' 문자가 있으면, 다음에 나오는 Line을 현재 Line의 연장으로 간주한다.

*/

-- 데이터 파일 ( ulcase4.dat )
*7782 CLARK MA
NAGER 7839 2572.50 -10 2585/11/12
*7839 KING PR
ESIDENT 5500.00 2583/10/03
*7934 MILLER CL
ERK 7782 920.00 2580/05/03
*7934 MILLER CL
ERK 7782 920.00 2580/05/03
* MILLER CL
ERK 7782 920.00 2580/05/03


-- 실행
D:\>sqlldr userid=hr/hr control=ulcase4.ctl log=ulcase4.log

-- TRUNCATE TABLE EMP_NOM3;
SELECT * FROM EMP_NOM3;

-- EMPNO가 없어 ORA-01400 에러가 발생하고, 해당 레코드는 BAD File에 기록된다.
-- EMPNO 7658 번이 앞에서 이미 등록되어, ORA-00001 에러가 발생하고, 해당 레코드는 BAD File에 기록된다.
-- ulcase4.log
레코드 4: 기각됨 - 테이블 EMP_NOM3에 오류
ORA-00001: 무결성 제약 조건(HR.EMPIX)에 위배됩니다
-- ulcase4.bad
*7934 MILLER CL
ERK 7782 920.00 2580/05/03



/*
Case #5 : 동시에 여러 테이블로 로딩

데이타 파일에서 한 레코드를 읽어서 여러 테이블에 동시에 로딩하는 예이다.
*/

-- 테이블 구조
CREATE TABLE EMP_NOM4 (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

CREATE UNIQUE INDEX EMPIX_4 ON EMP_NOM4(EMPNO);

CREATE TABLE PROJ_NOM4 (
EMPNO NUMBER,
PROJNO NUMBER
);

-- 콘트롤 파일(ulcase5.ctl)
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dis'
REPLACE
INTO TABLE EMP_NOM4
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
ENAME POSITION(6:15) CHAR,
DEPTNO POSITION(17:18) CHAR,
MGR POSITION(20:23) INTEGER EXTERNAL
)
-- 1st PROJ
INTO TABLE PROJ_NOM4
WHEN PROJNO != ' '
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(25:27) INTEGER EXTERNAL
)
-- 2st PROJ
INTO TABLE PROJ_NOM4
WHEN PROJNO != ' '
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(29:31) INTEGER EXTERNAL
)
-- 3st PROJ
INTO TABLE PROJ_NOM4
WHEN PROJNO != ' '
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(33:35) INTEGER EXTERNAL
)

/*
- Discard 파일을 ulcase5.dis 로 생성한다. WHEN 조건에 위배되는 레코드가 Discard
파일에 기록된다.
- Replace 옵션으로 인해 로딩할 테이블의 기존 데이타를 모두 삭제하고 로딩한다.
- 여러 필드에서 PROJNO를 구해서 PROJ 테이블에 등록한다.
*/

-- 데이터 파일( ulcase5.dat )
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 236 456
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200

-- 편의상 칸수 맞추느라 데이터를 표시했을 뿐이지 정확한 바이트 순으로 기록되어 있어야 한다. POSITION(start:end) 주의
-- PROJNO가 없는 경우는 WHEN 조건에 위배되므로, PROJ 테이블에 등록되지 않고,
-- 레코드가 Discard 파일에 기록된다.

-- 실행
D:\>sqlldr userid=hr/hr control=ulcase5.ctl log=ulcase5.log

-- TRUNCATE TABLE EMP_NOM4;
SELECT * FROM EMP_NOM4;
SELECT * FROM PROJ_NOM4;

/*
Case #6 : Direct Path Load

Direct Path Load방식에 의해 로딩하는 예이다.
*/

-- 테이블 구조
CREATE TABLE EMP_NOM6 (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

CREATE UNIQUE INDEX EMPIX_6 ON EMP_NOM6(EMPNO);

-- 콘트롤 파일(ulcase6.ctl)
LOAD DATA
INFILE 'ulcase6.dat'
REPLACE
INTO TABLE EMP
SORTED INDEXES (EMPIX_6)
(
EMPNO POSITION(1:4),
ENAME POSITION(6:15),
JOB POSITION(17:25),
MGR POSITION(27:30) NULLIF MGR=BLANKS,
SAL POSITION(32:39) NULLIF SAL=BLANKS,
COMM POSITION(41:48) NULLIF COMM=BLANKS,
DEPTNO POSITION(50:51) NULLIF EMPNO=BLANKS
)

/*
- Index를 만들기 위해 소팅하는 시간을 줄일 수 있는 기능이다.
Direct Path Load 시에는 SQL*Loader가 로딩 전에 인덱스를 Drop하고
로딩 후 다시 만드는데, 이때 로딩될 데이타가 이미 소팅되어 있어 empix 인덱스를 만들기 위해 소팅할 필요가 없음을 SQL*Loader에게 알려준다.
- nullif field_name=blanks 필드 위치에 입력할 데이타가 없는 경우, 대신 null 을 입력한다.
- NVL, upper, to_number 등과 같은 함수를 Direct path load에서는 사용할 수 없다.
왜냐하면 SQL Interface를 경유하지 않고 바로 Data block에 기록하기 때문이다.
*/
-- 데이터파일 ( ulcase6.dat )
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7566 JONES MANAGER 7839 3123.75 20
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10


-- 실행
D:\>sqlldr userid=hr/hr control=ulcase6.ctl log=ulcase6.log

-- TRUNCATE TABLE EMP_NOM6;
SELECT * FROM EMP_NOM6;


/*
※ Conventional Path Load
SQL*Loader 수행 시 direct 옵션을 주지 않는 일반적인 로딩이 여기에 해당된다.
데이타의 로딩을 SQL*Plus 상에서 Insert 문을 수행하듯이 처리한다.
※ Direct Path Load
이전 Block들을 재활용하지 않고, 새로운 Block만 사용하므로, 재활용 가능한 Block들을
검색하는 시간을 줄일 수 있고, Writing 횟수를 줄일 수 있다.
Bind-Array Buffer를 사용하지 않고, Formatted Database Block에 직접 기록한다.
unrecoverable 옵션을 사용하면, redo/undo log에 기록하는 시간도 없앨 수 있다.
*/


/*
Case #7 : 정형화된 보고서에서 추출하여 로딩
Trigger를 이용하여 정형화된 보고서에서 데이타 로딩하는 예이다.
*/

-- 테이블 구조
CREATE TABLE EMP_NOM7 (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

--Table Description 순서와 Control file의 Column 순서가 일치할 필요는 없다.

CREATE UNIQUE INDEX EMPIX_7 ON EMP_NOM7(EMPNO);



-- 콘트롤 파일(ulcase7.ctl)
LOAD DATA
INFILE 'ulcase7.dat'
DISCARDFILE 'ulcase7.dis'
APPEND
INTO TABLE EMP_NOM7
WHEN (57)='.'
TRAILING NULLCOLS
(
HIREDATE SYSDATE,
DEPTNO POSITION(1:2) INTEGER EXTERNAL(3) NULLIF DEPTNO=BLANKS,
JOB POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF JOB=BLANKS "UPPER(:JOB)",
MGR POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE NULLIF MGR=BLANKS,
ENAME POSITION (34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ENAME)",
EMPNO INTEGER EXTERNAL TERMINATED BY WHITESPACE,
SAL POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:SAL,'$99,999.99')",
COMM INTEGER EXTERNAL ENCLOSED BY '(' AND '%' ":COMM * 100")


-- WHEN 조건에 의해서 57번 컬럼에 점(.)이 있는 경우만 로딩하고, 나머지는 Discard 파일에 기록된다.
-- TRAILING NULLCOLS에 의해 실제 필요한 컬럼수보다 데이타파일의 컬럼이 적은 경우 나머지는 Null로 등록한다.
-- NULLIF deptno=BLANKS deptno가 공백인 경우 Null로 등록
-- UPPER() 해당 필드값을 로딩 시 UPPER() 함수를 실행하여 로딩하라는 의미
-- comm 값은 '('와 '%'문자로 둘러싸여 있고, 로딩시 :comm * 100 을 수행하여 로딩하라는 의미


-- 데이타 파일(ulcase7.dat)

Today''s Newly Hired Employees

Dept Job Manager MgrNo Emp Name EmpNo Salary/Commission
---- ---------- --------- ----- ----------- ------- -------------------
20 Salesman Blake 7698
Shepard 8061 $1,600.00 (3%)
Falstaff 8066 $1,250.00 (5%)
Major 8064 $1,250.00 (14%)

30 Clerk Scott 7788
Conrad 8062 $1,100.00
Ford 7369
DeSilva 8063 $800.00
Manager King 7839
Provo 8065 $2,975.00



-- Trigger를 이용하여 앞 레코드의 데이타가 다음 레코드 로딩 시 이용되도록 한다. 즉, 두번째, 세번째 레코드의 Dept 필드는 공백이지만 첫번째 레코드의 20이 대신 로딩된다.


-- 실행 예
sqlldr userid=hr/hr control=ulcase7.ctl log=ulcase7.log

-- TRUNCATE TABLE EMP_NOM7;
SELECT * FROM EMP_NOM7;



-- Trigger 생성

CREATE OR REPLACE PACKAGE uldemo7 AS
last_deptno NUMBER;
last_job CHAR(9);
last_mgr NUMBER;
END uldemo7;
/


-- 이전 값들을 저장하기 위한 전역변수 개념으로 이해하면 된다.


CREATE OR REPLACE TRIGGER uldemo7_emp_nom7_insert
BEFORE INSERT ON emp_nom7
FOR EACH ROW

BEGIN
IF :new.deptno IS NOT NULL THEN
uldemo7.last_deptno := :new.deptno; --save value for later use
ELSE
:new.deptno := uldemo7.last_deptno; --use last valid value
END IF;

IF :new.job IS NOT NULL THEN
uldemo7.last_job := :new.job; --save value for later use
ELSE
:new.job := uldemo7.last_job; --use last valid value
END IF;

IF :new.mgr IS NOT NULL THEN
uldemo7.last_mgr := :new.mgr; --save value for later use
ELSE
:new.mgr := uldemo7.last_mgr; --use last valid value
END IF;

END;
/



-- emp 테이블에 insert가 일어나기 바로직전에 아래의 PL/SQL문장이 수행된다.
-- 새로운 값이 들어오면 uldemo7 패키지에 값을 저장하고,
-- Null 이면 uldemo7 패키지에 있는 값을 사용한다.



Case #8 : (8i이상) 파티션 테이블



파티션 테이블에 데이타를 로딩하는 예이다.



테이블 구조

create table lineitem (
l_orderkey number,
l_partkey number,
l_suppkey number,
l_linenumber number,
l_quantity number,
l_extendedprice number,
l_discount number,
l_tax number,
l_returnflag char,
l_linestatus char,
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(17),
l_shipmode char(7),
l_comment char(43)
)
partition by range (l_shipdate)
(
partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DD-MON-YYYY')),
partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY')),
partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY')),
partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY'))
);



- l_shipdate 컬럼을 Partition Key로 하여 4개의 파티션으로 구성되어 있다.



콘트롤 파일(ulcase8.ctl)

LOAD DATA
INFILE 'ulcase8.dat' "fix 129"
BADFILE 'ulcase8.bad'
TRUNCATE
INTO TABLE lineitem
PARTITION (ship_q1)
(l_orderkey position (1:6) char,
l_partkey position (7:11) char,
l_suppkey position (12:15) char,
l_linenumber position (16:16) char,
l_quantity position (17:18) char,
l_extendedprice position (19:26) char,
l_discount position (27:29) char,
l_tax position (30:32) char,
l_returnflag position (33:33) char,
l_linestatus position (34:34) char,
l_shipdate position (35:43) char,
l_commitdate position (44:52) char,
l_receiptdate position (53:61) char,
l_shipinstruct position (62:78) char,
l_shipmode position (79:85) char,
l_comment position (86:128) char)



- 데이타파일의 각 레코드가 129byte의 고정길이임을 지정한다.

- lineitem 테이블의 모든 데이타를 삭제한 후 로딩을 시작한다.



데이타 파일(ulcase8.dat)

1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP
1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN MAIL 5wM04SNyl0AnghCP2nx lAi
1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN REG AIRSQC2C 5PNCy4mM
1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE AIR Om0L65CSAwSj5k6k
1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh
1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE FOB C2gOQj OB6RLk1BS15 igN
2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD AIR O52M70MRgRNnmm476mNm
3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN FOB 6wQnO0Llg6y
3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN SHIP LhiA7wygz0k4g4zRhMLBAM
3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297



실행 예

sqlldr userid=scott/tiger control=ulcase8.ctl log=ulcase8.log




Case #9 : (8i이상) LOBFILE (CLOB)



CLOB 데이타의 로딩 예이다.



테이블 구조

create table emp (
empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2),
resume clob
);



콘트롤 파일(ulcase9.ctl)

LOAD DATA
INFILE *
INTO TABLE EMP
REPLACE
FIELDS TERMINATED BY ','
(EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
RES_FILE FILLER CHAR,
"RESUME" LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = 'NONE'
)
BEGINDATA
7782,CLARK ,MANAGER ,7839 ,2572.50, ,10,ulcase91.dat
7839,KING ,PRESIDENT, ,5500.00, ,10,ulcase92.dat
7934,MILLER ,CLERK ,7782 ,920.00 , ,10,ulcase93.dat
7566,JONES ,MANAGER ,7839 ,3123.75, ,20,ulcase94.dat
7499,ALLEN ,SALESMAN ,7698 ,1600.00,300.00 ,30,ulcase95.dat
7654,MARTIN,SALESMAN ,7698 ,1312.50,1400.00,30,ulcase96.dat
7658,CHAN ,ANALYST ,7566 ,3450.00, ,20,NONE



- 실제 Lobfile과 Mapping하기 위한 Lobfile의 이름을 추출한다. 즉, 첫번째 레코드의 경우 RES_FILE 필드에 ulcase91.dat 문자열이 읽혀지고, 이는 실제 데이타를 읽어 올 Lobfile의 파일명이다.

- RES_FILE에 지정된 Lobfile에서 데이타를 읽어서 RESUME 컬럼에 로딩한다. 데이타는 EOF를 만날 때까지 읽는다. RES_FILE에 'NONE'으로 지정된 경우는 NULL로 로딩된다.


실행 예
sqlldr userid=scott/tiger control=ulcase9.ctl log=ulcase9.log



데이타 파일(ulcase91.dat)


Resume for Mary Clark

Career Objective: Manage a sales team with consistent record breaking performance.

Education: BA Business University of Iowa 1992

Experience: 1992-1994 - Sales Support at MicroSales Inc.
Won "Best Sales Support" award in 1993 and 1994

1994-Present - Sales Manager at MicroSales Inc.
Most sales in mid-South division for 2 years



- ulcase91.dat 파일의 내용이 resume 컬럼에 통째로 들어간다.



Case #10 : (8i이상) REF Fields and VARRAYs



Reference Type과 Varray Type의 컬럼을 가진 테이블에 자료를 로딩하는 예이다.



테이블 구조

create type customer_type as object (
cust_no char(5),
name char(20),
addr char(20)
);



create table customers of customer_type
(primary key (cust_no))
object id primary key;

--customers 테이블의 컬럼은 cust_no, name, addr이고, cust_no는 PK로 사용된다.



create type item_type as object (
item varchar(50),
cnt number,
price number(7,2)
);



create type item_list_type as varray (1000) of item_type;



create table orders (
order_no char(5),
cust ref customer_type references customers,
item_list item_list_type
);

--customers 테이블의 reference 타입이다.

--item_type의 varray 타입이다. C language의 구조체 배열과 비슷한 개념이다.



콘트롤 파일(ulcase10.ctl)

LOAD DATA
INFILE *
CONTINUEIF THIS (1) = '*'
INTO TABLE customers
replace
fields terminated by ","
(
cust_no char,
name char,
addr char
)
INTO TABLE orders
replace
fields terminated by ","
(
order_no char,
cust_no FILLER char,
cust REF (CONSTANT 'CUSTOMERS', cust_no),
item_list_count FILLER char,
item_list varray count (item_list_count)
(
item_list column object
(
item char,
cnt char,
price char
)
)
)
BEGINDATA
*00001,Spacely Sprockets,15 Space Way,
*00101,00001,2,
*Sprocket clips, 10000, .01,
Sprocket cleaner, 10, 14.00
*00002,Cogswell Cogs,12 Cogswell Lane,
*00100,00002,4,
*one quarter inch cogs,1000,.02,
*one half inch cog, 150, .04,
*one inch cog, 75, .10,
Custom coffee mugs, 10, 2.50



- '*' 라인의 첫번째 컬럼에 '*' 문자가 있으면, 다음에 나오는 Line을 현재 Line의 연장으로 간주한다.

- orders 테이블의 cust 컬럼은 customers 테이블의 reference type이므로, 로딩 시 customers 테이블의 어떤 레코드와 레퍼런싱 할 것인지의 정보가 필요하다. 따라서 customers 테이블의 PK에 해당하는 cust_no를 읽어서 이를 이용해 customers 테이블과 레퍼런싱 한다.

- item_list 컬럼은 item_type의 varray로 C의 구조체 배열과 같은 개념이다. 여기서는 item, cnt, price를 로딩해야 하는데, 중요한 건 몇번을 로딩해야 하는지를 알아야 하는 것이다.
따라서, 먼저 item_list_count 를 구한 후, 이 값만큼 자료를 item_list 컬럼으로 로딩한다.

- 보라, 남색, 녹색 순

customers 테이블의 cust_no, name, addr 로 로딩된다.

orders 테이블의 order_no, cust 로 로딩된다.

orders 테이블의 item_list 로 로딩된다.

customers 테이블의 cust_no, name, addr 로 로딩된다.

orders 테이블의 order_no, cust 로 로딩된다.

orders 테이블의 item_list 로 로딩된다.


실행 예
sqlldr userid=scott/tiger control=ulcase10.ctl log=ulcase10.log







.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

** TXT 파일을 ORACLE DB로 저장할수 있는 오라클 유틸리티입니다 **



SQL*LOADER SAMPLES
==================


SQL*Loader는 외부 화일의 데이타를 ORACLE 데이타베이스의 table에 넣기
위한 유틸리티입니다. SQL*Loader를 사용하려면 외부 데이타 화일과
콘트롤 화일이 필요합니다. 콘트롤 화일이라고 하는 것은 로드하는 데이타의
정보를 저장한 화일입니다. 간단한 샘플 콘트롤 화일을 설명하겠습니다.



load data 제어 화일의 선두에는 반드시 이 말이 필요합니다.
infile sample.dat 외부 화일을 지정합니다.
replace 테이블에 데이타 넣는 방법 지정
into table TABLE_NAME 데이타를 로드하는 테이블을 지정
fields terminated by ',' 데이타 필드의 종결 문자 지정
(a integer external, 테이블의 열, 외부 데이타 화일의 데이타 형을 지정
b char)



참고로 replace 외에 다음의 옵션이 가능합니다.

replace 테이블의 기존 행을 모두 삭제(delete)하고 insert
append 새로운 행을 기존의 데이타에 추가
insert 비어 있는 테이블에 넣을 때
truncate 테이블의 기존 데이타를 모두 truncate 하고 insert



SQL*Loader를 실행하면 아래의 화일이 작성됩니다.

* 로드 작업 중 동작에 관한 통계 등을 포함한 로그 화일(확장자는 log)
* 데이타 에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)
* 사용자의 선택 기준에 적합하지 않은 레코드를 저장한 화일(discard 화일)
이것은 discardfile 옵션으로 별도로 지정해야 생성됩니다.



실행 방법은 다음과 같습니다.

$sqlldr scott/tiger control=sample.ctl data=sample.dat

---

.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

구현 환경 : Oracle

Source :

-- Tablename.sh

sqlldr userid=id/password
control=./control/TABLE_NAME.ctl
log=./log/TABLE_NAME_$1.log
bad=./bad/TABLE_NAME_$1.bad
discard=./bad/TABLE_NAME_$1.dsc
data=./data/TABLE_NAME_$1.dat


-- TABLE_NAME.ctl

oad data
append
into table TABLE_NAME
fields terminated ','
trailing nullcols
(
RES_NO,
NAME,
TEL
)

-- TABLE_NAME_20021017.dat

주민번호,이름,전화번호
123456-234567,홍길동,011-111-1111
123456-234567,홍길동,011-111-1111
123456-234567,홍길동,011-111-1111
123456-234567,홍길동,011-111-1111
123456-234567,홍길동,011-111-1111

실행 :

% ./Tablename.sh 20021017

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

물론 GUI 의 client 프로그램을 사용하면 좀더 멋지게 사용할수 있겠지요.

그러나 ... 현장에서 보니 telnet 환경에서 필요한것들이 있더군요...

그래서 몇개 소개하겠습니다. 물론 모두 알고 있지만 ... 사람은 기억력이 좋지 않기에..


테이블 확인

SELECT * FROM TAB;


INDEX 확인

DESC USER_INDEXS OR DESC USER_IND_COLUNMS


CONSTRINTS 확인

DESC USER_CONSTRAINTS


STORE PROCEDURE 확인


DESC 스토어 프로시저명

DESC USER_SOURCE


입니다...


SQL > SHOW ALL

하시면 sqlnet 의 환경값이 많이 나오지요....

그중에서 SET SERVEROUT ON, SET PAUSE ON, SET TIMING ON

등 유용한 것들을 사용하면 좀더 .... 좋지 않을까 해서리..

http://www.ihelpers.co.kr/programming/tipntech.php?CMD=view&TYPE=0&KEY=oracle&SC=S&&CC=&PAGE=1&IDX=39

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

SQL EDITOR상에서의 편리한 단축키

ESC: 열린 윈도우 닫기
F1: 모든 단축키를 보여준다.
F2: FULL SCREEN
F4: Table, View, Proc, Funct, Package를 DESC(테이블명에 커서를 둔 상태에서 해야함)
F6: SQL Editor와 결과창간의 전환
F7: 화면을 모두 CLEAR
F8: 실행했던 SQL들의 HISTORY
F9: 모든 쿼리 실행(Ctrl+Enter는 현재 쿼리만 실행)
Ctrl+F9: 실제 쿼리를 실행하지 않고 validate함
Ctrl+. : table Completion (매칭되는 테이블목록 출력)
Ctrl+T : Columns Dropdown (해당테이블의 컬럼목록 표시)
Alt+Up : History Up (리눅스 bash의 UP키와 같다.)
Alt+Down: History Down (리눅스 bash의 DOWN키와 같다.)
Ctrl+Tab: MDI Child윈도간 전환

그냥 'desc 테이블명' 을 치면, 팝업윈도로 해당 테이블의 컬럼목록윈도가 표시된다.
이때, 해당 컬럼을 double-click하면 SQL Editor로 반영된다.

"테이블명."까지 입력하고 좀 있으면, 해당 테이블의 컬럼목록이 표시된다.
마치 프로그래밍툴에서 클래스내 멤버들의 목록을 표시하듯이..
Ctrl+T는 출력된 목록을 취소했을 경우, 다시 불러낼때 쓰인다.

test, member 2개의 테이블이 있다고 하자.
select * from t까지 입력하고 Ctrl+.를 치면 select * from test로 테이블명을 완성시켜준다.
system계정에서는 안되더군.. 일반계정만 가능했다.

SQL문을 실행시

Ctrl+Enter
현재 커서의 SQL문 실행
여러개의 SQL문이 있을때, SQL문들을 개행문자로 분리해주면 된다.
해당 커서에서 개행문자가 있는 곳까지만 하나의 SQL문으로 간주한다.

F9
SQL Editor의 SQL문 실행하여 Grid에 출력.
여러 SQL문들을 개행문자로 구분하지 않는다는 것을 제외하면, 위의 Ctrl+Enter와 동일하다.
에디터내의 모든 SQL문은 하나로 취급하므로, 여러 개의 SQL문이 있으면 실행할 SQL문만 Selection하여 실행(F9)해준다.

F5
SQL Editor내의 모든 SQL문 실행. 여러개의 SQL문들을 모두 실행하여 SQL*PLUS와 같이 출력해준다.
이것은 출력이 Grid로 표시되지 않고 Editor윈도에 표시되므로, SQL*PLUS의 출력과 같이,
다른 편집기로의 복사/붙여넣기 할때 사용하면 유용하다.

내용출처 : [기타] 인터넷 : http://www.ihelpers.co.kr

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

ETCL

Extraction
(추출)
=============================================
• 다수의 소스시스템으로 부터 데이터를
추출하는 기능
---------------------------------------------
• 계정계시스템으로부터 ODS DB로의 데이터 추출은 실시간/배치로 수행
• 현행 계정계 중계로그 디퍼드시스템이 아닌 CDC 도구에 의한
레코드 이미지 방식으로 ODS에 1차 적재후 확장기반DB로 적재
• 대량 데이터 처리는 일마감 후 배치로 적재
• 초기적재는 최초1회 실시하며, 이후 변경적재 수행

---

Transformation
(변환)
=============================================
• DW내 표준화 규칙에 의거 데이터 변환
• 소스시스템 데이터를 DW 시스템 모델 형태
로 변경
---------------------------------------------
• 데이터 필드의 형식 변화
• 규칙에 따른 형태 변경
• 업무 코드의 변형

---

Cleansing
(정제)
=============================================
• 오류데이터를 Check하여 정제Rule 에 따른
데이터 정제 수행
---------------------------------------------
• 필수적인 데이터가 존재하지 않는 경우
• 데이터의 Type(문자형, 숫자형)에 위배되는 값이 들어있는 경우
• 날짜의 경우 날짜로서 유효한 형태를 갖고 있지 않은 경우
• 금액의 경우 유효한 값의 범위를 벗어나는 경우
• 업무 코드의 값이 정해진 범위를 벗어나는 경우
• 데이터가 복수로 존재하며 서로 상충되는 경우

---

Loading
(적재)
=============================================
• 소스시스템으로 부터 추출/변환/정제된
데이터를 타켓 시스템에 적재
• 적재 시간의 최소화
• 데이터 검증 수행
---------------------------------------------
• Load 유틸리티, 병렬 처리 기능 활용
• 일과 중에 On-line을 통한 적재 및 갱신을 수행할 경우에는 병렬
처리 기능을 활용
• 적재 과정에서 오류 발생시 사후 복구처리 체계 확립
• 현행 계정계 및 정보계간 데이터 검증 체계 파악 후 최적의 방법 적용

---

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

------------------------------------------------------------------------------
ETCL 개발주의사항
------------------------------------------------------------------------------

[ 데이터 적재 ]
- 원천파일의 각 컬럼별 자리수가 초과하는지 확인
- 원천파일의 항목이외의 기본값을 넣기위해 로드컨트롤 쿼리를 수정
날짜 SYSDATE
일련번호 RECNUM
시퀀스 SEQUENCE(시작, SEED)
특정값 CONSTANT 'XXX'
- 적재에러를 무시하고 싶은경우 로드옵션에 ERRORS = -1
- 파일FFD, DBFFD 컬럼명칭이 동일해야 매핑됨

[ FFD ]
- 타겟테이블 DBFFD 생성
- 언로드할 원천파일용 FFD를 생성(DBFFD참조)
- 필요에 따라 원천파일 가공이 일어나는 경우 추가 FFD 생성
- 다중선택으로 컬럼복사

[ 데이터 언로드 ]
- 언로드 쿼리에서 모든 문자열은 TRIM해서 가져와야함(로드할때 자리수초과 에러가 많이 남)
- 한개 테이블을 가져올 경우 해당 DBFFD와 내려받을 FDD를 지정해 언로드블럭 사용
- 조인등 기타로직이 들어가는 쿼리인경우 USQL블럭으로 해결함(결과 SELECT문장 직전에 다음문장 삽입)
set TEMPORARY OPTION Temp_Extract_Name1 = '/batnas/Snr/ACR/GCR/CGCR_UNFY_CUST_BASE_TMP_00.OUT';
set TEMPORARY OPTION Temp_Extract_Binary = 'OFF';
set TEMPORARY OPTION Temp_Extract_Swap = 'OFF';
set TEMPORARY OPTION Temp_Extract_Column_delimiter = '^|';
set TEMPORARY OPTION Temp_Extract_Null_As_Zero = 'ON';
set TEMPORARY OPTION Temp_Extract_Append = 'OFF';
- 디폴트경로: $$ACR_GCR_PATH/ACR/

[ 소트옵션 ]
- No Duplication : 설정한 키에 대한 중복제거
- Record정렬 : 모든레코드단위정렬
- Stable : 첫레코드중심 중복제거
- Mastering : 여러개입력파일에서 같은컬럼명 기준 소트

[네이밍규칙]
Project: [P(Project) C(etCl) D(Daily, Monthly) B(unload) or C(load)]_GCR_TargetTable_00
(PCDB, PCDC, PCMB, PCMC_GCR_TargetTable_00)

Block: [ST(sort) MG(merge) GR(group) JN(join) TR(convert) UN(unload) LD(load) SP(usql) SH(shell)
+ A(adw) or N(bsa) or B(badw) or C(etcl)
+ _SourceorTargetTable_00
(LDC_CGCR_CLTR_ADR_BASE_TMP_00)

Control-M: D(Daily or Monthly) M(A계정계 or M정보계) GCR + 2(dbsvr) + 0001(?)
(DMGCR20001)

FILE: BlockName_[YYYYMMDD].dat (dat or log or ffd or ctl or bad)

[디렉토리]
$$ACR_NAS_PATH : /batnas/Snr/ACR
$$ACR_NAS_PATH/GCR/ACR/unload/UNC_CGCR_BC_MEST_TMP_00.out

[로드컨트롤옵션]
direct = true ERRORS = -1 rows=10000 SKIP_INDEX_MAINTENANCE=TRUE

[정합성체크]
- 특정데이터만 데이터적재하고 원천쿼리와 적재쿼리를 돌려 엑셀카피후 화면비교


[출처] ETCL 개발 주의사항|작성자 아이칭
http://blog.naver.com/pie22/130047521869

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

/*
인덱스
INDEX - 인덱스는 "값 + rowid" 로 구성되어 있다.
- 동일한 값을 같은 경우 ROWID순으로 소트되어 있기 때문에 블럭에서 테이블 엑세스가 이루어 진다.

인덱스 컬럼
+ 검색조건에 빈빈히 이용되는 컬럼 혹은 컬럼의 집합(SQL문의 WHERE절)
+ ORDER BY, GROUP BY, UNION, DISTINCT (TEMP TABLESPACE) 등 정렬이 필요한 구문에 빈번히 이용되는 컬럼 혹은 컬럼의 집합
+ 집계함수의 이용에 사용되는 컬럼 혹은 컬럼의 집합
+ 수정이 잦은 컬럼은 인텍스 지정 억제(오히려 부하를 줄수 있다)
+ 외부키(FOREIG KEY)는 클러스터인텍스 지정
클러스터 테이블
= 클러스터는 하나 이상의 테이블이 물리적으로 같이 저장되어 있는 그룹을 말한다.
관련된 행들이 같이 저장되어 있다면 디스크 엑세스는 시간을 개선시킨다.
= 클러스터 안에 있는 관련된 테이블의 컬럼을 CLUSTER KEY라고 말한다.

인덱스 적용 대상
+ 중,대규모 테이블
+ 대상 데이터의 10% 이내의 데이터 요구인 경우(분포도)
+ 스캔의 범위를 줄이고자 할때
+ 정렬을 필요로 하는 경우
+ 테이블에 접근하지 않고 인덱스만으로 데이터 요구를 해결하고자 하는 경우

인덱스 적용시 고려사항
+ 인덱스를 위한 추가적인 기억공간을 필요로 함(INDEX TABLESPACE)
+ 인덱스와 테이블 I/O수의 합계가 전체 테이블을 스캔하는 I/O수보다 적은 경우에만 성능향상에 기여한다고 볼 수 있음(손익분기점: 10~15%)
+ 여러 컬럼을 결합하여 인덱스를 구성하는 경우 컬럼의 순서 중요
+ 입력, 수정 혹은 인덱스 컬럼의 값을 수정하는 경우 인덱스 데이터도 변경되어야 하므로 그에 따른 오버헤드도 고려할 것
- 테이블당 인덱스의 수는 4개 정도가 적당
- 무분별한 인덱스는 등록, 수정, 삭제작업시 부하
+ 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음(RANK 등)

인텍스 대상테이블 선정
+ 테이블의 크기가 5~6블럭 이상
+ 무작위접근(RANDOM ACCESS)이 빈번한 경우
+ 특정범위/순서의 데이터조회가 필요한 경우
+ JOIN의 연결고리가 되는 칼럼
+ 참조 무결성을 지정한 경우 연결고리 되는 컬럼
+ NESTED JOIN이 일어나는 테이블
+ 단순보관용이거나 전체조회용일 경우에는 인덱스를 생성하지 않는다.
+ 여러 개의 인덱스가 있는 경우 동시에 많은 양의 입력/수정/삭제가 이루어지면 부하가 발생하므로 인덱스의 수나 생성시점 또한 중요하다.

분포도와 손익분기점
+ 인덱스를 생성하고자 하는 컬럼의 분포도는 10~15%를 넘지 않아야 한다.
분포도 = 1/컬럼값의 종류 * 100
= 데이터별 평균 로우 수 / 테이블의 총 로우 수 * 100

+ 인덱스의 선택성(SELECTIVITY)
테이블의 전체 레코드중에서 구분값(DISTINCT VALUE)이 차지하는 비율.
선택성 = (구분값의 갯수 / 전체 ROW 갯수) * 100
선택성이 높을 수록 인덱스의 분포도는 양호 - UNIQUE INDEX의 선택성은 1.00
예) 테이블의 레코드수: 1000, 구분값:950 => 선택성: 950/1000 (0.95)

인덱스의 활용[적용기준]
+ 6블럭 이상의 테이블에 적용(6블럭이하는 연결고리만)
+ 컬럼의 분포도가 10~15% 이내인 경우 적용
+ 분포도가 범위이내더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 검토할 것
+ 분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 적용
+ 인덱스만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음(손익분기점)
분포도 1/컬럼값의 종류 * 100 = 컬럼값의 평균 로우수/테이블의 총 로우수 * 100

인덱스의 효율적 이용
+ 일반적으로 가장 좋은 하나의 인덱스만 사용하는 것이 여러 개의 인덱스를 사용하는 것보다 유리하다.
-> 인덱스 머지(INDEX MERGE)의 회피
+ 인덱스 테이블과 데이터 테이블은 각기 다른 디스크에 배치
+ 인덱스 테이블에 대한 적절한 기억공간 할당
+ 테이블 접근없이 인덱스만으로 데이터 요구 해결
+ 인덱스 생성 및 삭제 시점의 효율적 운용 검토
+ 데이터 로드나 일시에 많은 데이터를 입력할 경우 인덱스에 대한 부하를 줄이기 위해
인덱스를 삭제,입력 및 로드가 끝난 이후 재생성
+ 인덱스의 영영은 생성목적과 의미를 내포

데이터 검색 방식
1. FULL-TABLE SCAN
데이터 BLOCK만을 읽는다.
다중 BLOCK I/O를 한다(DB_FILE_MULTIBLOCK_READ_COUNT)
PARALLEL QUERY OPTION 기능을 사용할 수 있다.
SELECT 되는 ROW의 수가 많은 경우에 유리하다.
2. INDEX SCAN
INDEX BLOCK 과 데이터 BLOCK을 읽는다.
다중 BLOCK I/O를 할 수 없다.
SELECT 되는 ROW의 수가 적은 경우에 유리하다.(10~15% 이내)
3. FAST-FULL INDEXED SCAN
SELECT 에 사용된 모든 컬럼이 INDEX 내에 포함이 되는 경우
즉 INDEX에만 접근하면 원하는 데이터가 전부 있는 경우에 사용한다.
FULL-TABLE SCAN 을 해야 하는 경우에 FULL-TABLE SCAN에 대한 대체방법.
FULL-TABLE SCAN 보다 가볍다.
FULL-TABLE SCAN의 장점인 다중 BLOCK I/O와 PARELLEL QUERY를 사용한다.

옵티마이저의 실행계획 작성 우선순위[RULE BASED]
1. ROWID에 의한 1 로우 엑세스
2. 클러스터 조인에 의한 1 로우 엑세스
3. UNIQUE KEY 또는 PRIMARY KEY 를 사용하는 HASH CLUSTER KEY에 의한 1 로우 엑세스
4. UNIQUE KEY 또는 PRIMARY KEY 에 의한 1 로우 엑세스
5. 클러스터 조인
6. 해시 클러스터 키
7. 인덱스 클러스터 키
8. 복합 키
9. 단일 컬럼 인덱스
10. 인덱스 컬럼에서의 바운드 범위 조회
11. 인덱스 컬럼에서의 언바운드 범위 조회
12. 소트 병합 조인
13. 인덱스 컬럼의 최대 또는 최소값
14. 인덱스 컬럼에서의 ORDER BY 사용
15. 테이블의 전체 검색

INDEX를 사용하지 않는 경우
+ 좌변을 가공하지 마라.(인덱스컬럼의 연산, 결합 등의 변형)
-> SAL * 12 = 2000 => SAL = 2000/12로
+ NULL은 가급적 사용하지 마라.(NULL 비교)
+ 부정형 비교( <>, NOT )
+ 함수를 사용하는 경우(SUBSTR, NVL 등)
+ LIKE 문에서 '%AB%',''%AB는 사용목함. ''AB% 가능.
+ WHERE 절에 인덱스가 2개 있다면 인덱스의 우선순위에 의해 LIKE 보다는 = 조건이 작동한다.(취사선택)

-- INDEX COLUMN 의 변형
-- SELECT * FROM DEPT WHERE SUBSTR(DNAME, 1, 2) = 'AC'

-- NOT OPERATOR
-- SELECT * FROM EMP WHERE JOB <> 'SALES'

-- NULL, NOT NULL
-- SELECT * FROM EMP WHERE ENAME IS NOT NULL

-- OPTIMIZER 의 취사선택
-- SELECT * FROM EMP WHERE JOB LIKE 'AB%' AND EMPNO = '7891'

INDEX COLUMN의 변형[EXTERNAL]
+ 모든 내용을 읽어서 서브스트링을 돌리지 말고, INDEX가 잡혀있다면 'AC'로 시작하는 것을 찾아서 그것만 테이블 엑세스
SELECT * FROM DEPT WHERE SUBSTR(DNAME, 1, 2) = 'AC'
-> SELECT * FROM DEPT WHERE DNAME LIKE 'AC%'

+ 모두를 연산한 후 비교하지 말고, 한번만 연산후 그 값인 것을 찾는다.
SELECT * FROM EMPTEST WHERE SAL * 12 = 12000000
-> SELECT * FROM EMPTEST WHERE SAL = 12000000 / 12

+ 1차 인덱스 컬럼은 CHAR타입으로 변경한다.
SELECT * FROM EMPTEST WHERE TO_CHAR(HIRE_DATE,'YYYYMMDD') = '19811203'
-> SELECT * FROM EMPTEST WHERE HIRE_DATE = ''19811203

+ 테이블 설계시에 NULL 을 예방하라.
SELECT * FROM EMP WHERE NVL(COMM, 0) < 100

SELECT * FROM EMP WHERE NVL(JOB,'') = 'CLERK'
-> SELECT * FROM EMP WHERE JOB LIKE 'CLERK%'

+ INDEX가 설정되있는 것을 각각 검색
SELECT * FROM EMP WHERE DEPTNO||JOB = '30MANAGER'
-> SELECT * FROM EMP WHERE DEPTNO = 30 AND JOB = 'MANAGER'

의도적인 SUPPRESSING(억제)
: 의도적인 SUPPRESSING은 타입에 관계없이 RTRIM()을 사용하자.
- 인덱스는 분포도 10~15% 이하의 경우 효율적.
- 분포도가 90% 이면 엄청 속도가 느려진다. 따라서 옵티마이저가 인덱스를 타지 않도록 의도적으로 SUPPRESSING한다.
SELECT * FROM EMP WHERE JOB = 'MANAGER'
-> SELECT * FROM RTRIM(JOB) = 'MANAGER'
-> SELECT * FROM EMP WHERE JOB||'' = 'MANAGER'

SELECT * FROM EMP WHERE EMPNO = 8978
-> SELECT * FROM EMP WHERE EMPNO + 0 = 8978 (NUMBER 형일 경우에만)

- 값이 틀려지면 더 작은 ROWID가 있을 구 있으므로 인덱스 머지 방식은 절대로 사용할 수 없다.
둘중 하나의 인덱스를 선정합니다.랭크를 비교.
둘 다 LIKE 이므로 만들어진 시점을 비교하여 최근에 만들어진 인덱스를 사용합니다. 전부다 비기면 나중에
쓰여진 인덱스를 사용. STATUS인덱스를 사용했다면 엄청 느려짐.
SELECT * FROM AAA WHERE CUST LIKE 'DN%' ABD STATUS LIKE '9%'
-> SELECT * FROM AAA WHERE CUST LIKE 'DN%' AND RTRIM(STATUS) LIKE '9%' (일부러 안쪽의 인덱스를 안쓰게 만들었다.)


CREATE TABLE SAMPLET(
CHA CHAR(10),
NUM NUMBER(12,3),
VAR VARCHAR2(20),
DAT DATE
)


INDEX COLUMN 의 변형(INTERNAL)

SELECT * FROM SAMPLET WHERE CHA = 10
-> SELECT * FROM SAMPLET WHERE TO_NUMBER(CHA) = 10

SELECT * FROM SAMPLET WHERE NUM LIKE '9410%'
-> SELECT * FROM SAMPLET WHERE TO_CHAR(NUM) LIKE '9410%'

SELECT * FROM SAMPLET WHERE DAT = '01-JAN-94'
-> SELECT * FROM SAMPLET WHERE DAT = TO_DATE('01-JAN-94')

+ 서로 다른 DATA TYPE이 비교시의 INTERNAL CONVERSION(옵티마이저의 인공지능은 사람을 따라올 수 없다)

-- EMAIL 은 현재 VARCHAR2형
SELECT * FROM EMPLOYEES WHERE EMAIL = 100 -- (문자열을 숫자형으로 비교, FULL SCAN)
-> SELECT * FROM EMPLOYEES WHERE EMAIL = '100' -- (TO_CHAR(100) 로 변화하여 검색)

-- DATE형: FULL SCAN -> 옵티마이저가 TO_DATE()로 변환한다.
SELECT * FROM EMPLOYEES WHERE HIRE_DATE = '99/06/21'
-> SELECT * FROM EMPLOYEES WHERE HIRE_DATE = TO_DATE('99/06/21')


NOT OPERATOR
+ 부정을 긍정으로 만들어서 사용한다.
SELECT * FROM EMP WHERE JOB <> 'SALESMAN'
-> SELECT * FROM EMP E WHERE E.EMPNO > 0 AND NOT EXISTS(SELECT * FROM EMP B WHERE B.EMPNO = E.EMPNO AND B.JOB = 'SALESMAN')
SELECT * FROM EMP WHERE EMPNO > 0 MINUS SELECT * FROM EMP B WHERE B.JOB = 'SALESMAN'

NULL, NOT NULL
+ NULL, NOT NULL 비교인 경우 인덱스 사용 안함.
+ NULL 이 정말 필요한지를 고민해봐야 한다. 테이블 설계시 디폴트값 셋팅이 필요한지 검토
+ NULL 이 필요한 경우는 정말 극히 드물다.

SELECT * FROM EMPLOYEES WHERE JOB_ID IS NOT NULL;
-> SELECT * FROM EMPLOYEES WHERE JOB_ID > ' ';

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IS NOT NULL;
-> SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID >= 0;

SELECT * FROM EMPLOYEES WHERE JOB_ID > ' ' AND EMPLOYEE_ID IS NULL;


OPTIMIZER 의 취사선택
+ 랭킹의 차이
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID = 'SH_CLERK'
(ENAME INDEX만 사용, LIKE 보다 = 이 우선임.)

+ 인덱스머지의 회피
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID LIKE 'S%';
(ENAME or JOB INDEX중 하나만 사용.)

+ 힌트에 의한 선택
SELECT /*+INDEX(EMP_JOB_IDX)* / * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID LIKE 'S%'
(같은 조건일때 힌트를 사용)


RULE_based vs COST_based - 차이점 예제
+ SqlPlus 에서 SHOW PARAMETER OPT 하면... optimizer_mode string CHOOSE

SQL> conn system
암호 입력: ********

SQL> show parameter opt;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2

현재 설정은 CHOOSE.
즉 오라클이 알아서 CBO 나 RBO를 선택하겠다는 ㅤㅆㅡㅅ이다.(Analyze작업이 되면 cost방식으로 해석 - dept테이블로 확인)

+ CBO를 적용하기 위해선 ANALYZE작업이 선행되어야 한다.
+ ANALYZE작업에 의한 통계값이 없다면 오라클은 정확하게 CBO를 수행할 수 없다.
+ 일단 analyze를 하게 되면 옵티마이저는 choose일 경우 cost를 선택하게 된다.
이때 통계치에 의해 기존 rbo로 수행되면 sql문의 실행계획이 달라지게 된다.
-> Cost를 사용하기 위해서느 ㄴ전체 app의 sql문을 수정할 필요가 있다.


+ RULE based
-- INDEX merge(and_equel)
SELECT * FROM EMPLOYEES WHERE JOB_ID = 'MK_REP' AND LAST_NAME = 'Fay';
-- 항상 EMPLOYEES_ID index만 사용
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND EMPLOYEE_ID = '202';
-- 항상 나중에 생성된 Index만 사용(job_id)
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID BETWEEN '202' AND '9000' AND JOB_ID LIKE 'MK%';

+ Cost based
-- INDEX merge(and_equel), 특정 index
SELECT * FROM EMPLOYEES WHERE JOB_ID = 'MK_REP' AND LAST_NAME = 'Fay';
-- 분포도에 따라 LAST_NAME index도 사용
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND EMPLOYEE_ID = '202';
-- 분포도에 따라 index 사용, 혹은 Full-Scan
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND JOB_ID LIKE 'MK%';


ANALYZE
-- 명령은 다음과 같다.
ANALYZE TABLE HR.EMPLOYEES COMPUTE STATISTICS;

+ 옵티마이저가 CBO로 수행되기 위해서는 ANALYZE에 의한 통계 정보가 있어야만 하며...
다음과 같이 확인해 볼 수 있다.

CONN SYSTEM

SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS AS EMPTY, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
FROM DBA_TABLES
WHERE OWNER = 'HR'
AND TABLE_NAME = 'EMPLOYEES';

+ 인덱스 Rebuild(Index Reorganization)를 위한 정보도 얻을 수 있다.


ANALYZE 명령어
+ Cost-Based 방식에 사용할 통계데이터를 모아 준다.
+ Analyze 명령어는 수행될 때마다 다음의 정보를 수정
- table
테이블의 행수, 사용된 블록 수, 한번도 사용되지 않은 블록 수,
사용가능한 Space의 평균, Chained Row수, 컬럼당 Distinct Value의 수,
컬럼당 두 번째로 가장 작은 값, 컬럼당 두번째로 가장 큰 값.
- INDEX
-> Index level, Level 블록 수, Distinct Key 수, 키당 leaf 블록 수의평균,
키당 Data 블록 수의 평균, Clustering Factor, 최소키 값, 최대키 값.







*/

Index Merge


select employee_id, hire_date from employees where salary between 8600 and 10000 and hire_date like '94%';
-- 결합 인덱스 생성
-> create index hire_date_salary_idx on employees(hire_date, salary);
select salary, hire_date from employees where salary between 8600 and 10000 and hire_date like '94%';


select * from employees where hire_date like '95/%';
-> select * from employees where hire_date in ( '95/10/17','95/07/14','95/05/18','95/05/01');
-> select * from employees where hire_date in ( select hire_date from employees where hire_date like '95/%' );

결합인덱스의 조건 : equal이 결합인덱스에 미치는 영향

- 결합인덱스(COL1, COL2)
+ 결합 인덱스가 WHERE 조건에 나올 경우 순서에 의해 효율의 차이가 많이 날수 있다.
결합인덱스 선택에서 EQUAL로 나오게 할 수 있느냐가 중요하다.

: 결합인덱스 컬럼 결정 순서
a. 항상 조건이 될 수 있는 컬럼이 제일 앞에 와야 한다.
b. 항상 = 로 나올수 있는가?
-> = 로 만들수 있는가 까지 볼수 있어야 겠다.
c. 분포도가 좋은 컬럼을 우선으로 한다.
d. sort 순서는?
결합인덱스 구성 컬럼 중 sort을 해야 하는 컬럼에 대한 결정.
e. 어떤 컬럼을 추가? (후보선수)

인덱스 선정 절차
+ 해당 테이블의 엑세스 유형 조사
+ 대상 컬럼의 선정 및 분포도 분석
+ 반복 수행되는 엑세스 경로의 해결
+ 클러스터링 검토(인덱스만으로도 안되는 경우가 있다.)
+ 인덱스 컬럼의 조합 및 순서의 결정
+ 시험생성 및 테스트
+ 수정이 필요한 애플리케이션 조사 및 수정

엑세스 유형이 조사(설계단계)
+ 반복 수행되는 엑세스 형태를 찾는다.
+ 분포도가 아주 양호한 컬럼을 찾아 인덱스 유형을 찾는다.
+ 자주 넓은 범위의 조건이 부여되는 경우를 찾는다.
+ 자주 조건절에 사용되는 컬럼들의 엑세스 유형을 찾는다.
+ 자주 결합되는 사용되는 경우를 찾는다.
+ 소트의 유형을 조사한다.
+ 일련번호를 부여하는 경우를 찾는다.(PK)
+ 통계자료 추출을 위한 엑세스 유형을 조사항다.(거의 대용량일 경우 대량의
데이터를 읽어서 GROUP BY 등을 하는 경우 클러스터링 고려)

인덱스의 선정기준
+ 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상
+ 자주 조합되어 사용되는 경우는 결합인덱스 생성
+ 각종 액세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담
+ 가능한 수정이 빈번하지 않는 컬럼(발란스트리 깨짐)
+ 기본키 및 외부키(조인 연결고리가 되는 컬럼)
+ 결합 인덱스의 컬럼 순서 선정에 주의
+ 반복수행 되는 조건은 가장 빠른 수행 속도를 내게 할 것
+ 실제 조사된 액세스 종류를 토대로 선정 및 검증

인덱스의 선정기준
+ 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상
+ 자주 조합되어 사용되는 경우는 결합인덱스 생성
+ 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스 간의 역활 분담
+ 가능한 수정이 빈번하지 않는 컬럼(발란스트리 깨짐)
+ 기본키 및 외부키(조인 연결고리가 되는 컬럼)
+ 결합 인덱스의 컬럼 순서 선정에 주의
+ 반복수행되는 조건은 가장 빠른 수행 속도를 내게 할 것
+ 실제 조사된 엑세스 종류를 토대로 선정 및 검증

인덱스 고려사항
+ 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음
(실행 계획이 바뀔 수 있다.)
+ 지나치게 많은 인덱스는 오버헤드 발생
+ 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생
랜덤엑세스(rowid에 의한 direct엑세스가 다량 발생)
+ 옵티마이저를 위한 통계데이터를 주기적으로 갱신(CBO)
+ 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
+ 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
트랜잭션이 많은 경우(OLTP) 최소로 인덱스를 만들어야 한다.
+ 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음
+ 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐
+ 조인시에 인덱스가 사용여부에 주의

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

/*
부분범위 처리
= 처리할 내용이 아무리 많더라도 일단 운반단위에 차게 되면 데이터를 RETURN시키고,
사용자가 다음 ACTION을 취할 때까지 기다리므로 처리범위의 크기에 영향을 받지 않도록 하는 기법
- 조건을 만족하는 전체 집합이 아닌 일부분만 엑세스
- 데이터량이 많아도 퍼포먼스에 지정이 없고, 오히려 향상
- 인덱스나 클러스터를 적절히 활용한 소트의 대체(ORDER BY 를 안하도록 한다.)
그 외..
- 테이블을 엑세스하지 않고 인덱스만 사용하도록 유도
- EXIST의 활용(IN 보다 효율적, EXISTS 는 옵티마이저가 부분범위처리한다.)
- ORDER BY 생략(불필요한 정렬작업 생략)
- MIN / MAX
- ROWNUM의 활용 -> STOP KEY 를 이용
- QUERY를 이원화 하여 일부분씩 스캔 하도록 유도(소트의 범위를 줄임)
등의 활용예를 통해 부분범위 처리를 확인해 볼 수 있다.

옵티마이저 힌트는 적대적인 것은 아니다. 힌트를 줘 놓고 힌트를 쓸수 있는 요소가 없으면 힌트가 사용되지 않는다.
때문에 멍텅구리 조건을 주는 경우가 있다.
- 인덱스가 있는 컬럼을 조건절에 넣는다.(단, 추출하려는 데이터에 영향을 미치지 않는 한도내에서.)

* 전체범위처리 : FULL RANGE SCAN 후 가공하여 ARRAY SIZE 만큼 추출
* 조건을 만족하는 ROW 수가 ARRAY SIZE에 도달되면 멈춤(STOP KEY)
*/

/*
SORT 를 대신하는 인덱스
*/
-- 싱글 인덱스
-- SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%' ORDER BY YMD, ITEM;

-- 결합 인덱스
-- SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%';

/*
INDEX 생성
*/
-- create index HIREDATE_IDX ON EMPLOYEES(HIRE_DATE)

/*
MAX 처리
*/
-- SELECT MAX(HIRE_DATE) FROM EMPLOYEES;
-- SELECT /*+INDEX_DESC(EMPLOYEES HIREDATE_IDX) */ HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE < '20/12/30' AND ROWNUM = 1;

-- SELECT MAX(EMPLOYEE_ID)+ 1 FROM EMPLOYEES WHERE DEPARTMENT_ID = 100;
-- SELECT /*+INDEX_DESC(EMPLOYEE_ID) */ EMPLOYEE_ID + 1 FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 AND ROWNUM = 1;

/*
EXISTS
*/

-- SELECT COUNT(*) FROM hr.EMPLOYEES WHERE DEPARTMENT_ID = 100 AND EMPLOYEE_ID > 10 ;
-- SELECT 1 FROM DUAL WHERE EXISTS(SELECT 'X' FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 AND EMPLOYEE_ID < 10);


/*
범위가 좁은것 부터 큰 것으로 조건을 지정
*/
-- select * from employees where employee_id between 10 and 10000 and department_id = '100';
-- select /*+index_asc(department_id, employee_id)*/ * from employees
-- where department_id = '100' and employee_id between 10 and 10000 ;

/*
인덱스를 최대한 활용하라.
*/

-- select min(salary) from employees;
-- select min(salary) from employees where salary > 0 and rownum = 1;


/*
부분범위 처리가능시 최초운반단위를 빨리 채우도록 한다.
*/

-- select last_name, salary * 100 from employees where hire_date like '9%' order by last_name;
-- select last_name, salary * 100 from employees where hire_date like '9%' and last_name > ' ' ;


/*
UNION 대신 UNION ALL을 사용하라.
*/

-- select last_name, salary * 100 from employees where hire_date like '96%'
-- union
-- select last_name, salary * 100 from employees where hire_date not like '96%' ;


-- select last_name, salary * 100 from employees where hire_date like '96%'
-- union all
-- select last_name, salary * 100 from employees where hire_date not like '96%' ;

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

Hash Function을 이용해서 메모리와 CPU를 많이 사용해서 일반적으로 배치작업에서 주로 사용됨


-. /*+ use_hash(테이블) */
-. 적은테이블과 큰테이블의 조인시에 유리
-. Equal 조인에서만 가능
-. Driving Table에 인덱스를 필요로 하지 않고 각 테이블을 한번만 읽음
-. 다른조인방법보다 CPU자원을 많이 소비하며 양쪽 테이블의 scan이 동시에 일어남


============================================================

해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식 입니다.

성능을 위해서는 당연히 사이즈가 작은 테이블이 메모리에 올라가는 것이 좋으며 이때 이 테이블을 드라이빙 테이블(driving/outer table) 이라고 합니다. 특히 이 해시 테이블이 메모리에 생성되면 성능은 좋으며(메모리에 생성되지 않으면 내부적으로 임시 테이블이 만들어 져야 합니다.) 두 테이블의 크기 차이가 클수록 성능은 좋아집니다.

또한 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위 검색(Range scan)이 아닌 동등 비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합 합니다.


.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장