压缩表空间的优点:节省时间,缺点:消耗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