oracle 压缩表类型测试-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心
  • 博客访问: 3976741
  • 博文数量: 536
  • 博客积分: 10470
  • 博客等级: 上将
  • 技术积分: 4825
  • 用 户 组: 普通用户
  • 注册时间: 2006-05-26 14:08
文章分类

全部博文(536)

文章存档

2024年(3)

2021年(1)

2019年(1)

2017年(1)

2016年(2)

2013年(2)

2012年(10)

2011年(43)

2010年(10)

2009年(17)

2008年(121)

2007年(252)

2006年(73)

相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·

分类: oracle

2011-10-14 17:07:13

the dbms_compression package uses the constants shown in e:

table 34-1 dbms_compression constants - compression types

constant type value description

comp_nocompress

number

1

no compression

comp_for_oltp

number

2

oltp compression

comp_for_query_high

number

4

high compression level for query operations

comp_for_query_low

number

8

low compression level for query operations

comp_for_archive_high

number

16

high compression level for archive operations

comp_for_archive_low

number

32

low compression level for archive operations


note:

the constants comp_for_query_high, comp_for_query_low, comp_for_archive_high, and comp_for_archive_low are used only in the context of exadata, specifically the feature, exadata hybrid columnar compression (ehcc) which offers higher compression ratios for direct path loaded data. for more information, see the oracle exadata storage server online documentation library.

more info:

----------------------------------------------------
test examples:
sql> alter table ct2 move compress; #看来这个操作不一定产生真正的压缩表。

table altered.

sql> select rowid, a.* from ct2 a where rownum<2;

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasaqaafaaaaeraaa       1513
aaaaaa1513


sql> select dbms_compression.get_compression_type('gan', 'ct2', 'aaasaqaafaaaaeraaa') from dual;

dbms_compression.get_compression_type('gan','ct2','aaasaqaafaaaaeraaa')
-----------------------------------------------------------------------
                                                                      1


sql> create table ct2_1 compress as select * from ct2;

table created.

sql> select rowid,a.* from ct2_1 a where rownum<2;

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasaraafaaaacraaa       1513
aaaaaa1513


sql> select dbms_compression.get_compression_type('gan', 'ct2_1', 'aaasaraafaaaacraaa') from dual;

dbms_compression.get_compression_type('gan','ct2_1','aaasaraafaaaacraaa')
-------------------------------------------------------------------------
                                                                        1

sql> insert into ct2_1 select * from ct2_1;

262144 rows created.

sql> /

524288 rows created.

sql> commit;

commit complete.

sql>  select rowid,a.* from ct2_1 a where rownum<2;

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasaraafaaaacraaa       1513
aaaaaa1513


sql> select dbms_compression.get_compression_type('gan', 'ct2_1', 'aaasaraafaaaacraaa') from dual;

dbms_compression.get_compression_type('gan','ct2_1','aaasaraafaaaacraaa')
-------------------------------------------------------------------------
                                                                        1

sql> alter table ct2_1 move compress;

table altered.

sql> alter system switch logfile;

system altered.

sql> select rowid,a.* from ct2_1 a where rownum<2;

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasasaafaaaa caaa       1034
aaaaaa1034


sql> select dbms_compression.get_compression_type('gan', 'ct2_1', 'aaasasaafaaaa caaa') from dual;

dbms_compression.get_compression_type('gan','ct2_1','aaasasaafaaaa caaa')
-------------------------------------------------------------------------
                                                                        2
终于产生了一个olt compress类型的压缩表。

再来产生一个query high类型的压缩表看看:
sql> create table ct1 compress for query high as select * from tabx;

table created.

sql> select count(*) from ct1;

  count(*)
----------
      8192
sql> select rowid from ct1 where rownum<2;

rowid
------------------
aaar/qaafaaaacjaaa

sql> select rowid from tabx where rownum<2;

rowid
------------------
aaar/paafaaaacdaaa

sql> select dbms_compression.get_compression_type('gan', 'ct1', 'aaar/qaafaaaacjaaa') from dual;

dbms_compression.get_compression_type('gan','ct1','aaar/qaafaaaacjaaa')
-----------------------------------------------------------------------
                                                                      4

sql> select dbms_compression.get_compression_type('gan', 'tabx', 'aaar/paafaaaacdaaa') from dual;

dbms_compression.get_compression_type('gan','tabx','aaar/paafaaaacdaaa')
------------------------------------------------------------------------
                                                                       1


query low类型的压缩表:
sql> create table ct8 compress for query low as select * from tabx;

table created.

sql> select rowid,a.* from ct8 a where rownum<2;

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasataafaaaacraaa       1513
aaaaaa1513


sql> select dbms_compression.get_compression_type('gan', 'ct8', 'aaasataafaaaacraaa') from dual;

dbms_compression.get_compression_type('gan','ct8','aaasataafaaaacraaa')
-----------------------------------------------------------------------
                                                                      8

archive high类型压缩表:
sql> create table ct16 compress for archive high as select * from tabx;

table created.

sql>  select rowid,a.* from ct16  a where rownum<2;

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasauaafaaaac7aaa          1
aaaaaa1


sql> select dbms_compression.get_compression_type('gan', 'ct16', 'aaasauaafaaaac7aaa') from dual;

dbms_compression.get_compression_type('gan','ct16','aaasauaafaaaac7aaa')
------------------------------------------------------------------------
                                                                      16

archive low类型压缩表:
sql> create table ct32 compress for archive low as select * from tabx;

table created.

sql> select rowid,a.* from ct16  a where rownum<2;

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasauaafaaaac7aaa          1
aaaaaa1


sql> c/16/32
  1* select rowid,a.* from ct32  a where rownum<2
sql> /

rowid                       a
------------------ ----------
b
--------------------------------------------------------------------------------
aaasavaafaaaaddaaa          1
aaaaaa1


sql> select dbms_compression.get_compression_type('gan', 'ct32', 'aaasavaafaaaaddaaa') from dual;

dbms_compression.get_compression_type('gan','ct32','aaasavaafaaaaddaaa')
------------------------------------------------------------------------
                                                                      32
阅读(1554) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图