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

SQL> set autotrace on
SQL> select count(*) from test;

  COUNT(*)
----------
     10000

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=10000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from test_compress;
  COUNT(*)
----------
     10000

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST_COMPRESS' (Cost=3 Card=10000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> update test_compress set c1='oracle' where rownum <10;

9 rows updated.

SQL> commit;

Commit complete.

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                          24            0