エクスポートインポートによるデータファイルサイズ変化確認

(8.0.22)


確認結果:
データファイルサイズは変化なし

-- データ準備
drop tablespace tbs1;
create tablespace tbs1 add datafile 'tbs1.ibd' engine=innodb;

drop table tab1;
drop table tab2;
drop table tab3;

create table tab1(col1 int primary key, col2 varchar(10)) tablespace tbs1;
create index ind11 on tab1(col2);
create table tab2(col1 int primary key, col2 varchar(10)) tablespace tbs1;
create index ind21 on tab2(col2);
create table tab3(col1 int primary key, col2 varchar(10)) tablespace tbs1;
create index ind31 on tab3(col2);

 

drop procedure proc1;

delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
while i < x do
set i = i + 1;
insert into tab1 values(i,rpad('x',10,'x') );
insert into tab2 values(i,rpad('x',10,'x') );
insert into tab3 values(i,rpad('x',10,'x') );
end while;
end
//
delimiter ;

start transaction;
call proc1(1000000);
commit;

-- exp/imp

mysqldump -uroot -p test tab2 > tab2.dmp

drop table tab2;

source tab2.dmp


-- 使用サイズ確認

select space,name, allocated_size/1024/1024 mbytes
from information_schema.innodb_tablespaces
where name like 'tbs1'
;

analyze table tab1;
analyze table tab2;
analyze table tab3;

select
table_name, engine, table_rows as tbl_rows,
floor( (data_length+index_length)/1024/1024) as allmb, #総容量
floor( (data_length)/1024/1024) as dmb, #データ容量
floor( (index_length)/1024/1024) as imb #インデックス容量
from information_schema.tables
where table_schema = database()
and table_name in ('tab1','tab2','tab3')
order by table_name
;


select database_name,table_name,clustered_index_size,sum_of_other_index_sizes
from mysql.innodb_table_stats
where database_name = database()
and table_name in ('tab1','tab2','tab3')
order by table_name
;

 

(19c)

確認方法:
3テーブルのデータをばらけるように積む。そのうち1テーブルをexp→drop→impする。
exp/imp前後でデータファイルサイズを確認し、dropで生成された隙間が再利用されるか確認。


確認結果:
dropで生成された隙間は基本的には再利用される。
ただし、ブロック数がすこし変動し、データファイルサイズが少し大きくなった。


-- データ準備
drop tablespace tbs1 including contents and datafiles;
create tablespace tbs1 datafile '/u01/app/oracle/oradata/ORCL/pdb1/tbs1_01.dbf' size 10M autoextend on maxsize unlimited;


drop table tab1 purge;
drop table tab2 purge;
drop table tab3 purge;

create table tab1(col1 int primary key, col2 varchar2(10)) tablespace tbs1;
create index ind11 on tab1(col2) tablespace tbs1;
create table tab2(col1 int primary key, col2 varchar2(10)) tablespace tbs1;
create index ind21 on tab2(col2) tablespace tbs1;
create table tab3(col1 int primary key, col2 varchar2(10)) tablespace tbs1;
create index ind31 on tab3(col2) tablespace tbs1;

 

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,rpad('x',10,'x') );
insert into tab2 values(i,rpad('x',10,'x') );
insert into tab3 values(i,rpad('x',10,'x') );
commit;
end loop;
end;
/

-- exp/imp

expdp test/test@pdb1 directory=ORA_DIR dumpfile=TAB2.dmp logfile=TAB2.exp.log tables=TAB2

drop table tab2 purge;

impdp test/test@pdb1 directory=ORA_DIR dumpfile=TAB2.dmp logfile=TAB2.imp.log tables=TAB2


-- 使用サイズ確認
select segment_name,tablespace_name,segment_type,bytes/1024/1024 mbytes,blocks,extents
from user_segments
where tablespace_name = 'TBS1'
order by segment_name
;


select
d.tablespace_name,
d.mbytes "total[MB]",
NVL(f.mbytes,0) "free[MB]",
d.mbytes - NVL(f.mbytes,0) "used[MB]",
(1 - (NVL(f.mbytes,0)/d.mbytes))*100 "used_percent"
from
(SELECT tablespace_name, (SUM(bytes)/(1024*1024)) mbytes
FROM dba_data_files GROUP BY tablespace_name) d
left outer join
(SELECT tablespace_name, (SUM(bytes)/(1024*1024)) mbytes
FROM dba_free_space GROUP BY tablespace_name) f
on d.tablespace_name=f.tablespace_name
;

 

(13)


確認結果:
テーブル単位にファイルが分かれているため、単にデータファイルが再作成されるのみ


-- データ準備
drop tablespace tbs1;
create tablespace tbs1 owner postgres location '/var/lib/pgsql/13';

drop table tab1;
drop table tab2;
drop table tab3;

create table tab1(col1 int, col2 varchar(10)) tablespace tbs1;
create unique index tab1pk on tab1(col1) tablespace tbs1;
alter table tab1 add constraint tab1pk primary key using index tab1pk;
create index ind11 on tab1(col2) tablespace tbs1;

create table tab2(col1 int, col2 varchar(10)) tablespace tbs1;
create unique index tab2pk on tab2(col1) tablespace tbs1;
alter table tab2 add constraint tab2pk primary key using index tab2pk;
create index ind21 on tab2(col2) tablespace tbs1;

create table tab3(col1 int, col2 varchar(10)) tablespace tbs1;
create unique index tab3pk on tab3(col1) tablespace tbs1;
alter table tab3 add constraint tab3pk primary key using index tab3pk;
create index ind31 on tab3(col2) tablespace tbs1;


do $$
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,rpad('x',10,'x'));
insert into tab2 values(i,rpad('x',10,'x'));
insert into tab3 values(i,rpad('x',10,'x'));
end loop;
end
$$
;

-- exp/imp

pg_dump -Fc -t tab2 test > tab2.dmp

drop table tab2;

pg_restore -Fc -d test tab2.dmp


-- 使用サイズ確認

\db+
\dt+ tab1
\dt+ tab2
\dt+ tab3

analyze tab1;
analyze tab2;
analyze tab3;

select oid,relname,relpages,reltuples
from pg_class
where reltablespace = (select oid from pg_tablespace where spcname = 'tbs1')
order by relname
;

select pg_size_pretty(pg_total_relation_size('tab1'));
select pg_size_pretty(pg_total_relation_size('tab2'));
select pg_size_pretty(pg_total_relation_size('tab3'));

 

(2019)


確認結果:
データファイルサイズは変化なし


-- データ準備
use master

alter database test remove file tbs1_01;
alter database test remove filegroup tbs1;

alter database test add filegroup tbs1;
alter database test add file
(name = tbs1_01 ,filename = 'C:\test\tbs1_01.ndf'
) to filegroup tbs1
;


use test
drop table tab1;
drop table tab2;
drop table tab3;


create table tab1(
col1 int not null primary key,
col2 varchar(10)
) on tbs1
;
create index ind11 on tab1(col2);
create table tab2(
col1 int not null primary key,
col2 varchar(10)
) on tbs1
;
create index ind21 on tab2(col2);
create table tab3(
col1 int not null primary key,
col2 varchar(10)
) on tbs1
;
create index ind31 on tab3(col2);

 

set nocount on
declare @i integer;
set @i = 1;
while @i <= 1000000
begin
insert into tab1 values(@i,replicate('x',10));
insert into tab2 values(@i,replicate('x',10));
insert into tab3 values(@i,replicate('x',10));
set @i = @i + 1;
end

-- exp/imp

SSMSのタスク→スクリプトの生成でスキーマとデータのスクリプトをエクスポート
※主キー以外のインデックスはエクスポートされない模様

drop table tab2;

エクスポートしたSQLを実行

 

-- 使用サイズ確認

select database_id,file_id,physical_name,size * 8/1024 mbyte
from sys.master_files
where database_id = DB_ID()
;

exec sp_spaceused 'dbo.tab1';
exec sp_spaceused 'dbo.tab2';
exec sp_spaceused 'dbo.tab3';

select * from sys.dm_db_partition_stats
where object_id in (object_id('dbo.tab1'),object_id('dbo.tab2'),object_id('dbo.tab3'))
order by object_name(object_id)
;