oltp compress

验证：Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression.

创建oltp compress 表

       ----- append 方式插入

SQL> create table t compress  for oltp tablespace test  as  select * From emp where 0=1;

表已创建。

           ----普通方式插入数据

      create table t1 compress   for oltp  tablespace test  as  select * From emp where 0=1;

            ----用于插入append 插入

        create table t2  tablespace test as select * From emp where 0=1;     

          -----普通方式插入数据          

         create table t3  tablespace test as select * From emp where 0=1;

插入数据：

SQL> select count(*)from test;

  COUNT(*)

----------

    196608

insert  /*+ append */into  t select * From test;

 insert  into  t1 select * From test;

insert  /*+ append */into  t2 select * From test;

insert  into  t3 select * From test;

对四个表进行分析：

 analyze table t compute statistics;

 analyze table t1 compute statistics;

 analyze table t2 compute statistics;

 analyze table t3 compute statistics;

SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T','T1','T2','T3');

TABLE_NAME BLOCKS COMPRESS_FOR

------------------------------ ---------- ------------

T2   1193

T    335    OLTP

T3   1252

T1  496    OLTP

结论：oltp compress 压缩方式下，即使是普通的插入方式，压缩效果也比较好

更新：

更新：

SQL> update t set sal=sal+1;

SQL> update t1 set sal=sal+1;

SQL> update t2 set sal=sal+1;

SQL> update t3 set sal=sal+1;

commit;

对四个表进行分析：

 analyze table t compute statistics;

 analyze table t1 compute statistics;

 analyze table t2 compute statistics;

 analyze table t3 compute statistics;

SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T','T1','T2','T3');

TABLE_NAME BLOCKS COMPRESS_FOR

------------------------------ ---------- ------------

T2      1193

T        762 OLTP

T3      1252

T1       874 OLTP

结论：oltp压缩方式，在更新后，仍然使用更多的空间，但是相对于不压缩效果还是比较好的

SQL> alter table t2 move compress for oltp;

SQL> analyze table t2 compute statistics;

表已分析。

SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T','T1','T2','T3');

TABLE_NAME BLOCKS COMPRESS_FOR

------------------------------ ---------- ------------

T 762 OLTP

T3 1252

T2 336 OLTP

T1 874 OLTP

t2 表变成压缩表

SQL> alter table t3 move compress ;

表已更改。

SQL> analyze table t3 compute statistics;

表已分析。

SQL> select table_name,blocks,compress_for From dba_tables where table_name in ('T','T1','T2','T3');

TABLE_NAME BLOCKS COMPRESS_FOR

------------------------------ ---------- ------------

T 762 OLTP

T2 336

T1 874 OLTP

T3 302 BASIC