BASIC压缩和ADVANCED压缩

botang · 发表于 2020-10-27 21:59:10

create tablespace tbs_nocompression datafile size 10M autoextend on;
create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
create tablespace tbs_query datafile size 10M autoextend on default compress for query;
create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
--sys-error--
select t.tablespace_name, t.def_tab_compression, t.compress_for from dba_tablespaces t
where t.tablespace_name in ('TBS_NOCOMPRESSION',
'TBS_BASIC', 'TBS_OLTP',
'TBS_QUERY',
'TBS_ARCHIVE');

复制代码

--hr--
create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
create table t_basic (a varchar2(200)) tablespace tbs_basic;
create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
--hr-error--
create table t_query (a varchar2(200)) tablespace tbs_query;
create table t_archive (a varchar2(200)) tablespace tbs_archive;

复制代码

--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;

复制代码

--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');

复制代码

--SYS--
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
from hr.T_OLTP group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);

复制代码

-------------------------------------------------------------------------------------------------------------

<span class="y"><a href="https://www.botangdb.com/member.php?mod=logging&action=login&referer=https%3A%2F%2Fwww.botangdb.com%2Fforum.php%3Fmod%3Dviewthread%26tid%3D1474%26extra%3Dpage%253D1" class="xi2">现</a></span>--hr--
create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression row store compress advanced;

复制代码

--hr-error--
create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;

复制代码

--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COM<span class="y"><a href="https://www.botangdb.com/member.php?mod=logging&action=login&referer=https%3A%2F%2Fwww.botangdb.com%2Fforum.php%3Fmod%3Dviewthread%26tid%3D1474%26extra%3Dpage%253D1" class="xi2">现</a></span>PRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );

复制代码

T_OLTP2 10 ENABLED ADVANCED TBS_NOCOMPRESSION
T_OLTP 10 ENABLED ADVANCED TBS_OLTP
T_NOCOMPRESSION 10 DISABLED TBS_NOCOMPRESSION
T_BASIC2 0 ENABLED BASIC TBS_NOCOMPRESSION
T_BASIC 0 ENABLED BASIC TBS_BASIC

--hr--
begin
for i in 1..400
loop
insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic2 group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp2 group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
from hr.T_BASIC2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid)
from hr.T_OLTP2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid);
--hr--
alter table t_nocompression compress for oltp;
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
alter table t_nocompression move tablespace tbs_nocompression;
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
insert /*+ append */ into t_basic select * from t_basic;
commit;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--hr--
alter table t_basic move tablespace TBS_NOCOMPRESSION;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys-evolution--
create table hr.tbig compress as select * from dba_source;
--sys-advisor--
declare
v_blkcnt_cmp number;
v_blkcnt_uncmp number;
v_row_cmp number;
v_row_uncmp number;
v_cmp_ratio number;
v_comptype_str varchar2(200);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO( 'USERS',
'HR',
'TBIG',
null,
2,
v_blkcnt_cmp,
v_blkcnt_uncmp,
v_row_cmp,
v_row_uncmp,
v_cmp_ratio,
v_comptype_str);
DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
end;
--输出--
Blk count compressed = 1785
Blk count uncompressed = 2340
Row count per block compressed = 68
Row count per block uncompressed = 51
ratio: 1.31092436974789915966386554621848739496
Compression type = "Compress For OLTP"
--OLTP压缩一下表--
验证表大小
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
from hr.T_BASIC_BIG group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);
--hr--
create table t_basic_col( a number , b varchar2(20)) compress ;
insert into t_basic_col values ( 1,'A') ;
commit;
create table t_oltp_col( a number , b varchar2(20)) compress for oltp;
insert into t_oltp_col values ( 1,'A') ;
commit;
select * from t_basic_col;
select * from t_oltp_col;
alter table t_basic_col drop column b;
alter table t_basic_col drop ( b);
alter table t_oltp_col drop column b;
select * from t_oltp_col;

复制代码