Dextrys DBA's space

分区表小研究

上一篇 / 下一篇 2008-03-17 08:41:51

查看( 119 ) / 评论( 2 )

今天BIAN同志咨询了我一个关于分区表效率问题，说有一张非常大的表，想提高一下查询的效率，单表提高查询效率，
很自然的就想到了采用分区表的方法。我的感觉是做一个基于范围的分区，Bian建议做一个HASH分区。为了比较两者的
效率，做了如下的简单测试:
首先准备三张表aa,hash,range其中aa是原始表，结构如下：
CREATE TABLE AA
(
  ...
  BILLING_DATE_TIME          DATE,
  ...
)
hash是基于billing_date_time的HASH分区表，结构如下
CREATE TABLE HASH
(
  ...
  BILLING_DATE_TIME          DATE,
  ...
)
PARTITION BY HASH (BILLING_DATE_TIME)
(
  PARTITION P1 TABLESPACE USERS,
  PARTITION P2 TABLESPACE USERS,
  PARTITION P3 TABLESPACE USERS,
  PARTITION P4 TABLESPACE USERS
)
range是基于billing_date_time的范围分区表，结构如下
CREATE TABLE RANGE
(
  ...
  BILLING_DATE_TIME          DATE,
  ...
)
PARTITION BY RANGE (BILLING_DATE_TIME)
(
  PARTITION P1 VALUES LESS THAN (TO_DATE(' 2006-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION P2 VALUES LESS THAN (TO_DATE(' 2006-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION P3 VALUES LESS THAN (TO_DATE(' 2007-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION P4 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))

首先做全表扫描查询,
SQL> select * from aa;
已选择277537行。

执行计划
----------------------------------------------------------
Plan hash value: 3285055242

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 288K| 85M|  1324 (2)| 00:00:16 |
| 1 |  TABLE ACCESS FULL| AA | 288K| 85M|  1324 (2)| 00:00:16 |
--------------------------------------------------------------------------

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

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   24029  consistent gets
   3430  physical reads
      0  redo size
44310658  bytes sent via SQL*Net to client
   203907  bytes received via SQL*Net from client
   18504  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   277537  rows processed
SQL> select * from hash;
已选择277537行。

执行计划
----------------------------------------------------------
Plan hash value: 2064578943

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

| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

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

| 0 | SELECT STATEMENT |    | 284K| 84M|  1336 (2)| 00:00:17 |    |    |

| 1 |  PARTITION HASH ALL|    | 284K| 84M|  1336 (2)| 00:00:17 |    1 |    4 |

| 2 | TABLE ACCESS FULL| HASH | 284K| 84M|  1336 (2)| 00:00:17 |    1 |    4 |

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


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

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   24220  consistent gets
      0  physical reads
      0  redo size
23313874  bytes sent via SQL*Net to client
   203907  bytes received via SQL*Net from client
   18504  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   277537  rows processed
SQL> select * from range;
已选择277537行。

执行计划
----------------------------------------------------------
Plan hash value: 184025858

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

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

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

| 0 | SELECT STATEMENT |    | 254K| 75M|  1320 (2)| 00:00:16 |    |    |

| 1 |  PARTITION RANGE ALL|    | 254K| 75M|  1320 (2)| 00:00:16 |    1 |    4 |

| 2 | TABLE ACCESS FULL | RANGE | 254K| 75M|  1320 (2)| 00:00:16 |    1 |    4 |

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


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

统计信息
----------------------------------------------------------
      6  recursive calls
      1  db block gets
   24220  consistent gets
   3973  physical reads
      176  redo size
22835132  bytes sent via SQL*Net to client
   203907  bytes received via SQL*Net from client
   18504  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   277537  rows processed
可以看到三张表的查询方式是不一样的,其中aa是采用的全表扫描，hash是先做HASH分区处理，然后再处理完的结果中做全表扫描，range是先做range分区处理，然后再处理完的结果中做全表扫描,
因为是全表处理，所以三者效率相差不大，range的cost1320，aa是1324，hash是1336（HASH要计算所以成本会略高一点）

下面测试在一个range范围内的情况（billing_date_time<'2006-01-01'）
SQL> select * from aa where billing_date_time<to_date('2006-01-01','yyyy=mm=dd');
已选择25295行。

执行计划
----------------------------------------------------------
Plan hash value: 3285055242

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 23162 |  7034K|  1318 (1)| 00:00:16 |
|*  1 |  TABLE ACCESS FULL| AA | 23162 |  7034K|  1318 (1)| 00:00:16 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("BILLING_DATE_TIME"<TO_DATE('2006-01-01 00:00:00',
            'yyyy-mm-dd hh24:mi:ss'))

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

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   7587  consistent gets
   5761  physical reads
      0  redo size
2010034  bytes sent via SQL*Net to client
   18931  bytes received via SQL*Net from client
   1688  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   25295  rows processed
SQL> select * from hash where billing_date_time<to_date('2006-01-01','yyyy=mm=dd');
已选择25295行。

执行计划
----------------------------------------------------------
Plan hash value: 2064578943

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

| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

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

| 0 | SELECT STATEMENT |    | 48329 | 14M|  1331 (2)| 00:00:16 |    |    |

| 1 |  PARTITION HASH ALL|    | 48329 | 14M|  1331 (2)| 00:00:16 | 1 |    4 |

|*  2 | TABLE ACCESS FULL| HASH | 48329 | 14M|  1331 (2)| 00:00:16 | 1 |    4 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BILLING_DATE_TIME"<TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

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

统计信息
----------------------------------------------------------
      9  recursive calls
      4  db block gets
   7842  consistent gets
   6072  physical reads
      572  redo size
2064478  bytes sent via SQL*Net to client
   18931  bytes received via SQL*Net from client
   1688  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   25295  rows processed
SQL> select * from range where billing_date_time<to_date('2006-01-01','yyyy=mm=dd');
已选择25295行。

执行计划
----------------------------------------------------------
Plan hash value: 955737907

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

| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

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

| 0 | SELECT STATEMENT    |    | 23695 |  7196K| 195 (2)| 00:00:03 |    |    |

| 1 |  PARTITION RANGE SINGLE|    | 23695 |  7196K| 195 (2)| 00:00:03 |    1 |    1 |

|*  2 | TABLE ACCESS FULL | RANGE | 23695 |  7196K| 195 (2)| 00:00:03 |    1 |    1 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BILLING_DATE_TIME"<TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

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

统计信息
----------------------------------------------------------
      6  recursive calls
      1  db block gets
   2611  consistent gets
      886  physical reads
      176  redo size
2029623  bytes sent via SQL*Net to client
   18931  bytes received via SQL*Net from client
   1688  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   25295  rows processed

  可以明显的看到aa和hash表消耗的成本基本不变，但是range消耗的成本从1320下降到195,下降效果明显.

论坛模式推荐收藏分享给好友推荐到圈子管理

TAG:

kelsoncong 发布于2008-03-17 08:42:00: 在测试一下,跨越range分区的情况（billing_date_time<'2007-01-01'）

  SQL> select * from aa where billing_date_time<to_date('2007-01-01','yyyy=mm=dd');
已选择157051行。

执行计划
----------------------------------------------------------
Plan hash value: 3285055242

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 157K| 46M|  1322 (2)| 00:00:16 |
|*  1 |  TABLE ACCESS FULL| AA | 157K| 46M|  1322 (2)| 00:00:16 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("BILLING_DATE_TIME"<TO_DATE('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))

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

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   16184  consistent gets
   3904  physical reads
      0  redo size
12802601  bytes sent via SQL*Net to client
   115555  bytes received via SQL*Net from client
   10472  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   157051  rows processed
SQL> select * from hash where billing_date_time<to_date('2007-01-01','yyyy=mm=dd');
已选择157051行。

执行计划
----------------------------------------------------------
Plan hash value: 2064578943

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

| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

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

| 0 | SELECT STATEMENT |    | 183K| 54M|  1335 (2)| 00:00:17 |    |    |

| 1 |  PARTITION HASH ALL|    | 183K| 54M|  1335 (2)| 00:00:17 |    1 |    4 |

|*  2 | TABLE ACCESS FULL| HASH | 183K| 54M|  1335 (2)| 00:00:17 |    1 |    4 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BILLING_DATE_TIME"<TO_DATE('2007-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

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

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   16342  consistent gets
   2174  physical reads
      0  redo size
13212459  bytes sent via SQL*Net to client
   115555  bytes received via SQL*Net from client
   10472  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   157051  rows processed
SQL> select * from range where billing_date_time<to_date('2007-01-01','yyyy=mm=dd');
已选择157051行。

执行计划
----------------------------------------------------------
Plan hash value: 2521943314

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

| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

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

| 0 | SELECT STATEMENT       |    | 147K| 43M| 850 (2)| 00:00:11 |    |    |

| 1 |  PARTITION RANGE ITERATOR|    | 147K| 43M| 850 (2)| 00:00:11 |    1 |    3 |

|*  2 | TABLE ACCESS FULL    | RANGE | 147K| 43M| 850 (2)| 00:00:11 |    1 |    3 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BILLING_DATE_TIME"<TO_DATE('2007-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))


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

统计信息
----------------------------------------------------------
   1330  recursive calls
      2  db block gets
   14357  consistent gets
   3018  physical reads
      264  redo size
12941526  bytes sent via SQL*Net to client
   115555  bytes received via SQL*Net from client
   10472  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   157051  rows processed

  可以明显的看到aa和hash表消耗的成本基本不变，range消耗的成本变成了850

下面看看定值查询的情况
  SQL> select * from aa where billing_date_time=to_date('2007-01-01','yyyy=mm=dd');
未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3285055242

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 66 | 20526 |  1318 (1)| 00:00:16 |
|*  1 |  TABLE ACCESS FULL| AA | 66 | 20526 |  1318 (1)| 00:00:16 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("BILLING_DATE_TIME"=TO_DATE('2007-01-01 00:00:00',
            'yyyy-mm-dd hh24:mi:ss'))

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

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   5938  consistent gets
   5848  physical reads
      0  redo size
   2163  bytes sent via SQL*Net to client
      374  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed
SQL> select * from hash where billing_date_time=to_date('2007-01-01','yyyy=mm=dd');
未选定行

执行计划
----------------------------------------------------------
Plan hash value: 1198858656

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

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

| 0 | SELECT STATEMENT    |    | 17 |  5287 | 333 (1)| 00:00:04 |    |    |

| 1 |  PARTITION HASH SINGLE|    | 17 |  5287 | 333 (1)| 00:00:04 |    1 |    1 |

|*  2 | TABLE ACCESS FULL | HASH | 17 |  5287 | 333 (1)| 00:00:04 |    1 |    1 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BILLING_DATE_TIME"=TO_DATE('2007-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

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

统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   1573  consistent gets
   1491  physical reads
      0  redo size
   2163  bytes sent via SQL*Net to client
      374  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed
SQL> select * from range where billing_date_time=to_date('2007-01-01','yyyy=mm=dd');
未选定行

执行计划
----------------------------------------------------------
Plan hash value: 955737907

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

| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

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

| 0 | SELECT STATEMENT    |    | 14 |  4354 | 278 (1)| 00:00:04 |    |    |

| 1 |  PARTITION RANGE SINGLE|    | 14 |  4354 | 278 (1)| 00:00:04 |    3 |    3 |

|*  2 | TABLE ACCESS FULL | RANGE | 14 |  4354 | 278 (1)| 00:00:04 |    3 |    3 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BILLING_DATE_TIME"=TO_DATE('2007-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note