(12R1)
create tablespace tbs01 datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf' size 10M autoextend on maxsize unlimited;
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int) tablespace tbs01;
create table tab2(col1 int) tablespace tbs01;
declare
begin
for i in 1..10000 loop
insert into tab1 values(i);
commit;
end loop;
end;
/
declare
begin
for i in 1..10000 loop
insert into tab2 values(i);
commit;
end loop;
end;
/
select count(*) from tab1;
select count(*) from tab2;
select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES
from dba_extents where tablespace_name = 'TBS01'
order by BLOCK_ID;
drop table tab1 purge;
--テーブル断片化の確認
select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES
from dba_extents where tablespace_name = 'TBS01'
order by BLOCK_ID;
--テーブル断片化の解消
alter table tab2 move;
select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES
from dba_extents where tablespace_name = 'TBS01'
order by BLOCK_ID;
(5.6)
https://yohei-a.hatenablog.jp/entry/20180610/1528650004
drop table tab1;
create table tab1(col1 int);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 100000 do
insert into tab1 values(i);
set i = i + 1;
end while;
end
//
delimiter ;
call proc1();
select count(*) from tab1;
SELECT table_name, engine, table_rows,avg_row_length, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema='test'
AND table_name = 'tab1'
;
delete from tab1 where col1 < 100000;
select count(*) from tab1;
--テーブル断片化の確認
SELECT table_name, engine, table_rows,avg_row_length, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema='test'
AND table_name = 'tab1'
;
--テーブル断片化の解消
alter table tab1 engine innodb;
SELECT table_name, engine, table_rows,avg_row_length, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema='test'
AND table_name = 'tab1'
;
(9.4)
確認のため、autovacuumはオフとする
drop table tab1;
create table tab1(col1 int primary key,col2 int);
insert into tab1 select generate_series(1,100000),generate_series(1,100000);
analyze tab1;
select relname,relpages,reltuples,reltuples/relpages
from pg_class
where relname = 'tab1'
;
DO $$
DECLARE
BEGIN
FOR i IN 1..10000 LOOP
EXECUTE 'update tab1 set col2 = col2+1 where col1 = floor(random() * 100000)+1';
END LOOP;
END
$$
;
--テーブル断片化の確認
analyze tab1;
select relname,relpages,reltuples,reltuples/relpages
from pg_class
where relname = 'tab1'
;
--テーブル断片化の解消
vacuum full または pg_repack
--(1)
vacuum full tab1;
※vacuum tab1では断片化は解消しない
--(2)
/usr/pgsql-9.4/bin/pg_repack -d test -t tab1
※テーブルには主キーが必要
analyze tab1;
select relname,relpages,reltuples,reltuples/relpages
from pg_class
where relname = 'tab1'
;
(2014)
https://garfie.weblogs.jp/life_of_dev/2013/05/sqlserver%E3%81%AF%E6%96%AD%E7%89%87%E5%8C%96%E7%8E%87%E3%81%8C%E9%AB%98%E3%81%8F%E3%81%AA%E3%82%8B%E3%81%A8sql%E3%81%8C%E5%AE%9F%E8%A1%8C%E3%81%95%E3%82%8C%E3%82%8B%E9%9A%9B%E3%83%91%E3%83%A9%E3%83%AC%E3%83%AB%E5%87%A6%E7%90%86%E3%81%95%E3%82%8C%E3%81%AA%E3%81%8F%E3%81%AA%E3%82%8A%E6%A5%B5%E7%AB%AF%E3%81%AB%E5%87%A6%E7%90%86%E6%80%A7%E8%83%BD.html
https://blogs.msdn.microsoft.com/jpsql/2011/10/16/337/
drop table tab1;
create table tab1(col1 int,col2 int);
DECLARE @counter int;
SET @counter = 1;
WHILE (@counter <= 10000)
BEGIN
INSERT INTO tab1 VALUES (@counter,@counter);
SET @counter = @counter + 1;
END
go
select count(*) from tab1;
DBCC SHOWCONTIG (tab1)
DECLARE @counter int;
SET @counter = 1;
WHILE (@counter <= 1000)
BEGIN
delete from tab1 where col2 = floor(rand() * 10000)+1;
SET @counter = @counter + 1;
END
go
--テーブル断片化の確認
DBCC SHOWCONTIG (tab1)
--テーブル断片化の解消
ALTER TABLE dbo.tab1 REBUILD PARTITION = ALL;
DBCC SHOWCONTIG (tab1)
