创建与管理Oracle分区表和本地索引的实例解析-51CTO.COM

创建与管理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 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

创建一个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. 
1.
2.
3.
4.
5.

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

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

通过统一的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   
　　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 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

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

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 ptimizer=CHOOSE (Cost=1 Card=1 Bytes=9)   
　　1 0 SORT (AGGREGATE)   
　　2 1 PARTITION RANGE (ALL)   
　　3 2 INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=8 Bytes=72)   
　　Statistics   
　　----------------------------------------------------------   
　　0 recursive calls   
　　0 db block gets   
　　25 consistent gets   
　　0 physical reads   
　　0 redo size   
　　380 bytes sent via SQL*Net to client   
　　503 bytes received via SQL*Net from client   
　　2 SQL*Net roundtrips to/from client   
　　0 sorts (memory)   
　　0 sorts (disk)   
　　1 rows processed   
　　SQL> select count(*) from dbobjs where created < to_date('01/01/2007','dd/mm/yyyy');   
　　COUNT(*)   
　　----------   
　　6154   
　　Execution Plan   
　　----------------------------------------------------------   
　　0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=9)   
　　1 0 SORT (AGGREGATE)   
　　2 1 INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=4 Bytes=36)   
　　Statistics   
　　----------------------------------------------------------   
　　0 recursive calls   
　　0 db block gets   
　　24 consistent gets   
　　0 physical reads   
　　0 redo size   
　　380 bytes sent via SQL*Net to client   
　　503 bytes received via SQL*Net from client   
　　2 SQL*Net roundtrips to/from client   
　　0 sorts (memory)   
　　0 sorts (disk)   
　　1 rows processed   
　　SQL> select count(distinct(object_name)) from dbobjs where created < to_date('01/01/2007','dd/mm/yyyy');   
　　COUNT(DISTINCT(OBJECT_NAME))   
　　----------------------------   
　　4753   
　　Execution Plan   
　　----------------------------------------------------------   
　　0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=75)   
　　1 0 SORT (GROUP BY)   
　　2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DBOBJS' (Cost=1 Card=4 Bytes=300)   
　　3 2 INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=1)   
　　Statistics   
　　----------------------------------------------------------   
　　0 recursive calls   
　　0 db block gets   
　　101 consistent gets   
　　0 physical reads   
　　0 redo size   
　　400 bytes sent via SQL*Net to client   
　　503 bytes received via SQL*Net from client   
　　2 SQL*Net roundtrips to/from client   
　　1 sorts (memory)   
　　0 sorts (disk)   
　　1 rows processed 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.

对于非分区表的测试：

SQL> CREATE TABLE dbobjs2   
　　2 (object_id NUMBER NOT NULL,   
　　3 object_name VARCHAR2(128),   
　　4 created DATE NOT NULL   
　　5 );   
　　Table created.   
　　SQL> CREATE INDEX dbobjs_idx2 ON dbobjs2 (created);   
　　Index created.   
　　SQL> insert into dbobjs2   
　　2 select object_id,object_name,created   
　　3 from dba_objects where created   
　　6227 rows created.   
　　SQL> commit;   
　　Commit complete.   
　　SQL> select count(distinct(object_name)) from dbobjs2 where created < to_date('01/01/2007','dd/mm/yyyy');   
　　COUNT(DISTINCT(OBJECT_NAME))   
　　----------------------------   
　　4753   
　　Execution Plan   
　　----------------------------------------------------------   
　　0 SELECT STATEMENT ptimizer=CHOOSE   
　　1 0 SORT (GROUP BY)   
　　2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DBOBJS2'   
　　3 2 INDEX (RANGE SCAN) OF 'DBOBJS_IDX2' (NON-UNIQUE)   
　　Statistics   
　　----------------------------------------------------------   
　　0 recursive calls   
　　0 db block gets   
　　2670 consistent gets   
　　0 physical reads   
　　1332 redo size   
　　400 bytes sent via SQL*Net to client   
　　503 bytes received via SQL*Net from client   
　　2 SQL*Net roundtrips to/from client   
　　1 sorts (memory)   
　　0 sorts (disk)   
　　1 rows processed 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

当增加表分区时，LOCAL索引被自动维护：

SQL> ALTER TABLE dbobjs   
　　2 ADD PARTITION dbobjs_08 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY'));   
　　Table altered.   
　　SQL> set autotrace off   
　　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   
　　DBOBJS_IDX DBOBJS_08 EYGLE   
　　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   
　　DBOBJS DBOBJS_08 EYGLE 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

关于创建与管理Oracle分区表和本地索引的相关知识及实例就介绍到这里了，如果您想了解更多关于Oracle数据库的知识，可以看一下这里的文章：http://database.51cto.com/oracle/，希望本次的介绍能够对您有所收获！

【编辑推荐】