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

最新推荐文章于 2024-06-13 21:12:59 发布

cmaw43885

最新推荐文章于 2024-06-13 21:12:59 发布

阅读量117

点赞数

文章标签：数据库

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

本篇主要是在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

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7876 ADAMS CLERK 7788 23-MAY-87 1100

7934 MILLER CLERK 7782 23-JAN-82 1300

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

EMP SYS_P2

EMP SYS_P3

TABLE_NAME PARTITION_NAME

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

HIGH_VALUE PARTITION_POSITION

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

EMP SYS_P4

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_TABLES，DBA_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/，如需转载，请注明出处，否则将追究法律责任。

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

cmaw43885

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
性能调优之分区篇(原创）

为了使大型数据库的管理更方便，可以使用分区。分区讲表中的行动态的分到小一些的表中，尽管数据在物理上被分开但逻辑上仍以一个整体出现，可以改进维护操作，备份，恢复，事务处理和查询的性能。本篇主要是在windows 2000环境下得o...
复制链接

扫一扫