ITPub博客

首页 > 应用开发 > IT综合 > 性能调优之分区篇(原创)

性能调优之分区篇(原创)

原创 IT综合 作者:chenmolin 时间:2004-09-23 18:45:44 0 删除 编辑

为了使大型数据库的管理更方便,可以使用分区。分区讲表中的行动态的分到小一些的表中,尽管数据在物理上被分开但逻辑上仍以一个整体出现,可以改进维护操作,备份,恢复,事务处理和查询的性能。

本篇主要是在windows 2000环境下得oracle 9.2.0.1,用emp作为例表。对于已经有数据的表采用先改名的方法。然后根据已改名的表新建这个表执行分区。

[@more@]

 SQL>rename emp to emp1;

rename complete.

SQL> create table emp

  2     partition by range(sal)

  3     (partition p1 values less than (1001),

  4    partition p2 values less than (2001),

  5     partition p3 values less than (3001),

  6     partition p4 values less than (10000))

  7    as select * from emp1;

Table created.

SQL> drop table emp1;

Table dropped.

在查询的时候可以直接根据分区查询,如下:

SQL> select * from emp partition (p2);

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     

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

    DEPTNO                                                                      

----------                                                                     

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     

        30                                                                      

                                                                               

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     

        30                                                                      

                                                                               

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     

        30                                                                      

                                                                               

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     

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

    DEPTNO                                                                     

----------                                                                     

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0     

        30                                                                      

                                                                               

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                

        20                                                                      

                                                                               

      7934 MILLER     CLERK           7782 23-JAN-82       1300                

        10                                                                      

                                                                               

 

6 rows selected.

也可以创建索引分区,局部索引和全局索引,对于HASH分区不能建立全局索引,

下面是创建局部索引,下面不用指定范围,一个分区索引对应一个分区,

SQL> create index emp_sal

  2  on emp(sal)

  3  local

  4  (partition part1,

  5  partition part2,

  6  partition part3,

  7  partition part4);

 

Index created.

 

SQL> DESC EMP

 Name                                      Null?    Type

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

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

下面是建立全局索引,指定范围,一般不要用相同分区范围表的相同列上创建全局索引,局部索引更加适合,失效的情况更少。

SQL> create index emp_depnp

  2  on emp(ename)

  3  global partition by range (ename)

  4  (partition p1 values less than ('J'),

  5  partition p2 values less than ('N'),

  6  partition p3 values less than ('R'),

  7  partition p4 values less than (MAXVALUE));

 

Index created.

建立索引分区的时候要注意以下问题:

1

Indexes for clusters cannot be partitioned.

Indexes for clustered tables cannot be partitioned.

Bitmap indexes must be locally partitioned indexes.

Bitmap indexes associated to non-partitioned tables cannot be   partitioned.

- Non-prefixed global indexes are not supported.

- Local non-prefixed unique indexes must have a partitioning key  included in the index key.

2.看看你有什么需求再来选择什么索引分区:

 If your index is partitioned on a left prefix of the index columns, 

then ===>  Local prefixed

If you want to create a unique index, whose columns do not include 

your partitioning keys  ===>  Global prefixed

If you can use parallel access to the different partitions and mainly 

work in a DSS environment  ===>  Local non-prefixed

If you work in an OLTP environment ===>  Global prefixed

Local non-prefixed indexes are particularly useful for historical type 

databases in which the data is partitioned on a DATE type criteria.  

This index is useful in efficiently probing the database with particular

criteria, different from a date.  Maintenance operations tied to adding 

or dropping a partition are simpler.

 

 

下面是建立hash分区,hash分区是通过对分区键值执行一个hash函数来确定数据的物理位置。在hash分区中,不像范围分区中,分区键的连续值不必储存在同一分区中。

SQL> rename emp to emp1;

 

Table renamed.

 

SQL> desc emp1;

 Name                                                  Null?    Type

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

 EMPNO                                                          NUMBER(4)

 ENAME                                                          VARCHAR2(10)

 JOB                                                            VARCHAR2(9)

 MGR                                                            NUMBER(4)

 HIREDATE                                                       DATE

 SAL                                                            NUMBER(7,2)

 COMM                                                           NUMBER(7,2)

 DEPTNO                                                         NUMBER(2)

 

 

SQL> create table emp

  2  partition by hash (deptno)

  3  partitions 4

  4  as select * from emp1;

 

Table created.

这时 分区名已经变化了,变成sys_p1等值了,显然是系统自动定义的名称。

SQL> select table_name,partition_name,high_value,partition_position from user_tab_partitions

  2  where upper(table_name)='EMP';

 

TABLE_NAME                     PARTITION_NAME                                                       

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

HIGH_VALUE                                                                       PARTITION_POSITION

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

EMP                            SYS_P1                                                              

                                                                                                  1

                                                                                                   

EMP                            SYS_P2                                                              

                                                                                                  2

                                                                                                   

EMP                            SYS_P3                                                              

                                                                                                  3

                                                                                                   

 

TABLE_NAME                     PARTITION_NAME                                                       

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

HIGH_VALUE                                                                       PARTITION_POSITION

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

EMP                            SYS_P4                                                              

                                                                                                  4

                                                                                                

SQL> drop table emp1;

 

Table dropped.

 

SQL> rename emp to emp1;

 

Table renamed.

范围分区和hash分区可以混合使用,这对于大表来说非常有用。

SQL> create table emp

  2  partition by range (sal)

  3  subpartition by hash (ename)

  4  subpartitions 4

  5  (partition p1 values less than (1001),

  6  partition p2 values less than (2001),

  7  partition p3 values less than (3001),

  8  partition p4 values less than (10001))

  9  as select * from emp1;

 

Table created.

其中还有一种分区很特殊,叫做列表分区:基于列表划分而不是使用值范围划分行,列表分区是范围分区的一个受限版本。

 

 

 

可以使用下面的视图来收集信息

DBA_PART_KEY_COLUMNS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_IND_PARTITIONS DBA_PART_TABLESDBA_TAB_PARTITIONS DBA_PART_LOBS DBA_LOB_PARTITIONS UUSER_TAB_PARTITIONS

 可通过查询USER_TAB_PARTITIONS视图来查询分区名单。

 

SQL> desc user_tab_partitions;

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

下一篇: 给列重命名
请登录后发表评论 登录
全部评论