建立與管理Oracle的分區表和本地索引

     在網上看到eygle寫的一篇關於分區表和本地索引的文章，感覺總結的挺好，特轉了過來。

     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

　　      

PS：我建了幾個oracle QQ群，歡迎資料庫愛好者加入。 Oracle專家QQ1群：60632593     Oracle專家QQ2群：60618621      Oracle專家QQ3群：23145225

-The End-