oracle10g改压缩表,Oracle 10g数据表压缩的一些细节（下）

下面我们通过几个极端情况实验，继续分析10gR2的Compression。

3、极端情况实验(1)

那么，上面我们验证数据compress选项切换无效，是不是和我们已经分配过的记录有关系呢？我们首先建立一张空表。

SQL> create table t_sample1 as select * from t_source where 1=0;

Table created

SQL>exec dbms_stats.gather_table_stats(user,'T_SAMPLE1',cascade => true);

PL/SQL procedure successfully completed

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SCOTT' and segment_name='T_SAMPLE1';

HEADER_FILE HEADER_BLOCKBYTESBLOCKSEXTENTS

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

441396553681

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_SAMPLE1';

NUM_ROWSBLOCKS COMPRESSION

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

00 DISABLED

我们创建空表，没有记录，但是默认有一个分区分配过来。

SQL> alter table t_sample1 compress;

Table altered

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_SAMPLE1';

NUM_ROWSBLOCKS COMPRESSION

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

00 ENABLED

SQL> insert into t_sample1 select * from t_source;

1367 rows inserted

SQL> commit;

Commit complete

SQL>exec dbms_stats.gather_table_stats(user,'T_SAMPLE1',cascade => true);

PL/SQL procedure successfully completed

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SCOTT' and segment_name='T_SAMPLE1';

HEADER_FILE HEADER_BLOCKBYTESBLOCKSEXTENTS

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

44139196608243

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_SAMPLE1';

NUM_ROWSBLOCKS COMPRESSION

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

136720 ENABLED

依然是使用三个分区，依然是没有进行压缩。

SQL> alter table t_sample1 move;

Table altered

SQL> exec dbms_stats.gather_table_stats(user,'T_SAMPLE1',cascade => true);

PL/SQL procedure successfully completed

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SCOTT' and segment_name='T_SAMPLE1';

HEADER_FILE HEADER_BLOCKBYTESBLOCKSEXTENTS

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

44187131072162

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_SAMPLE1';

NUM_ROWSBLOCKS COMPRESSION

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

136711 ENABLED

结论：即使没有一行数据插入的情况下，我们使用compress的数据表，利用OLTP方式插入，也不能实现压缩。依然需要手工的压缩过程。

4、极端情况实验(2)

对实验数据表t_sample1继续插入数据时，新分配的数据块是可能应用压缩格式的。

SQL> insert into t_sample1 select * from t_source;

1367 rows inserted

SQL> commit;

Commit complete

此时，数据行会出现部分压缩的现象。

SQL> exec dbms_stats.gather_table_stats(user,'T_SAMPLE1',cascade => true);

PL/SQL procedure successfully completed

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SCOTT' and segment_name='T_SAMPLE1';

HEADER_FILE HEADER_BLOCKBYTESBLOCKSEXTENTS

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

44187262144324

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_SAMPLE1';

NUM_ROWSBLOCKS COMPRESSION

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

273431 ENABLED

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) bno, count(*) from t_sample1 group by dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) order by bno;

FNOBNOCOUNT(*)

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

4413883

4413985

4414083

44141 81

4414284

4414379

4414479

4414883

4414983

4415088

4415188

4415227

44188193

44189184

44190184

44191177

44192188

44193199

44194199

4419543

4419684

4419782

4419886

4419982

4420090

25 rows selected

在我们持续增加数据的时候，会出现一定的压缩情况。

SQL> alter table t_sample1 move;

Table altered

SQL> exec dbms_stats.gather_table_stats(user,'T_SAMPLE1',cascade => true);

PL/SQL procedure successfully completed

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SCOTT' and segment_name='T_SAMPLE1';

HEADER_FILE HEADER_BLOCKBYTESBLOCKSEXTENTS

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

44211196608243

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_SAMPLE1';

NUM_ROWSBLOCKS COMPRESSION

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

273419 ENABLED

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) bno, count(*) from t_sample1 group by dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) order by bno;

FNOBNOCOUNT(*)

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

44212193

44213184

44214184

44215177

44216188

44225199

44226199

44227191

44228191

44229178

44230198

44231174

44232185

44490199

4449194

15 rows selected

通过move过程，实现完全压缩。

5、实现压缩的insert方式

从上面一系列实验中，我们可以看出Oracle 10g Compression对OLTP方式插入数据压缩效果较差。但是，在进行Direct Insert方法的时候，Compression的效果是可以的。

SQL> create table t_source as select * from dba_objects where wner='SYS';

Table created

SQL>exec dbms_stats.gather_table_stats(user,'T_SOURCE',cascade => true);

PL/SQL procedure successfully completed

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SCOTT' and segment_name='T_SOURCE';

HEADER_FILE HEADER_BLOCKBYTESBLOCKSEXTENTS

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

45395314572838418

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_SOURCE';

NUM_ROWSBLOCKS COMPRESSION

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

22946327 DISABLED

新原数据表没有压缩，共占用384个块，18个extent分区。下面建立一个新的数据表t_compress，采用压缩配置。

--建立空数据表；

SQL> create table t_compress as select * from dba_objects where 1=0;

Table created

SQL>exec dbms_stats.gather_table_stats(user,'T_COMPRESS',cascade => true);

PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_COMPRESS';

NUM_ROWSBLOCKS COMPRESSION

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

00 DISABLED

开启压缩选项。

SQL> alter table t_compress compress;

Table altered

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_COMPRESS';

NUM_ROWSBLOCKS COMPRESSION

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

00 ENABLED

之后采用direct insert方式插入数据。

SQL> insert /*+append */into t_compress select * from t_source;

22946 rows inserted

SQL> commit;

Commit complete

SQL>exec dbms_stats.gather_table_stats(user,'T_COMPRESS',cascade => true);

PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS, Compression from dba_tables where wner='SCOTT' and table_name='T_COMPRESS';

NUM_ROWSBLOCKS COMPRESSION

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

22946139ENABLED

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SCOTT' and segment_name='T_COMPRESS';

HEADER_FILE HEADER_BLOCKBYTESBLOCKSEXTENTS

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

46835209715225617

可见，在10R2中，压缩主要是针对那些稳定数据。如果操作是频繁的增加修改和删除的OLTP操作，压缩是不进行或者效果很差的。

6、结论

从上面的实验中，我们可以看出：Oracle 10R2中的压缩技术主要是针对稳定数据表而言的。如果数据表很大，而且不会频繁的进行增加修改和删除操作，我们推荐使用压缩功能。如果我们对一个压缩表进行OLTP方式操作，压缩效应是不明显的。只有在Direct Insert等特殊的操作中，压缩才能体现出来。

Oracle 11g带给我们的Advanced Compression新特性，其中的Compression For OLTP在一定程度上缓解了这个难题。

再次说明，我们的解决方案，要依据版本、效果和特性进行。