Oracle 47일차

[실습 – professor]

professor.csv 를 oel7 서버 업로드 후(winscp – text) 해당 데이터를 새로운 테이블로 생성한 뒤 적재(테이블명 : sqlldr_apply)

1. 테이블 생성

CREATE TABLE SCOTT.PROFESSOR
(
    PROFNO      NUMBER(4),
    NAME        VARCHAR2(20) NOT NULL,
    ID          VARCHAR2(20) NOT NULL,
    POSITION    VARCHAR2(20) NOT NULL,
    PAY         NUMBER(3) NOT NULL,
    HIREDATE    DATE NOT NULL,
    BONUS       NUMBER(4),
    DEPTNO      NUMBER(3),
    EMAIL       VARCHAR2(55),
    HPAGE       VARCHAR2(55)
)
TABLESPACE USERS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

ALTER TABLE SCOTT.PROFESSOR
ADD PRIMARY KEY (PROFNO);

-- 또는 DBlink이용해서 create하기

create table scott.professor
as
select *
from scott.professor@dblink_orcl
where 1=2;
-- where 1=2는 행은 복사 안하고 구조만 가져오려고 할 때 쓰는 것.

2. winscp text모드로 professor.csv 옮기기

# 리눅스에서 한글 인코딩이 깨짐. 그래서 iconv쓰기
$ cat professor.csv
$ iconv -c -f euc-kr -t UTF-8 professor.csv --output professor.csv
$ cat professor.csv

3. 적재

-- 컨트롤 파일 생성
$ vi professor.ctl
options (skip=1)
load data
characterset utf8
infile '/home/oracle/sqlldr/professor.csv'
replace
into table professor
fields terminated by ',' trailing nullcols
(profno,
name,
id,
position,
pay,
hiredate "TO_DATE(:hiredate, 'YYYY-MM-DD HH24:MI:SS')",
bonus,
deptno,
email,
hpage)

characterset utf8 : 한글 처리
trailing nullcols : 맨 마지막에 값이 없는 컬럼(hpage) 자동으로 null 처리
"TO_DATE(:hiredate, 'YYYY-MM-DD HH24:MI:SS')" : date 형식을 지정해줌.

-- sqlldr 실행
$ sqlldr scott/oracle control=professor.ctl

-- 데이터 조회
select * from scott.professor;

-- TO_DATE 형식 지정 안쓸거면 데이터타입 바꾸랬어
alter table scott.professor modify hiredate varchar2(20);

[실습 – student]

1. table 생성

create table scott.student2
as
select *
from scott.student
where 1=2;

2. winscp text모드로 student.csv 옮기기

# 리눅스에서 한글 인코딩이 깨짐. 그래서 iconv쓰기
$ cat student.csv
$ iconv -c -f euc-kr -t UTF-8 student.csv --output student.csv
$ cat student.csv

3. 적재

-- 컨트롤 파일 생성
$ vi student.ctl
options (skip=1)
load data
characterset utf8
infile '/home/oracle/sqlldr/student.csv'
replace
into table student2
fields terminated by ',' trailing nullcols
(STUDNO,
NAME,
ID,
GRADE,
JUMIN,
BIRTHDAY "to_date(:birthday,'YYYY/MM/DD HH24:MI:SS')",
TEL,
HEIGHT,
WEIGHT,
DEPTNO1,
DEPTNO2,
PROFNO)

characterset utf8 : 한글 처리
trailing nullcols : 맨 마지막에 값이 없는 컬럼 자동으로 null 처리
"TO_DATE(:hiredate, 'YYYY-MM-DD HH24:MI:SS')" : date 형식을 지정해줌.

-- sqlldr 실행
$ sqlldr scott/oracle control=student.ctl

-- 데이터 조회
select * from scott.student2;

[case5 : 여러 파일 적재하기]

window에 있는 act_data_1.csv ~ act_data_6.csv 파일을 winscp를 사용하여 oel7 서버에 업로드 후 해당 파일 데이터를 아래 테이블 생성 후 모두 적재.
참고) 수동으로 6개를 로딩해도 되지만, 여유가 되면 쉘로 여러 파일을 적재하는 식으로 작성

create table scott.sqlldr_act
(user_id number,
vdate date,
act_1_cnt number,
act_2_cnt number,
act_3_cnt number,
act_4_cnt number,
act_5_cnt number,
act_6_cnt number,
act_7_cnt number,
act_8_cnt number,
act_9_cnt number,
act_10_cnt number,
act_11_cnt number,
act_12_cnt number,
act_13_cnt number,
act_14_cnt number,
act_15_cnt number,
act_16_cnt number,
act_17_cnt number);

1. 컨트롤 파일 만들기

act_data.ctl

options (skip=1)
load data
append
into table sqlldr_act
fields terminated by ','
(user_id,
vdate "to_date(:vdate,'YYYY-MM-DD')",
act_1_cnt,
act_2_cnt,
act_3_cnt,
act_4_cnt,
act_5_cnt,
act_6_cnt,
act_7_cnt,
act_8_cnt,
act_9_cnt,
act_10_cnt,
act_11_cnt,
act_12_cnt,
act_13_cnt,
act_14_cnt,
act_15_cnt,
act_16_cnt,
act_17_cnt)

2. 쉘 만들기

act_data.sh
– 매번 act_data_.csv의 번호가 달라지기 때문에 컨트롤 file에 있던 infile부분을 없애고

#!/bin/sh

# 변수 설정
CONTROL_FILE="act_data.ctl"

# 반복문으로 데이터 파일 전달
for a in {1..6}
do
    echo "===== SQL*Loader 실행 시작: ${a} ====="
    sqlldr scott/oracle control=${CONTROL_FILE} data=/home/oracle/sqlldr/act_data_${a}.csv silent=header,feedback
    echo "===== SQL*Loader 실행 완료: ${a} ====="
done

echo "모든 SQL*Loader 작업이 완료되었습니다."

# 강사님이 짠 쉘
for fname in $(ls -v act*.csv)
do
  cp $fname $dir/backup
  mv $fname act_data.csv

  echo "파일적재 시작" | tee -a $dir/sqlldr_act.log
  sqlldr scott/oracle control=act.ctl silent=header,feedback | tee -a $dir/sqlldr_act.log
  echo "파일적재 종료" | tee -a $dir/sqlldr_act.log
done

3. 적재

$ sh act_data.sh

-- 조회 (잘 나온다~)
select * from scott.sqlldr_act;

[case6 : sqlldr 전송 모드]

** conventional mode (default)
– 소량의 데이터를 적재하는 기본 모드.
– 모든 기록을 정상적으로 수행 (db buffer cache에 기록->DBWR 동작, redo, undo에 기록)
– 한 건씩 insert & commit 수행하는 느낌.

** direct mode
– 대용량 데이터를 보다 빠르게 적재하기 위한 모드.
– db buffer cache를 거치지 않고 직접 disk에 기록.
– redo, undo에 기록을 최소화.
-> archive와 redo에 기록 안 남으니 recover 불가
-> 적재 직후 backup 받는 게 필수.

.log에 실행시간이 나오니 확인 ㄱㄱ

1. conventional mode로 act_data_6.csv 적재

-- 컨트롤 파일 생성 $ vi act6.ctl
options (skip=1)
load data
infile '/home/oracle/sqlldr/act_data_6.csv'
append
into table sqlldr_act
fields terminated by ','
(user_id,
vdate "to_date(:vdate,'YYYY-MM-DD')",
act_1_cnt,
act_2_cnt,
act_3_cnt,
act_4_cnt,
act_5_cnt,
act_6_cnt,
act_7_cnt,
act_8_cnt,
act_9_cnt,
act_10_cnt,
act_11_cnt,
act_12_cnt,
act_13_cnt,
act_14_cnt,
act_15_cnt,
act_16_cnt,
act_17_cnt)

-- sqlldr conventional mode로 실행
$ sqlldr scott/oracle control='act6.ctl' silent=header,feedback

-- log에서 실행시간 확인
Elapsed time was:     00:00:03.20
CPU time was:         00:00:00.78

2. direct mode로 act_data_6.csv 적재

-- sqlldr direct mode로 실행
$ sqlldr scott/oracle control='act6.ctl' silent=header,feedback direct=y

-- log에서 실행시간 확인
Elapsed time was:     00:00:01.06
CPU time was:         00:00:00.33

[case7 : 원격 적재]

tns alias를 사용한 원격 적재 기능.
target DB 정보를 local의 tnsnames.ora 파일에 기록해놔야 가능.

1. 테이블 생성

create table scott.sqlldr_bigmart
(
Item_Identifier varchar2(10),
Item_Weight number,
Item_Fat_Content varchar2(10),
Item_Visibility number,
Item_Type varchar2(20),
Item_MRP number,
Outlet_Identifier varchar2(10),
Outlet_Establishment_Year number,
Outlet_Size varchar2(10),
Outlet_Location_Type varchar2(10),
Outlet_Type varchar2(20),
Item_Outlet_Sales number
);

select * from scott.sqlldr_bigmart;

2. windows에서 컨트롤 파일 생성

bigmart.ctl

options (skip=1)
load data
infile './bigmart_train.csv'
replace
into table sqlldr_bigmart
fields terminated by ','
(Item_Identifier,
Item_Weight,
Item_Fat_Content,
Item_Visibility,
Item_Type,
Item_MRP,
Outlet_Identifier,
Outlet_Establishment_Year,
Outlet_Size,
Outlet_Location_Type,
Outlet_Type,
Item_Outlet_Sales)

3. cmd 창에서 적재

tnsnames.ora에 지정해둔 걸 @ 뒤에 써야 됨.

cd \Users\itwill\Desktop

sqlldr scott/oracle@db1 control='bigmart.ctl' silent=header,feedback

-- 데이터 조회 (잘 나옴)
select * from scott.sqlldr_bigmart;

** 빠른 적재를 위한 팁

1. direct mode

2. 제약조건 및 인덱스 비활성화

3. insert select 시 parallel hint 적용

4. insert select 시 append hint

[실습 – pk 여부에 따른 속도 차이]

pk disable 상태가 속도가 더 빠르다.

1. 테이블 비우기

truncate table scott.sqlldr_act;

2. user_id, vdate 컬럼에 pk 생성

-- 중복값 있는지 확인하는 쿼리
SELECT user_id, vdate, COUNT(*)
FROM scott.sqlldr_act
GROUP BY user_id, vdate
HAVING COUNT(*) > 1;

-- 중복값 삭제하는 쿼리
DELETE FROM scott.sqlldr_act a
WHERE ROWID > (
    SELECT MIN(ROWID)
    FROM scott.sqlldr_act b
    WHERE a.user_id= b.user_id
);
-- user_id=10404, 2010-08-05 가 2개있어서 1개 직접 vi 들어가서 지움.

-- PK 제약조건 생성 쿼리
ALTER TABLE scott.sqlldr_act
ADD CONSTRAINT pk_user_id_vdate PRIMARY KEY (user_id, vdate);

3. act_data_6.csv 파일 적재 (속도 확인)

-- 적재
sqlldr scott/oracle control='act6.ctl' silent=header,feedback

-- 속도 확인
cat act6.log
Elapsed time was:     00:00:05.10
CPU time was:         00:00:00.87

4. 제약조건 비활성화->적재->제약조건 활성화 (속도 확인)

truncate table scott.sqlldr_act;
-- 제약조건 비활성화
alter table scott.sqlldr_act disable constraint pk_user_id_vdate;

-- 적재
sqlldr scott/oracle control='act6.ctl' silent=header,feedback

-- 속도 확인
cat act6.log
Elapsed time was:     00:00:03.20
CPU time was:         00:00:00.79

-- 제약조건 활성화
alter table scott.sqlldr_act enable constraint pk_user_id_vdate;