性能调优:Oracle9iR2 NF:压缩表技术

[oracle@jumper oracle]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 26 16:07:24 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> create table test (c1 varchar2(20),c2 varchar2(20));

Table created.

SQL> begin
  2  for i in 1 .. 10000 loop
  3  insert into test values('eygle','test');
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> create table test_compress compress as select * from test;

Table created.

SQL> select table_name,COMPRESSION from user_tables where table_name like 'TEST%';

TABLE_NAME                     COMPRESS
------------------------------ --------
TEST                           DISABLED
TEST_COMPRESS                  ENABLED

SQL> analyze table test compute statistics;

Table analyzed.

SQL> analyze table test_compress compute statistics;

Table analyzed.

SQL> select table_name,blocks,EMPTY_BLOCKS from user_tables
  2  where table_name like 'TEST%';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST                                   28            4
TEST_COMPRESS                          18            6

SQL> select (28-4)/(18-6) from dual;

(28-4)/(18-6)
-------------
            2

SQL> select segment_name,file_id,block_id,blocks from dba_extents
  2  where segment_name='TEST_COMPRESS';

SEGMENT_NAME            FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ----------
TEST_COMPRESS                 3         17          8
TEST_COMPRESS                 3         25          8
TEST_COMPRESS                 3         33          8

SQL> alter system dump datafile 3 block 20;

System altered.

SQL> @gettrcname.sql

TRACE_FILE_NAME
-------------------------------------------------------------------
/opt/oracle/admin/eygle/udump/eygle_ora_20984.trc