創建與管理Oracle分區表和本地索引的實例解析

創建與管理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   
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 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 

對於非分區表的測試：

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 

當增加表分區時，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