压缩表dml
对于压缩表（索引）执行 dml操作，会产生解压操作， 也因为解压的操作，所以花费的时候会比一样的非压缩用的时间要长
create table t_compress compress as select * from dba_objects;
create table t_uncompress as select * from dba_objects;
exec dbms_stats.gather_table_stats('SONG','T_COMPRESS');
exec dbms_stats.gather_table_stats('SONG','T_UNCOMPRESS');
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES
----------
T_UNCOMPRESS
9437184
T_COMPRESS
3145728
SQL> col segment_name format a30
SQL> /
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 3145728
SQL> update t_compress set wner='song' where wner='SYS';
31705 rows updated.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 6291456
SQL> update t_compress set object_type='TTBLE' where wner='TABLE';
0 rows updated.
SQL> update t_compress set object_type='TTBLE' where object_type='TABLE';
2898 rows updated.
SQL> COMMIT;
Commit complete.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 6291456
SQL> update t_compress set CREATED=SYSDATE;
74763 rows updated.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 10485760
SQL> update t_uncompress set CREATED=SYSDATE
2 ;
74764 rows updated.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 10485760
SQL> update t_uncompress set object_type='TTBLE' where object_type='TABLE';
2899 rows updated.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 10485760
SQL> update t_uncompress set wner='song' where wner='SYS';
31705 rows updated.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 10485760
SQL> exec dbms_stats.gather_table_stats('SONG','T_COMPRESS');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SONG','T_UNCOMPRESS');
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','T_UNCOMPRESS');
SEGMENT_NAME BYTES
------------------------------ ----------
T_UNCOMPRESS 9437184
T_COMPRESS 10485760
压缩索引的操作也会有这样的问题：
create index idx_t_test on t_test (owner,object_name) compress 1;
SQL> create index idx_t_test on t_test (owner,object_name) compress 1;
Index created.
SQL> select segment_name,bytes from dba_segments where segment_name in ('T_COMPRESS','IDX_T_TEST');
SEGMENT_NAME BYTES
------------------------------ ----------
T_COMPRESS 10485760
IDX_T_TEST 4194304
SQL> select segment_name,bytes from dba_segments where segment_name in ('IDX_T_TEST');
SEGMENT_NAME BYTES
------------------------------ ----------
IDX_T_TEST 4194304
SQL> UPDATE T_TEST SET WNER='SSS' WHERE WNER='SYS';
31705 rows updated.
SQL> COMMIT;
Commit complete.
SQL> select segment_name,bytes from dba_segments where segment_name in ('IDX_T_TEST');
SEGMENT_NAME BYTES
------------------------------ ----------
IDX_T_TEST 5242880
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25099483/viewspace-769668/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
