oracle压缩表空间

压缩表空间的优点：节省时间，缺点：消耗CPU。一般用于存放历史数据，或者不经常update delete的表。

1.创建压缩表空间：

SYS@orcl> create tablespace compress_01 datafile '/u01/app/oracle/oradata/orcl/compress_01.dbf' size 10m default compress for oltp;

Tablespace created.

--查询表空间信息

SYS@orcl> selecttablespace_name,compress_for from dba_tablespaces;

TABLESPACE_NAME                COMPRESS_FOR

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

TEST

COMPRESS_01                    OLTP

2.创建一个普通表空间，修改普通表空间为压缩表空间

SYS@orcl> create tablespace tbs1datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' size 10m;

Tablespace created.

 

SYS@orcl> alter tablespace tbs1 default compress for oltp;

Tablespace altered.

SYS@orcl> alter tablespace tbs1 default nocompress; 

Tablespace altered.

3.在压缩表空间中创建表

a.在压缩表空间里创建oltp压缩表

SYS@orcl> create table t compress foroltp tablespace compress_01 as select * from all_objects;

Table created.

b.在压缩表空间里创建非压缩表

SYS@orcl> create table t1 tablespacecompress_01 as select * from all_objects;

Table created.

对压缩表和非压缩表进行分析：

SYS@orcl> execdbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

 

SYS@orcl> execdbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SYS@orcl> select table_name,compress_forfrom user_tables where table_name in('T','T1');

TABLE_NAME                     COMPRESS_FOR

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

T                              OLTP

T1                             OLTP

结论：在oltp压缩表空间里创建压缩表，如果不指定默认的压缩方式，默认是按照表空间的压缩方式

oltp来进行压缩。

c.在压缩表空间里创建指定压缩方式的表

SYS@orcl> create table t2 compresstablespace compress_01 as select * from v$tablespace;

Table created.

 

SYS@orcl> select table_name,compress_forfrom user_tables where table_name='T2';

TABLE_NAME                     COMPRESS_FOR

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

T2                             BASIC

结论：在oltp压缩表空间内创建表的时候，指定压缩方式为basic compress，那么创建的表是按照basic compression压缩，

说明表级别的压缩方式会覆盖表空间级别的压缩方式。

d.索引的压缩

SYS@orcl> create index idx_1 ont(object_id) tablespace compress_01; 

Index created.

SYS@orcl> select index_name,compression from user_indexes whereindex_name='IDX_1';

INDEX_NAME                     COMPRESS

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

IDX_1                          DISABLED

 

SYS@orcl> create index idx_2 ont1(object_id) compress tablespace compress_01;

Index created.

SYS@orcl> select index_name,compressionfrom user_indexes where index_name='IDX_2';

INDEX_NAME                     COMPRESS

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

IDX_2                          ENABLED

结论：在压缩表空间里创建索引，索引仍然是diabled,没有压缩，如果要压缩索引，必须手动致命compress