ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 压缩表dml

压缩表dml

原创 Linux操作系统 作者:psufnxk2000 时间:2013-08-30 14:54:29 0 删除 编辑


对于压缩表(索引)执行 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博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-769668/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    616230