(8.0.27)
調べた限り、圧縮サイズ見積機能はない模様
(19c)
https://blogs.oracle.com/otnjp/post/shibacho-014
-- 1. 非圧縮テーブル作成
drop table tab1 purge;
create table tab1 (col1 int, col2 date, col3 varchar2(100) );
-- 1.1 ランダムデータの場合
insert into tab1
select floor(dbms_random.value(1, 1000001) )
,to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 365*20) )
,substr(standard_hash(dbms_random.value(), 'MD5'),1,30)
from (select 1 from all_catalog where rownum <= 100),
(select 1 from all_catalog where rownum <= 100),
(select 1 from all_catalog where rownum <= 100);
commit;
-- 1.2 規則性のあるデータの場合
insert into tab1
select row_number() over(order by 1)
,to_date('20010101','yyyymmdd') + row_number() over(order by 1)
,rpad(to_char(mod(row_number() over(order by 1), 10)), 100, 'X')
from (select 1 from all_catalog where rownum <= 100),
(select 1 from all_catalog where rownum <= 100),
(select 1 from all_catalog where rownum <= 100);
commit;
select * from tab1 fetch first 10 rows only;
-- 2. 圧縮見積値取得
set serveroutput on
declare
SCRATCHTBSNAME VARCHAR2(30) :='USERS';
OWNNAME VARCHAR2(30) :='TEST';
TABNAME VARCHAR2(30) :='TAB1';
PARTNAME VARCHAR2(30) :='';
COMPTYPE_FLG NUMBER :=2;
SAMPLE_BLKCNT_CMP BINARY_INTEGER;
SAMPLE_BLKCNT_UNCMP BINARY_INTEGER;
SAMPLE_ROWNUM_PER_BLK_CMP BINARY_INTEGER;
SAMPLE_ROWNUM_PER_BLK_UNCMP BINARY_INTEGER;
CMP_RATIO NUMBER;
COMPTYPE_STR VARCHAR2(100);
begin
DBMS_COMPRESSION.GET_COMPRESSION_RATIO
(SCRATCHTBSNAME,OWNNAME,TABNAME,PARTNAME,COMPTYPE_FLG,
SAMPLE_BLKCNT_CMP,SAMPLE_BLKCNT_UNCMP,SAMPLE_ROWNUM_PER_BLK_CMP,
SAMPLE_ROWNUM_PER_BLK_UNCMP,CMP_RATIO,COMPTYPE_STR);
dbms_output.put_line('--------------------------------------------------------');
dbms_output.put_line('OBJECT_NAME => '|| OWNNAME ||'.'|| TABNAME || ' (PARTITION='|| PARTNAME ||')');
dbms_output.put_line('COMPRESS_RATIO => '|| CMP_RATIO);
dbms_output.put_line('--------------------------------------------------------');
dbms_output.put_line('COMPRESSED_TYPE = '||COMPTYPE_STR);
dbms_output.put_line('SAMPLE_UNCOMPRESSED_BLOCKS = '||SAMPLE_BLKCNT_UNCMP);
dbms_output.put_line('SAMPLE_COMPRESSED_BLOCKS = '||SAMPLE_BLKCNT_CMP);
dbms_output.put_line('SAMPLE_UNCOMPRESSED_ROWS_PER_BLK = '||SAMPLE_ROWNUM_PER_BLK_UNCMP);
dbms_output.put_line('SAMPLE_COMPRESSED_ROWS_PER_BLK = '||SAMPLE_ROWNUM_PER_BLK_CMP);
end;
/
-- 3. 見積値検証
drop table tab2 purge;
create table tab2 (col1 int, col2 date, col3 varchar2(100) ) ROW STORE COMPRESS ADVANCED;
insert into tab2
select * from tab1;
commit;
select segment_name, sum(bytes)/1024/1024 as mb
from user_segments
where segment_name in ('TAB1','TAB2')
group by segment_name
order by segment_name;
(14)
一般的なデータ圧縮機能がなし
(2019)
-- 1. 非圧縮テーブル作成
drop table tab1;
create table tab1 (col1 int, col2 date, col3 varchar(100) );
-- 1.1 ランダムデータの場合
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab1
select floor(rand() * 1000000)+1
,dateadd(day,floor(365 * 20 * rand()),'2001-01-01')
,substring(master.dbo.fn_varbintohexstr(hashbytes('md5',cast(rand() as varchar))),3,30);
set @i = @i + 1;
end
commit;
-- 1.2 規則性のあるデータの場合
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab1
select @i
,dateadd(day,@i,'2001-01-01')
,cast( (@i % 10) as varchar)+ replicate('X',99)
set @i = @i + 1;
end
commit;
select top 10 * from tab1;
-- 2. 圧縮見積値取得
exec sp_estimate_data_compression_savings @schema_name='dbo',@object_name='tab1',@index_id=NULL,@partition_number=NULL,@data_compression='ROW';
exec sp_estimate_data_compression_savings @schema_name='dbo',@object_name='tab1',@index_id=NULL,@partition_number=NULL,@data_compression='PAGE';
-- 3. 見積値検証
drop table tab2;
create table tab2 (col1 int, col2 date, col3 varchar(100) ) with (data_compression = PAGE);
insert into tab2 select * from tab1;
exec sp_spaceused 'dbo.tab1';
exec sp_spaceused 'dbo.tab2';
