[] Direct Path Insert
[] nologging 모드 Insert
alter table t NOLOGGING;
[] 최소 로깅(minimal nologging)
alter database SQLPRO set recovery SIMPLE
1) 파일 데이터를 읽어 DB로 로딩하는 Bulk Insert 구문을 사용할 때, With 옵션에 TABLOCK 힌트를 추가.
BULK INSERT Adventure Works.Sales.SalesOrderDetail
FROM 'C:\orders\lineitem.txt'
WITH
(
DATAFILETYPE = 'CHAR',
FIELDTERMINATOR = ' |',
ROWTERMINATOR = ' |\n',
TABLOCK
)
2) 복구 모델이 'Bulk-logged' 또는 'Simple'로 설정된 상태에서 select into 사용.
select * into target from source;
3) SQL Server 2008 버전부터 힙(Heap) 테이블에 Insert할 때 TABLOCK 힌트를 사용.
이때, X 테이블 Lock 때문에 여러 트랜잭션이 동시에 Insert 할 수 없게 됨.
insert into t_heap with (TABLOCK) select * from t_source
필요한 다른 조건 : 비어있는 B*Tree 구조에서 TABLOCK 힌트 사용
비어있는 B*Tree 구조에서 TF-610을 활성화
비어 있지 않은 B*Tree 구조에서 TF-610을 활성화하고, 새로운 키 범위만 입력
TABLOCK 힌트가 반드시 필요하지 않으므로 입력하는 값 범위가 중복되지 않는다면 동시 Insert도 가능함.
use SQLPRO
go
alter database SQLPRO set recovery SIMPLE
DBCC TRACEON(610);
insert into t_idx
select * from t_source
order by col1 ------------ t_idx 테이블의 클러스터형 인덱스 키 순 정렬
대량의 데이터 변경시 오랜 시간이 걸릴 수 있음.
Update문을 이용하는 것보다 아래 방식으로 처리하는 것이 더 빠를 수 있음.
1. 대상테이블의 데이터로 temp 테이블 생성
2. 대상테이블의 제약조건 및 인덱스 삭제
3. 대상테이블 truncate
4. temp 테이블에 있는 원본 데이터를 update 할 값으로 수정하여 대상테이블에 insert
5. 대상테이블에 제약조건 및 인덱스 생성
[] 전통적인 방식의 Update문
update 고객
set (최종거래일시, 최근거래금액) = (select max(거래일시), sum(거래금액)
from 거래
where 고객번호 = 고객.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select 'x'
from 거래
where 고개번호 = 고객.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1))
);
[] SQL Server 확장 Update문 활용
update 고객
set 최종거래일시 = b.거래일시
, 최근거래금액 = b.거래금액
from 고객 a
inner join (select 고객번호, max(거래일시) 거래일시, sum(거래금액) 거래금액
from 거래
where 거래일시 >= dateadd(mm, -1, convert(datetime, convert(char(8), getdate(), 112), 112))
group by 고객번호
) b
on a.고객번호 = b.고객번호
[] Oracle 수정 가능 조인 뷰 활용
update /*+ bypass_ujvc */
(select c.최종거래일시
, c.최근거래금액
, t.거래일시
, t.거래금액
from (select 고객번호, max(거래일시) 거래일시, sum(거래금액) 거래금액
from 거래
where 거래일시 >= trunc(add_months(sysdate, -1))
group by 고개번호) t
, 고객 c
where c.고객번호 = t.고객번호)
set 최종거래일시 = 거래일시
, 최근거래금액 = 거래금액
| 1 : M = M | M쪽 테이블이 키-보존 테이블 |
| 1 : 1 = 1 | 1쪽 테이블이 키-보존 테이블 |
| M : N = M * N | 키-보존 테이블 없음 |
| M : 1 = M | M쪽 테이블이 키-보존 테이블 |
drop table t1;
create table t1(c1 number, c2 varchar2(1));
insert into t1
select rownum
, 'x'
from dual
connect by level <= 10
;
create unique index idx1_t1 on t1(c1);
-- t1 테이블의 자식 테이블인 t2 생성
drop table t2;
create table t2(c1 number, c2 number, c3 varchar2(1));
insert into t2
select mod(rownum,10) + 1
, rownum
, 'x'
from dual
connect by level <= 100
;
create unique index idx1_t2 on t2(c1, c2);
-- t1 컬럼 수정 => 키보존 테이블이 아니므로 수정 불가
SQL> update (select t1.c1, t1.c2, t2.c3
2 from t1
3 , t2
4 where t1.c1 = t2.c1
5 )
6 set c2 = 'A'
7 ;
set c2 = 'A'
*
6행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
-- t2 컬럼 수정 => 키보존 테이블이므로 수정 가능
SQL> update (select t1.c1, t1.c2, t2.c3
2 from t1
3 , t2
4 where t1.c1 = t2.c1
5 )
6 set c3 = 'A'
7 ;
100 행이 갱신되었습니다.
-- t1 테이블 인덱스를 제거해서 M : N 관계로 만든 후 t2 컬럼 수정 => 수정 불가
SQL> drop index idx1_t1;
인덱스가 삭제되었습니다.
경 과: 00:00:00.00
SQL>
SQL> update (select t1.c1, t1.c2, t2.c3
2 from t1
3 , t2
4 where t1.c1 = t2.c1
5 )
6 set c3 = 'A'
7 ;
set c3 = 'A'
*
6행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
[] Oracle Merge문 활용
merge 문 : insert, update, delete 작업을 한번에 처리 가능.
9i 부터 제공.
10g 부터 delete 작업, update/insert 선택적 처리 가능.
-- Updatable Join View 기능을 대체
merge into 고객 c
using (select 고객번호
, max(거래일시) 거래일시
, sum(거래금액) 거래금액
from 거래
where 거래일시 >= trunc(add_months(sysdate, -1))
group by 고객번호) t
on (c.고객번호 = t.고객번호)
when matched then update set c.최종거래일시 = t.거래일시
, c.최근거래금액 = t.거래금액