SQLP 50회 -2

실기2 (1~3번 모두 로깅을 최소화하는 문제)

​

1번

insert into t1 select * from t2

​

답

alter table t1 nologging;

insert문에 APPEND 삽입

해설

append 힌트는 테이블이 nologging일 때만 작동합니다.

​

2번

조건(c1,c2 는 둘다 not null)

update t1 set c2 = case when c1 < trunc(add_months(sysdate,2)) then 'Y' else c2 end

​

답

update t1 set c2 = 'Y'

where c1 < trunc(add_months(sysdate,2))

and c2 <> 'Y' ;

​

해설

CASE 조건절 WHERE절로 변경하여 C2 <> 'Y'인 행은 갱신안되도록 변경

​

3번

아래쿼리의 조건은 t1 테이블의 95퍼가량 데이터 삭제하는 쿼리이다.

delete from t1 partition(20402) where c1='Y'

​

답​

답안1

CTAS 임시테이블생성 -> TRUNCATE -> INSERT로 변경

​

create table tmp nologging

as select * from t where 1=2;

​

insert /*+ append */ into tmp

select * from t partition(p202402) where c1 <>'Y';

​ SELECT * FROM t PARTITION (p202402);

 

alter table t truncate partition p202402;

​ ALTER TABLE t TRUNCATE PARTITION p202402;

 

alter table t nologging;

​

insert /*+ append */ into t

select * from tmp;

​

alter table t logging;

​

drop table tmp;

​

​

* 임시 테이블 생성시 nologging로 생성

* 임시테이블에서 t1으로 인서트 전에도 테이블 노로깅이 추가되야 한다고 봅니다.

​

답안2

alter table t truncate partition p202402

 

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

2번

3문제가 있었고 각각 로깅을 최소하하게 튜닝하라는 스타일

 

1)

insert into t1 select * from t2

 

닫기

insert 시에는 nologging + append 사용하여 로깅을 최소화하고 direct path insert 할 수 있음.

alter table t1 nologging;
insert /*+ append */ t1 select * From t2;

2)

c1,c2 는 둘다 not null
update set t1 c2 = (case when c1 < trunc(add_months(sysdate,2)) then 'Y' else c2 end);

 

닫기

update set t1
c2 = 'Y'
where c1  < trunc(add_months(sysdate,2))
and c2 != 'Y';

위의 case 절은 이미 Y인 로우들까지 모두 탐색후에 update를 수행한다.
이미 C2='Y'인 로우는 제거하고 수행하자.

3)

 

t1의 95%를 삭제하는 쿼리.
delete from t1 partition (202402) where c2='Y';

닫기

특정 파티션의 95%를 삭제하는데 일반 DML로 삭제하여 로깅이 많이 발생되고 있음.

파티션의 특성을 활용하여 5%데이터만 tmp테이블이 넘기고 truncate 후 다시 insert하여 로깅을 줄이기.

 

tmp 테이블 nologging으로 생성 -> insert tmp c2 !='Y' nologging +append -> t1 truncate -> insert t1 c2 != 'Y' nologgin + append

create table tmp nologging
as select * from t1 where 1=2;


insert /*+ append */ into tmp 
select * from t1 partition (202402) where c2 != 'Y';
commit;

alter table t1 truncate partition(202402); 

※실제 문제에서 이부분에 대한 명령어가 헷갈려 잘 못적었던 기억이 있네요..

alter table t1 nologging;
insert /*+ append */ into t1
as select * from tmp;
commit;

alter table t1 logging;
drop table tmp;