oracle10G分区的创建与维护Oracle分区表和本地索引

 

　　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   　　-The End-