PK 가 없는 경우 PK 가 있는 경우에 따라 시간이 얼마나 차이나고, 가장 빠른 변환 방법은 무엇인가 고민해보자
-- identity, pk, int nonclustered index -> identity, pk bigint 로 변경시간 테스트
if object_id('t_product') is not null
drop table t_product
go
-- 재현을 위해서 int identity 컬럼을 만든다.
create table t_product
( idx int identity(1,1)
, shopid int
, productid int
, contentOtherCol char(400)
)
go
-- PK 인덱스를 추가한다.
alter table t_product add constraint PK_t_product primary key (idx)
go
-- identity 테이블 입력 가능하게
set identity_insert t_product on
go
-- identity 있는 상태로 데이터를 생성해 넣는다.
with temp as
(
select
top 2000000 cast(row_number() over (order by (select 1)) as int) idx
, cast('contents other column' as char(400)) contentOtherCol
from sys.objects a1
cross join sys.objects a2
cross join sys.objects a3
cross join sys.objects a4
cross join sys.objects a5
)
insert into t_product (idx, shopid, productid, contentOtherCol)
select
idx
, cast(abs(checksum(newid())) % 2 as int) shopid
, cast(abs(checksum(newid())) % 100000 as int) productid
, contentOtherCol
--into t_product
from temp
go
-- identity 기능을 활성화 한다.
set identity_insert t_product off
go
----------------------------------------------
-- 일반적인 방법으로 실행
-- 점검시간 X*2 = 9초
----------------------------------------------
-- 0초
alter table t_product drop constraint PK_t_product
go
-- 4초 전체 리빌드
ALTER TABLE t_product ALTER COLUMN idx bigint not null
go
-- 5초 전체 리빌드
alter table t_product add constraint PK_t_product primary key (idx)
go
----------------------------------------------
-- 테이블 압축과 online 옵션 이용
-- 점검시간 X*1 = 3초
----------------------------------------------
-- 테스트로 압축 해본다. (압축 시간은 얼마나 걸리나? online = 7초, offline = 1초, 테이블 사이즈와 비즈니스 영향도에 따라 선택)
-- 압축은 언제든지 stop 하면 취소된다. online, offline 구분없음
-- *** 운영시간에 테이블을 압축 해둔다.
ALTER TABLE DBO.t_product REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);
go
--ALTER TABLE DBO.t_product REBUILD PARTITION = ALL
--WITH (DATA_COMPRESSION = PAGE);
--go
--sp_help t_product
-- pk 가 있으면, int 에서 bigint 로 바로 변경이 불가능하기에 pk 를 삭제한다.
-- ALTER TABLE t_product ALTER COLUMN idx bigint not null -- 에러
-- 메시지 5074, 수준 16, 상태 1, 줄 57
-- The object 'PK_t_product' is dependent on column 'idx'.
-- 메시지 4922, 수준 16, 상태 9, 줄 57
-- ALTER TABLE ALTER COLUMN idx failed because one or more objects access this column.
-- pk 삭제 0초
alter table t_product drop constraint PK_t_product
go
--insert into t_product (
--shopid
--, productid
--, contentOtherCol
--)
--values (
--1
--, 1
--, 1
--)
--select IDENT_CURRENT( 't_product' )
-- 3~4배 느려짐
-- ALTER TABLE t_product ALTER COLUMN idx bigint not null with (online = on)
-- int 에서 bigint 로 변경한다. (압축테이블 : 메타 변경 0초 (online offline 0초) VS. 비 압축테이블 : 테이블 전체 리빌드 2초, 온라인 : 7초)
-- * 반드시 압축해서 사용한다 0초 아니면 시간이 오래걸림
ALTER TABLE t_product ALTER COLUMN idx bigint not null
go
-- pk 를 다시 만들어 준다. (offline : 3초, online : 8초, 언제든지 취소 가능하다.)
-- pk 를 이용하는 쿼리가 있는지 확인하고 없다면 online 옵션 사용
-- pk 를 사용하는 쿼리가 있으면 점검 시간에 offline 으로 사용
-- *** 점검 시간의 대부분 사용함
alter table t_product add constraint PK_t_product primary key (idx)
alter table t_product add constraint PK_t_product primary key (idx) with (online = on)
--exec sp_help t_product
--exec sp_helpindex t_product
--select IDENT_CURRENT( 't_product' )
-- 언제든지 online 으로 풀 수 있음, query stop 언제든지 가능
ALTER TABLE DBO.t_product REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE, ONLINE = ON);
go
----------------------------------------------
-- pk 가 없는 일반테이블의 경우, 테이블 압축을 이용하면 int -> bigint 변경은 0초
-- 운영 정지시간 X*0 = 0초
----------------------------------------------
if object_id('t_product') is not null
drop table t_product
go
-- 재현을 위해서 int identity 컬럼을 만든다.
create table t_product
( idx int identity(1,1)
, shopid int
, productid int
, contentOtherCol char(400)
)
go
-- identity 테이블 입력 가능하게
set identity_insert t_product on
go
-- identity 있는 상태로 데이터를 생성해 넣는다.
with temp as
(
select
top 2000000 cast(row_number() over (order by (select 1)) as int) idx
, cast('contents other column' as char(400)) contentOtherCol
from sys.objects a1
cross join sys.objects a2
cross join sys.objects a3
cross join sys.objects a4
cross join sys.objects a5
)
insert into t_product (idx, shopid, productid, contentOtherCol)
select
idx
, cast(abs(checksum(newid())) % 2 as int) shopid
, cast(abs(checksum(newid())) % 100000 as int) productid
, contentOtherCol
--into t_product
from temp
go
-- identity 기능을 활성화 한다.
set identity_insert t_product off
go
-- 테스트로 압축 해본다. (압축 시간은 얼마나 걸리나? online = 7초, offline = 1초, 테이블 사이즈와 비즈니스 영향도에 따라 선택)
-- 압축은 언제든지 stop 하면 취소된다. online, offline 구분없음
-- *** 운영시간에 테이블을 압축 해둔다.
ALTER TABLE DBO.t_product REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);
go
--ALTER TABLE DBO.t_product REBUILD PARTITION = ALL
--WITH (DATA_COMPRESSION = PAGE);
--go
-- 3~4배 느려짐
-- ALTER TABLE t_product ALTER COLUMN idx bigint not null with (online = on)
-- int 에서 bigint 로 변경한다. (압축테이블 : 메타 변경 0초 (online offline 0초) VS. 비 압축테이블 : 테이블 전체 리빌드 2초, 온라인 : 7초)
-- * 반드시 압축해서 사용한다 0초 아니면 시간이 오래걸림
ALTER TABLE t_product ALTER COLUMN idx bigint not null
go
--exec sp_help t_product
--exec sp_helpindex t_product
--select IDENT_CURRENT( 't_product' )
-- 언제든지 online 으로 풀 수 있음, query stop 언제든지 가능
ALTER TABLE DBO.t_product REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE, ONLINE = ON);
go
