int -> bigint 변환

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