--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=)