建立與維護Oracle分區表和本地索引

【原文： http://www.searchdatabase.com.cn/showcontent_12937.htm 】

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