oracle 分区表的维护

oracle 分区表的维护(2009-10-28 16:04:01)转载标签： oraclepartitiontable分区表it 分类： oracle
Oracle的分区技术在某些条件下可以极大的提高查询的性能，所以被广泛采用。
从产品上说，分区技术是Oracle企业版中独立收费的一个组件。

以下是对于分区及本地索引的一个示例。

首先根据字典表创建一个测试分区表：

SQL> connect eygle/eygle
Connected.
SQL> CREATE TABLE dbobjs
2 (OBJECT_ID NUMBER NOT NULL,
3 OBJECT_NAME varchar2(128),
4 CREATED DATE NOT NULL
5 )
6 PARTITION BY RANGE (CREATED)
7 (PARTITION dbobjs_06 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
8 PARTITION dbobjs_07 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')));

Table created.

SQL> COL segment_name for a20
SQL> COL PARTITION_NAME for a20
SQL> SELECT segment_name, partition_name, tablespace_name
2 FROM dba_segments
3 WHERE segment_name = 'DBOBJS';

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------
DBOBJS DBOBJS_06 EYGLE
DBOBJS DBOBJS_07 EYGLE


创建一个Local索引，注意这里可以将不同分区的索引指定创建到不同的表空间：

SQL> CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL
2 (PARTITION dbobjs_06 TABLESPACE users,
3 PARTITION dbobjs_07 TABLESPACE users
4 );

Index created.

这个子句可以进一步调整为类似：

CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL
(PARTITION dbobjs_06 TABLESPACE users,
PARTITION dbobjs_07 TABLESPACE users
) TABLESPACE users;

通过统一的tablespace子句为索引指定表空间。


SQL> COL segment_name for a20
SQL> COL PARTITION_NAME for a20
SQL> SELECT segment_name, partition_name, tablespace_name
2 FROM dba_segments
3 WHERE segment_name = 'DBOBJS_IDX';

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------
DBOBJS_IDX DBOBJS_06 USERS
DBOBJS_IDX DBOBJS_07 USERS


SQL> insert into dbobjs
2 select object_id,object_name,created
3 from dba_objects where created <to_date('01/01/2008','dd/mm/yyyy') and object_id is not null;

6227 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from dbobjs partition (DBOBJS_06);

COUNT(*)
----------
6154

SQL> select count(*) from dbobjs partition (dbobjs_07);

COUNT(*)
----------
73


我们可以通过查询来对比一下分区表和非分区表的查询性能差异：

SQL> set autotrace on
SQL> select count(*) from dbobjs where created < to_date('01/01/2008','dd/mm/yyyy');

COUNT(*)
----------
6227


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=

第1页下一页