(8.0.22)
drop tablespace tbs1;
create tablespace tbs1 add datafile 'tbs1.ibd' engine=innodb;
drop table tab1;
create table tab1(col1 int primary key, col2 varchar(1000)) tablespace tbs1;
create index ind1 on tab1(col1);
select space,name, allocated_size/1024/1024 mbytes
from information_schema.innodb_tablespaces
where name like 'tbs1'
;
analyze table tab1;
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 = 'tab1'
;
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 = 'tab1'
;
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',1000,'x') );
end while;
end
//
delimiter ;
start transaction;
call proc1(1000000);
commit;
select count(*) from tab1 where col2 = 'AAA';
truncate table tab1;
delete from tab1;
確認結果：
truncateの場合
truncate実行に時間がかからない。
analyze実行前
使用ブロック数、select文実行時間は小さくなる。
セグメントサイズは変化なし
analyze実行後
セグメントサイズは小さくなる。
deleteの場合
delete実行に時間がかかる。
analyze実行前
使用ブロック数、セグメントサイズは変化なし
1回目のselect文実行時間はかなり大きくなるが、2回目以降は小さくなる。★
analyze実行後
使用ブロック数、セグメントサイズは小さくなる。
(19c)
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;
create table tab1(col1 int primary key, col2 varchar2(1000)) tablespace tbs1;
select segment_name,tablespace_name,segment_type,bytes/1024/1024 mbytes,blocks,extents
from user_segments where tablespace_name = 'TBS1';
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
;
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,rpad('x',1000,'x') );
commit;
end loop;
end;
/
set time on
set timing on
select count(*) from tab1 where col2 = 'AAA';
truncate table tab1;
delete from tab1;
commit;
確認結果：
truncateの場合
truncate実行に時間がかからない。
使用ブロック数、セグメントサイズ、表領域使用サイズ、select文実行時間は削除前と比べて小さくなる。
※8ブロックはtruncateしても残る。
deleteの場合
delete実行に時間がかかる。
使用ブロック数、セグメントサイズ、表領域使用サイズ、select文実行時間は削除前と比べて変化なし。
※analyzeしても変化なし
(13)
drop tablespace tbs1;
create tablespace tbs1 owner postgres location '/var/lib/pgsql/13';
drop table tab1;
create table tab1(col1 int, col2 varchar(100)) tablespace tbs1;
create unique index tab1pk on tab1(col1) tablespace tbs1;
alter table tab1 add constraint tab1pk primary key using index tab1pk;
\db+
\dt+ tab1
analyze tab1;
select oid,relname,relpages,reltuples
from pg_class
where reltablespace = (select oid from pg_tablespace where spcname = 'tbs1')
;
select pg_size_pretty(pg_relation_size('tab1'));
select pg_size_pretty(pg_relation_size('tab1pk'));
select pg_size_pretty(pg_total_relation_size('tab1'));
insert into tab1 select g,rpad('x',100,'x') from generate_series(1,1000000) g;
vi postgresql.conf
autovacuum = off
sudo systemctl restart postgresql-13
\timing 1
select count(*) from tab1 where col2 = 'AAA';
truncate table tab1;
delete from tab1;
確認結果：
truncateの場合
truncate実行に時間がかからない。
使用ブロック数、セグメントサイズ、表領域使用サイズ、select文実行時間は削除前と比べて小さくなる。(即座に反映される)
※インデックスサイズも小さくなる
deleteの場合
delete実行に時間がかかる。
analyze実行前
使用ブロック数、セグメントサイズ、表領域使用サイズは変化なし。select文実行時間は削除前と比べて少しだけ小さくなる。
analyze実行後
使用ブロック数、セグメントサイズ、表領域使用サイズは変化なし。select文実行時間は削除前と比べて少しだけ小さくなる。
vacuum実行後
使用ブロック数、セグメントサイズ、表領域使用サイズ、select文実行時間は削除前と比べて小さくなる。
※インデックスサイズは変化なし
(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;
create table tab1(
col1 int not null primary key,
col2 varchar(1000)
) on tbs1
;
create index ind1 on tab1(col1);
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';
select * from sys.dm_db_partition_stats
where object_id = object_id('dbo.tab1')
;
set nocount on
declare @i integer;
set @i = 1;
while @i <= 1000000
begin
insert into tab1 values(@i,replicate('x',1000));
set @i = @i + 1;
end
set statistics time on
select count(*) from tab1 where col2 = 'AAA';
truncate table tab1;
delete from tab1;
確認結果：
truncateの場合
truncate実行に時間がかからない。
使用ブロック数、セグメントサイズ、select文実行時間は小さくなる。
deleteの場合
delete実行に時間がかかる。
使用ブロック数、セグメントサイズ、select文実行時間は小さくなる。
