--ORACLE 表压缩
/*
从Oracle9iR2 开始，ORACLE提供了表/表空间压缩技术，以减少磁盘开销，节省空间，并在某些情况下提高查询性能。
表压缩是如何工作的在Orcle9i第2版中，表压缩特性通过删除在数据库表中发现的重复数据值来节省空间。
压缩是在数据库的数据块级别上进行的。
当确定一个表要被压缩后，数据库便在每一个数据库数据块中保留空间，以便储存在该数据块中的多个位置上出现的数据的单一拷贝。
这一被保留的空间被称作符号表（symbol table）。
被标识为要进行压缩的数据只存储在该符号表中，而不是在数据库行本身内。
当在一个数据库行中出现被标识为要压缩的数据时，该行在该符号表中存储一个指向相关数据的指针，而不是数据本身。
节约空间是通过删除表中数据值的冗余拷贝而实现的。
*/
--ORACLE 表压缩
Oracle至少有六种压缩的方法:
1.BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse
2.OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well
3.QUERY LOW compression (Exadata only such as asm), recommended for Data Warehouse with Load Time as a critical factor
4.QUERY HIGH compression (Exadata only such as asm), recommended for Data Warehouse with focus on Space Saving
5.ARCHIVE LOW compression (Exadata only such as asm), recommended for Archival Data with Load Time as a critical factor
6.ARCHIVE HIGH compression (Exadata only such as asm), recommended for Archival Data with maximum Space Saving
--PS:QUERY LOW ，QUERY HIGH ，ARCHIVE LOW，ARCHIVE HIGH 只适合exadata数据库
--创建正常表
create table t_test as select * from dba_objects,(select level from dual connect by level <= 100) where 1 =2;
--压缩表
create table t_test1 compress as select * from dba_objects,(select level from dual connect by level <= 100) where 1=2;
--create table t_test2 compress for oltp as select * from dba_objects,(select level from dual connect by level <= 100);
--create table t_test3 compress for QUERY LOW as select * from dba_objects,(select level from dual connect by level <= 100);
--create table t_test4 compress for QUERY HIGH as select * from dba_objects,(select level from dual connect by level <= 100);
--create table t_test5 compress for ARCHIVE LOW as select * from dba_objects,(select level from dual connect by level <= 100);
--create table t_test6 compress for ARCHIVE HIGH as select * from dba_objects,(select level from dual connect by level <= 100);
--修改现有表为压缩表：
alter table t_test1 compress;
--取消表的压缩：
alter table t_test1 nocompress;
--查询表是否压缩
select table_name,compression from user_tables where table_name in ('T_TEST','T_TEST1');
--插入数据
/*
当表指定compress时,其它表中(表空间)的数据并没有压缩,它只是修改了数据字典的设置;
只有在向一个表中加裁/插入数据时,才会压缩数据.
只有在使用下面4种方法时,表中的数据才会被压缩存放:
1.直接路径的 sql*load
2.带有\*+ append*\的 insert语句
3.create table .. as select..
4.并行insert
*/
--正常插入
insert into t_test t select * from dba_objects,(select level from dual connect by level <= 100);
insert into t_test1 t select * from dba_objects,(select level from dual connect by level <= 100);
insert /*+append_value(t)*/ into t_test t select * from dba_objects,(select level from dual connect by level <= 100);
--查询占用表空间
select t.segment_name,t.BYTES/1024/1024 from user_segments t where t.segment_name in('T_TEST','T_TEST1');
--基本没压缩
--T_TEST 856
--T_TEST1 768
--清除表
truncate table t_test;
truncate table t_test1;
--使用append方式插入
insert /*+append*/ into t_test t select * from dba_objects,(select level from dual connect by level <= 100);
insert /*+append*/ into t_test1 t select * from dba_objects,(select level from dual connect by level <= 100);
--再次查询占用表空间
select t.segment_name,t.BYTES/1024/1024 from user_segments t where t.segment_name in('T_TEST','T_TEST1');
--T_TEST 856
--T_TEST1 240
--压缩的表不能删除字段
alter table t_test drop column status; --successful
alter table t_test1 drop column status; --ORA-39726: unsupported add/drop column operation on compressed tables
--处理方法 需要进行解压缩后才能删除字段
Alter Table t_test1 Move Nocompress;
alter table t_test1 drop column status;
alter table t_test1 compress;
alter table t_test1 move compress;
--查询效率比较
--查询1 查询正常的表
SQL> Select t.object_id, t.object_name
2 From t_test t
3 Where t.object_id = 20
4 Order By t.object_id
5 /
已选择100行。
执行计划
----------------------------------------------------------
Plan hash value: 2796558804
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1204 | 95116 | 29765 (1)| 00:05:58 |
|* 1 | TABLE ACCESS FULL| T_TEST | 1204 | 95116 | 29765 (1)| 00:05:58 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID"=20)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
217864 consistent gets
108950 physical reads
0 redo size
1791 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
--查询2 查询压缩的表
SQL> Select t.object_id, t.object_name
2 From t_test1 t
3 Where t.object_id = 20
4 Order By t.object_id;
已选择100行。
执行计划
----------------------------------------------------------
Plan hash value: 1883417357
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 484 | 38236 | 8403 (2)| 00:01:41 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 484 | 38236 | 8403 (2)| 00:01:41 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID"=20)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
206 recursive calls
0 db block gets
30428 consistent gets
30681 physical reads
116 redo size
1791 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
100 rows processed
![oracle中的start with connect by用法[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)