壓縮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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- unusable index對DML/QUERY的影響Index
- INDEX建立方式對SQL的影響IndexSQL
- JPEG的量化引數QP如何影響壓縮質量
- ASP.NET Core中的響應壓縮ASP.NET
- alter table move與shrink space
- laravel 怎麼壓縮響應的 html 內容LaravelHTML
- .Net Core HttpClient處理響應壓縮HTTPclient
- Nginx網路壓縮 CSS壓縮 圖片壓縮 JSON壓縮NginxCSSJSON
- Nologging對恢復的影響(二)
- 語言對思維的影響
- Nologging對恢復的影響(一)
- 網線的分類與對網速的影響 網線對網速影響大嗎？
- 浮動的盒子對img的影響
- Linux中檔案的壓縮和解壓縮Linux
- linux 高效壓縮工具之xz的壓縮解壓使用Linux
- 檔案壓縮和解壓縮
- ppt怎麼壓縮，ppt壓縮的技巧分享
- 來電對播放音樂的影響
- python：super()對多繼承的影響Python繼承
- DB2 HADR對效能的影響DB2
- 關於OPcache對Swoole影響的理解opcache
- Python實現壓縮和解壓縮Python
- linux下壓縮解壓縮命令Linux
- linux壓縮和解壓縮命令整理Linux
- JS壓縮方法及批量壓縮JS
- 0317TABLE ACCESS BY INDEX ROWID BATCHEDIndexBAT
- Linux下的tar壓縮解壓縮命令詳解Linux
- Linux 常用的壓縮與解壓縮命令詳解Linux
- Linux下對於檔案或者目錄的打包及壓縮、解壓Linux
- kotlin對ZipInputStream與ZipOutputStream的一些擴充工具(壓縮,解壓)Kotlin
- 終端環境對go程式的影響？Go
- margin為負值對佈局的影響
- Sailthru：Facebook醜聞對人們的影響AI
- 網路延遲對事務的影響
- JVM 引數調整對 sortx 的影響JVM
- Mavrck：COVID-19對創作者的影響VR
- cluster factor對執行計劃的影響
- 淺談疫情對消費金融的影響
- 虛擬記憶體對 OI 的影響記憶體