oralce 壓縮表與heap表儲存空間與查詢效能比較

首先來看一下test表
SQL> set autot trace exp stat
SQL> SELECT COUNT(*) FROM test;

已用時間:  00: 00: 03.42

執行計劃
----------------------------------------------------------
Plan hash value: 3187697131

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
      42900  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  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;

已用時間:  00: 00: 03.29

執行計劃
----------------------------------------------------------
Plan hash value: 3187697131

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
      42900  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

將buffer_cache記憶體中cache的資料清空
SQL> alter system flush buffer_cache;

系統已更改。

已用時間:  00: 00: 00.03
SQL> SELECT COUNT(*) FROM test;

已用時間:  00: 00: 03.39

執行計劃
----------------------------------------------------------
Plan hash value: 3187697131

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
      42901  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

將此表資料cache到記憶體後終於，查詢的效率提高了
SQL> SELECT COUNT(*) FROM test;

已用時間:  00: 00: 00.60

執行計劃
----------------------------------------------------------
Plan hash value: 3187697131

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |  9479   (4)| 00:01:54 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TIME |    15M|  9479   (4)| 00:01:54 |
--------------------------------------------------------------------------

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42926  consistent gets
          0  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

以下建立壓縮表和普通表，對比儲存和查詢效能
SQL> create table test2
  2  as
  3  select * from test;

表已建立。

已用時間:  00: 01: 31.29
SQL> create table test3 compress
  2  as
  3  select * from test;

表已建立。

已用時間:  00: 01: 25.39

SQL> SELECT COUNT(*) FROM test2;

已用時間:  00: 00: 10.46

執行計劃
----------------------------------------------------------
Plan hash value: 634289536

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 | 33818   (2)| 00:06:46 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST2 |    15M| 33818   (2)| 00:06:46 |
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
        146  recursive calls
          0  db block gets
     152084  consistent gets
     152017  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT COUNT(*) FROM test3;

已用時間:  00: 00: 07.78

執行計劃
----------------------------------------------------------
Plan hash value: 1832809687

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 | 24779   (3)| 00:04:58 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST3 |    16M| 24779   (3)| 00:04:58 |
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
         28  recursive calls
          0  db block gets
     110776  consistent gets
     110719  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SET AUTOT OFF
SQL> col owner format a15
SQL> col segment_name format a15
SQL> select owner,segment_name,bytes/1024/1024 m from dba_segments where segment_name='TEST2';

OWNER           SEGMENT_NAME             M
--------------- --------------- ----------
SYS             TEST2                 1216

已用時間:  00: 00: 00.06
SQL> select owner,segment_name,bytes/1024/1024 m from dba_segments where segment_name='TEST3';

OWNER           SEGMENT_NAME             M
--------------- --------------- ----------
SYS             TEST3                  872

已用時間:  00: 00: 00.06
SQL> show parameter db_block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
SQL>

test2表與test3表相比：
1: 儲存test2表比test3多了1216-872=344M
2: 查詢效能：test2表比test3多掃描344*1024/8=44032個資料庫，故查詢時間也多出10.46-07.78=2.68秒

但是載入壓縮表資料比載入普通表時間將近多2倍