壓縮table and index 對 space 的影響test
一. 建立兩個不同(壓縮與非壓縮)表，空間的比較
SQL> create table t_compress compress as select * from dba_objects;
Table created.
SQL> create table t_uncompress as select * from dba_objects;
SQL> select segment_name,bytes from user_extents where segment_name in ('T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 65536
T_UNCOMPRESS 1048576
T_UNCOMPRESS 1048576
T_UNCOMPRESS 1048576
T_UNCOMPRESS 1048576
T_UNCOMPRESS 1048576
T_UNCOMPRESS 1048576
22 rows selected.
SQL> select segment_name ,bytes from user_extents where segment_name in ('T_COMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 65536
T_COMPRESS 1048576
T_COMPRESS 1048576
18 rows selected.
檢視錶的大小：
SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'t_compress',cascade=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t_uncompress',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
T_UNCOMPRESS 7
T_COMPRESS 3
INDEX 的壓縮：
SQL> create table t10 as select * from dba_objects;
Table created.
SQL> insert into t10 select * from t10;
60689 rows created.
SQL> r
1* insert into t10 select * from t10
121378 rows created.
SQL> r
1* insert into t10 select * from t10
242756 rows created.
SQL> commit;
Commit complete.
SQL> create table t20 as select * from t10;
Table created.
SQL> commit;
SQL> create index ind_t10_compress on t10(object_name) compress;
Index created.
SQL> create index ind_t20_uncompress on t20(object_name);
Index created.
SQL> commit;
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in ('IND_T10_COMPRESS','IND_T20_UNCOMPRESS');
SEGMENT_NAME BYTES_MB
------------------------------ ----------
IND_T10_COMPRESS 8
IND_T20_UNCOMPRESS 19
##################################################################################################
二、 當列上的鍵值重複率高時，索引的壓縮就會較大，
SQL> create table t21 as select object_id,object_name from dba_objects;
Table created.
SQL> CREATE table t22 as select 1 object_id,object_name from dba_objects;
SQL> create index ind_t21 on t21(object_id) compress;
Index created.
SQL> create index ind_t22 on t22(object_id) compress;
Index created.
SQL> select count(distinct object_id) from t21;
COUNT(DISTINCTOBJECT_ID)
------------------------
60685
SQL> select count(distinct object_id) from t22;
COUNT(DISTINCTOBJECT_ID)
------------------------
1
SQL> select count(1) from t21;
COUNT(1)
----------
485544
SQL> select count(1) from t22;
COUNT(1)
----------
485552
SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t21',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t22',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in ('IND_T21','IND_T22');
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
IND_T21 2
IND_T22 .8125
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_extents where segment_name in ('IND_T21');
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 .0625
IND_T21 1
17 rows selected.
SQL>
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_extents where segment_name in ('IND_T22');
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
IND_T22 .0625
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
IND_T22 .0625
IND_T22 .0625
13 rows selected.
SQL>
######################################################################
正常的INSERT 的資料並不會被壓縮：
SQL> create table t31 compress as select * from dba_objects;
Table created.
SQL> create table t32 compress as select * from dba_objects where 1=2;
Table created.
SQL> insert into t32 select * from dba_objects;
60626 rows created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'t31',cascade=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t32',cascade=>true);
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in ('T31','T32');
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
T31 3
T32 6
UPDATE 對空間的影響：（解壓宿）
SQL> update t31 set object_name=object_name||'decompress';
60625 rows updated.
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in ('T31','T32');
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
T31 8
T32 6
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'t31',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in ('T31','T32');
SEGMENT_NAME BYTES_MB
--------------------------------------------------------------------------------- ----------
T31 8
T32 6 3
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27036311/viewspace-740986/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 驗證資料壓縮對DML的影響
- unusable index對DML/QUERY的影響Index
- INDEX建立方式對SQL的影響IndexSQL
- reverse index 對於 MAX/MIN操作的影響Index
- JPEG的量化引數QP如何影響壓縮質量
- 影響flashback table的操作！
- table_open_cache引數對mysql效能的影響MySql
- optimizer_index_cost_adj和optimizer_index_caching對CBO的影響Index
- Oracle Index Key Compression索引壓縮OracleIndex索引
- SQL查詢table或index異常增長問題 - space_usage (show_space)SQLIndex
- Oracle 10g Shrink Table - Shrink Space 收縮空間Oracle 10g
- partition table test
- 引數Optimizer_index_cost_adj 對執行計劃的影響Index
- ASP.NET Core中的響應壓縮ASP.NET
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- 字串的壓縮和解壓縮字串
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- laravel 怎麼壓縮響應的 html 內容LaravelHTML
- .Net Core HttpClient處理響應壓縮HTTPclient
- Oracle ASM Free Space TableOracleASM
- Oracle 表壓縮(Table Compression)技術介紹Oracle
- alter table move 和 alter table shrink space的區別
- index table (IOT)Index
- Arraysize 對consistent get的影響
- mysql event對主從的影響MySql
- 新增欄位對SQL的影響SQL
- 語言對思維的影響
- Nginx網路壓縮 CSS壓縮 圖片壓縮 JSON壓縮NginxCSSJSON
- 簡單的zip壓縮和解壓縮
- JAVA壓縮和解壓縮Java
- zip壓縮和解壓縮
- “影響者研究”系列（一）：社會網路化時代影響者對營銷創新的影響
- db2 partition table testDB2
- 絕對定位對margin外邊距的影響
- shrink 與rebuild對索引高度的影響對比Rebuild索引
- Java和.NET的GZIP壓縮功能對比Java
- aix 檔案的壓縮與解壓縮AI