分区表对性能的影响。

对数据库的总体查询性能来说，分区的影响有以下几种可能:

测试Table script.:

普通表:create table test_1 as select * from dba_objects;

分区表:

CREATE TABLE TEST_2
(
  OWNER           VARCHAR2(30 BYTE),
  OBJECT_NAME     VARCHAR2(128 BYTE),
  SUBOBJECT_NAME  VARCHAR2(30 BYTE),
  OBJECT_ID       NUMBER,
  DATA_OBJECT_ID  NUMBER,
  OBJECT_TYPE     VARCHAR2(19 BYTE),
  CREATED         DATE,
  LAST_DDL_TIME   DATE,
  TIMESTAMP       VARCHAR2(19 BYTE),
  STATUS          VARCHAR2(7 BYTE),
  TEMPORARY       VARCHAR2(1 BYTE),
  GENERATED       VARCHAR2(1 BYTE),
  SECONDARY       VARCHAR2(1 BYTE)
)
PARTITION BY RANGE (OBJECT_ID)
( 
  PARTITION P1 VALUES LESS THAN (10000), 
  PARTITION P2 VALUES LESS THAN (40000),
  PARTITION P3 VALUES LESS THAN (80000),
  PARTITION P4 VALUES LESS THAN (120000),
  PARTITION P5 VALUES LESS THAN (200000),
  PARTITION P6 VALUES LESS THAN (MAXVALUE)
    );

insert into test_2 select * from dba_objects;

第一种情况：

以partition key作为条件进行的查询，大家都知道，分区表的性能会更高一些。

select * from test_1 where object_id between 49000 and 50000;
select * from test_2 where object_id between 49000 and 50000;

第二种情况：

以partition key以外的条件进行的查询。

SQL> select * from test_1 where object_type='TABLE';

3101 rows selected.

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  3030 |   289K|   150   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_1     |  3030 |   289K|   150   (0)|
|   2 |   INDEX RANGE SCAN          | IDX_TEST_1 |  3030 |       |     9   (0)|
-------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        662  consistent gets
          0  physical reads
          0  redo size
     160780  bytes sent via SQL*Net to client
       1682  bytes received via SQL*Net from client
        208  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3101  rows processed

SQL> select * from test_2 where object_type='TABLE';

3102 rows selected.

Execution Plan
----------------------------------------------------------

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

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

| Id  | Operation                          | Name       | Rows  | Bytes | Cost (

%CPU)| Pstart| Pstop |

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

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

|   0 | SELECT STATEMENT                   |            |  3078 |   294K|   228
  (0)|       |       |

|   1 |  PARTITION RANGE ALL               |            |  3078 |   294K|   228
  (0)|     1 |     6 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST_2     |  3078 |   294K|   228
  (0)|     1 |     6 |

|   3 |    INDEX RANGE SCAN                | IDX_TEST_2 |  3078 |       |    18
  (0)|     1 |     6 |

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

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

Note
-----
   - 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        821  consistent gets
          0  physical reads
          0  redo size
     159136  bytes sent via SQL*Net to client
       1682  bytes received via SQL*Net from client
        208  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3102  rows processed

结论:可以看到这种情况下，使用分区表的性能略有降低。

第三种情况:

在分区表上建立global index

SQL> select * from test_2 where object_type='TABLE';

3102 rows selected.

Elapsed: 00:00:00.36

Execution Plan
----------------------------------------------------------

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

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

| Id  | Operation                          | Name         | Rows  | Bytes | Cost

 (%CPU)| Pstart| Pstop |

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

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

|   0 | SELECT STATEMENT                   |              |  3078 |   294K|   15

3   (0)|       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST_2       |  3078 |   294K|   15

3   (0)| ROWID | ROWID |

|   2 |   INDEX RANGE SCAN                 | IDX_TEST_2_3 |  3078 |       |    1

1   (0)|       |       |

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

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

Note
-----
   - 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        659  consistent gets
          0  physical reads
          0  redo size
     161506  bytes sent via SQL*Net to client
       1682  bytes received via SQL*Net from client
        208  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3102  rows processed

结论:与非分区表执行效率差不多。但是这种global index在进行partition table的DDL操作时会变为unusable状态。

因此在一个高并发的OLTP系统中，partition table使用时要充分考虑到SQL是否适用，否则可能导致性能变得更糟。

分区表的优点:(摘自ORACLE 9i&10g 编程艺术)

（1） 提高数据的可用性：这个特点对任何类型的系统都适用，而不论系统本质上是OLTP
还是仓库系统。
（2） 由于从数据库中去除了大段，相应地减轻了管理的负担。在一个100GB 的表上执
行管理操作时（如重组来删除移植的行，或者在“净化”旧信息后回收表左边的“空白”
空间），与在各个10GB 的表分区上执行10 次同样的操作相比，前者负担要大得多。另外，
通过使用分区，可以让净化例程根本不留下空白空间，这就完全消除了重组的必要！
（3） 改善某些查询的性能：主要在大型仓库环境中有这个好处，通过使用分区，可以
消除很大的数据区间，从而不必考虑它们，相应地根本不用访问这些数据。但这在事务
性系统中并不适用，因为这种系统本身就只是访问少量的数据。
（4） 可以把修改分布到多个单独的分区上，从而减少大容量OLTP 系统上的竞争：如
果一个段遭遇激烈的竞争，可以把它分为多个段，这就可以得到一个副作用：能成比例
地减少竞争。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-721587/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-721587/