性能调优之分区全攻略（三）

增加分区：

SQL>ALTER TABLE test ADD PARTITION emp_p5 values less than (300);

[@more@]

SQL> select partition_name, high_value, tablespace_name 

  from dba_tab_partitions

  where table_name = 'TEST';

PARTITION_NAME           HIGH_VALUE              TABLESPACE_NAME

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

EMP_P1                   50                      TBS1

EMP_P2                   100                     USERS

EMP_P3                   150                     USERS

EMP_P4                   200                     USERS

EMP_P5                   300                     USERS

5 rows selected

 

 

分裂分区：

通过SPLIT PATITION子句把已经的分区分裂，老分区将被删除，相关的局部和全局索引也不可用了。

SQL> ALTER TABLE test 

  SPLIT PARTITION EMP_P5 AT ( 250 ) 

  INTO (partition EMP_P5, 

        partition EMP_P6);

 通过查询得到以下的值：

PARTITION_NAME           HIGH_VALUE              TABLESPACE_NAME

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

EMP_P2                   100                     USERS

EMP_P3                   150                     USERS

EMP_P4                   200                     USERS

EMP_P6                   300                     USERS

EMP_P5                   250                     USERS

Emp_p5分区被分成两个新分区了：emp_p5和emp_p6，值分别是200－249，250－300。

SPLIT命令不能被用于hash类型的分区，只能用add命令。

 

SQL> ALTER TABLE emp_hpart ADD PARTITION P5;

查询得到：

SEGMENT_NAME    PARTITION_NAME  TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

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

EMP_HPART     SYS_P69      DATA01          8            2

EMP_HPART    SYS_P70         DATA02                    9           21

EMP_HPART    SYS_P71         DATA03                   10            2

EMP_HPART    SYS_P72         DATA04                   11            2

EMP_HPART    P5              USERS

5 rows selected

这里实际的分区已经被删掉重建了。

 

合并分区：

SQL> ALTER TABLE test MERGE PARTITIONS emp_p2, emp_p3 into emp_p2_p3;

SQL> select partition_position, partition_name, tablespace_name 

  from dba_tab_partitions

  where table_name = 'TEST';

查询得到：

 

通过PARTITION_POSITION PARTITION_NAME                 TABLESPACE_NAME

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

                 2 EMP_P4                         USERS

                 4 EMP_P6                         USERS

                 3 EMP_P5                         USERS

                 1 EMP_P2_P3                      USERS

对于hash分区的合并必须带上Coalesce子句。

SQL> ALTER TABLE emp_hpart COALESCE PARTITION;

查询一下：

SEGMENT_NAME    PARTITION_NAME  TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

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

EMP_HPART    P1            DATA01                    8           21

EMP_HPART    P2            DATA02                    9            2

EMP_HPART    P3            DATA03                   10            2

这里注意这个语句把p2和p4分区重新分区成一个分区p2。

 

Index Organized Tables (IOT) 索引组织表也能被分区

但要注意以下几点：

分区必须是 RANGE 类型.

分区键 key(s) 必须是primary key的子集.

SQL>CREATE TABLE emp_iot

   (empno NUMBER(4) PRIMARY KEY,

    ename VARCHAR2(10),

    sal NUMBER(7,2))

  ORGANIZATION INDEX INCLUDING ename OVERFLOW

  PARTITION BY RANGE(empno)

   (partition emp_p1 VALUES LESS THAN (50) TABLESPACE data01,

    partition emp_p2 VALUES LESS THAN (100) TABLESPACE data02,

    partition emp_p3 VALUES LESS THAN (150) TABLESPACE data03,

    partition emp_p4 VALUES LESS THAN (MAXVALUE) TABLESPACE data04);

Table created.

SQL>select table_name, partitioning_type "TYPE", partition_count "COUNT",

         def_tablespace_name

  from dba_part_tables

  where table_name like '%IOT%';

TABLE_NAME          TYPE           COUNT  DEF_TABLESPACE_NAME

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

SYS_IOT_OVER_12770  RANGE              4  USERS

EMP_IOT             RANGE              4

SQL>select index_name, partition_name, high_value, tablespace_name 

  from dba_ind_partitions

  where index_name like '%IOT%';

 

INDEX_NAME         PARTITION_NAME  HIGH_VALUE  TABLESPACE_NAME

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

SYS_IOT_TOP_12770  EMP_P1          50          DATA01

SYS_IOT_TOP_12770  EMP_P2          100         DATA02

SYS_IOT_TOP_12770  EMP_P3          150         DATA03

SYS_IOT_TOP_12770  EMP_P4          MAXVALUE    DATA04

 

 

使用分区要注意以下关键因素：

1．虽然范围分区可能是大多数工作的默认的选择，但还考虑散列分区，在散列分区中数据不能轻易地分解成范围。同时还要考虑组合分区。

2．在实现分区时，给每个分区建立一个表空间认为更具有可用性和管理性。这提供了接通和断开数据地能力。这个功能对于在某个时间段后需要隐藏其数据后根据需要再联机地那些站点是有用的，如果一个分区中数据不再需要，可以通过发布alter tablespace x offline命令断开。当该分区中的数据在后来又被请求时，可通过alter tablespace x online命令接通。

3。被分区的表和索引需要分析，并且oracle的初始化参数optimizer_mode（该参数定义和控制应用于sql语句的优化类型）需要设置为choose，这确保了优化程序在执行查询可以执行删除分区的工作。

4．应该至少建立一个局部有前缀分区索引。这将确保当查询使用该表的分区键执行搜索时删除分区。

5．要尽可能建立局部分区索引，而无论它们是否有前缀。

6．Multi-column partition keys are limited to 16.

7 下面的列不能作为分区的key:

 -  LEVEL 或者 ROWID这些伪列.

－嵌套表

－Varray

－对象类型

  －Ref

  －Rowid

 

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

转载于:http://blog.itpub.net/41224/viewspace-780375/