CTAS中の更新

DB

確認方法：
1000万件のテーブルtab1を作成し
tab1を更新しながら、CTAS実行

 

--テスト用テーブル作成
drop table tab1 purge;
create table tab1(col1 int,col2 int);
create index ind1 on tab1(col1);

declare
begin
for i in 1..10000000 loop
insert into tab1 values(i,i);

if mod(i,1000) = 0 then
commit;
end if;
end loop;
end;
/


--ランダム更新

declare
v_random integer;

begin
while true loop

-- 1500万以下のランダムな数字を生成
select floor(dbms_random.value(1, 15000001)) into v_random from dual;

-- 数字が1-500万ならupdate
if v_random >= 1 and v_random < 5000000 then
update tab1 set col2 = -1 * col2 where col1 = v_random;
-- 数字が500万-1000万ならdelete
elsif v_random >= 5000001 and v_random < 10000000 then
delete from tab1 where col1 = v_random;
-- 数字が1000万-1500万ならinsert
else
insert into tab1 values(v_random,v_random);
end if;

commit;

end loop;
end;
/


--ランダム更新中にCTAS実行
drop table tab2 purge;
create table tab2 as select * from tab1;


--tab2の状態確認

select count(*),min(col2),max(col2) from tab2;

select * from (select * from tab2 where col1 >= 1 order by col1) where rownum < 50;
select * from (select * from tab2 where col1 >= 5000001 order by col1) where rownum < 50;
select * from (select * from tab2 where col1 >= 10000001 order by col1) where rownum < 50;

 

→
CTAS中も更新可能

--テスト用テーブル作成
drop table tab1 ;
create table tab1(col1 int,col2 int);
create index ind1 on tab1(col1);

drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i int;

set i = 0;

while i <= 10000000 do
set i = i + 1;
insert into tab1 values(i,i);
end while;
end
//
delimiter ;


call proc1();


--ランダム更新

drop procedure proc2;

delimiter //
create procedure proc2()
begin
declare i int;
declare v_random int;

set i = 0;

while true do
set i = i + 1;

-- 1500万以下のランダムな数字を生成
select floor(rand() * 15000000)+1 into v_random;

-- 数字が1-500万ならupdate
if ( v_random >= 1 and v_random < 5000001 ) then
update tab1 set col2 = -1 * col2 where col1 = v_random;
-- 数字が500万-1000万ならdelete
elseif ( v_random >= 5000001 and v_random < 10000000 ) then
delete from tab1 where col1 = v_random;
-- 数字が1000万-1500万ならinsert
else
insert into tab1 values(v_random,v_random);
end if;


end while;
end
//
delimiter ;


call proc2();


--ランダム更新中にCTAS実行
drop table tab2;
create table tab2 as select * from tab1;

確認方法：
1000万件のテーブルtab1を作成し
tab1を更新しながら、CTAS実行

 

--テスト用テーブル作成
drop table tab1;
create table tab1(col1 int,col2 int);
create index ind1 on tab1(col1);


insert into tab1 select generate_series(1,10000000),generate_series(1,10000000);


--ランダム更新

drop function fun1();

create or replace function fun1()
returns void as
$$
DECLARE

v_random integer;

BEGIN

-- 1500万以下のランダムな数字を生成
select floor(random() * 15000000)+1 into v_random;

-- 数字が1-500万ならupdate
if v_random >= 1 and v_random < 5000000 then
update tab1 set col2 = -1 * col2 where col1 = v_random;
-- 数字が500万-1000万ならdelete
elsif v_random >= 5000001 and v_random < 10000000 then
delete from tab1 where col1 = v_random;
-- 数字が1000万-1500万ならinsert
else
insert into tab1 values(v_random,v_random);
end if;

END
$$ language 'plpgsql';


----------

while true;do

SQL="psql -c 'select fun1()' test"
eval ${SQL}

done

----------

--ランダム更新中にCTAS実行
drop table tab2;
create table tab2 as select * from tab1;

 

select count(*) from tab1 where col1 < 5000001 and col2 < 0;
select count(*) from tab1 where col1 >= 5000001 and col1 < 10000001;
select count(*) from tab1 where col1 > 10000000 ;

select count(*) from tab2 where col1 < 5000001 and col2 < 0;
select count(*) from tab2 where col1 >= 5000001 and col1 < 10000001;
select count(*) from tab2 where col1 > 10000000 ;

確認方法：
100万件のテーブルtab1を作成し
tab1を更新しながら、CTAS実行

 

--テスト用テーブル作成
drop table tab1;
create table tab1(col1 int,col2 int);
create index ind1 on tab1(col1);


DECLARE @i integer;
SET @i = 1;
WHILE @i <= 1000000
BEGIN
insert into tab1 values(@i,@i);
SET @i = @i + 1;
END

--ランダム更新

DECLARE @v_random integer;
WHILE 1=1
BEGIN
-- 150万以下のランダムな数字を生成
set @v_random = (select floor(rand() * 1500000)+1);

-- 数字が1-50万ならupdate
if ( @v_random >= 1 and @v_random < 500000 )
update tab1 set col2 = -1 * col2 where col1 = @v_random;

-- 数字が50万-100万ならdelete
if ( @v_random >= 500001 and @v_random < 1000000 )
delete from tab1 where col1 = @v_random;

-- 数字が100万-150万ならinsert
if ( @v_random >= 1000001 and @v_random < 1500000 )
insert into tab1 values(@v_random,@v_random);

END


--ランダム更新中にCTAS実行
drop table tab2 ;
select * into tab2 from tab1;


--tab2の状態確認

select count(*),min(col2),max(col2) from tab2;

select top 50 * from tab2 where col1 >= 1 order by col1;
select top 50 * from tab2 where col1 >= 500001 order by col1;
select top 50 * from tab2 where col1 >= 1000001 order by col1;