第71/72/73/74次：2016-01-23/24双休日

botang · 发表于 2016-1-27 10:33:14

上完新特性1-5章

Oracle11gR2的表压缩特性通过压缩表中的数据以减少对空间的占用。Oracle11gR2支持三种不同的压缩方式：
· 支持直接路径加载的Basic压缩方式（10x）
· 支持针对所有DML操作的OLTP压缩方式（2-4x）
· Exadata专属的Hybrid columnar compression压缩方式
Oracle公司建议我们采用以上三种不同的压缩方式，针对特定的应用场合来进行表压缩。如果该表包含大量冗长的和重复的值，通过被压缩可以减少该表对磁盘空间的占用和对SGA中数据库缓冲区缓存的占用。
先从图形界面入手，我们发现在新建表空间时Oracle11gR2的界面出现压缩选项：
在下面各个部分所有的实验中我们都创建两个表：一个表在TBS_NOCOMPRESSION表空间，另一个表在有对应默认压缩选项的表空间。最后我们把在 TBS_NOCOMPRESSION表空间上的那个表转换成对应压缩选项表。通过对比实验来弄清Oracle11gR2这方面的新特性。
--sys--
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;
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');
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp 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);
--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);
--hr--
create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
--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.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' );
--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--
drop table hr.t_basic_big;
create table hr.t_basic_big 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(scratchtbsname => 'USERS',
ownname =>'HR',
tabname =>'T_BASIC_BIG',
partname =>null,
comptype => 2,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp =>v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str =>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;
--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;

复制代码