创建与管理Oracle的分区表和本地索引

在网上看到eygle写的一篇关于分区表和本地索引的文章，感觉总结的挺好，特转了过来。

Oracle的分区技术在某些条件下可以极大的提高查询的性能，所以被广泛采用。从产品上说，分区技术是Oracle企业版中独立收费的一个组件。以下是对于分区及本地索引的一个示例。

　　首先根据字典表创建一个测试分区表：

SQL>connecteygle/eygle 　　Connected. 　　SQL>CREATETABLEdbobjs 　　2(OBJECT_IDNUMBERNOTNULL, 　　3OBJECT_NAMEvarchar2(128), 　　4CREATEDDATENOTNULL 　　5) 　　6PARTITIONBYRANGE(CREATED) 　　7(PARTITIONdbobjs_06VALUESLESSTHAN(TO_DATE('01/01/2007','DD/MM/YYYY')), 　　8PARTITIONdbobjs_07VALUESLESSTHAN(TO_DATE('01/01/2008','DD/MM/YYYY'))); 　　Tablecreated. 　　SQL>COLsegment_namefora20 　　SQL>COLPARTITION_NAMEfora20 　　SQL>SELECTsegment_name,partition_name,tablespace_name 　　2FROMdba_segments 　　3WHEREsegment_name='DBOBJS'; 　　SEGMENT_NAMEPARTITION_NAMETABLESPACE_NAME 　　---------------------------------------------------------------------- 　　DBOBJSDBOBJS_06EYGLE 　　DBOBJSDBOBJS_07EYGLE

　　创建一个Local索引，注意这里可以将不同分区的索引指定创建到不同的表空间：

SQL>CREATEINDEXdbobjs_idxONdbobjs(created)LOCAL 　　2(PARTITIONdbobjs_06TABLESPACEusers, 　　3PARTITIONdbobjs_07TABLESPACEusers 　　4); 　　Indexcreated.

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

CREATEINDEXdbobjs_idxONdbobjs(created)LOCAL 　　(PARTITIONdbobjs_06TABLESPACEusers, 　　PARTITIONdbobjs_07TABLESPACEusers 　　)TABLESPACEusers;

通过统一的tablespace子句为索引指定表空间。

SQL>COLsegment_namefora20 　　SQL>COLPARTITION_NAMEfora20 　　SQL>SELECTsegment_name,partition_name,tablespace_name 　　2FROMdba_segments 　　3WHEREsegment_name='DBOBJS_IDX'; 　　SEGMENT_NAMEPARTITION_NAMETABLESPACE_NAME 　　---------------------------------------------------------------------- 　　DBOBJS_IDXDBOBJS_06USERS 　　DBOBJS_IDXDBOBJS_07USERS 　　SQL>insertintodbobjs 　　2selectobject_id,object_name,created 　　3fromdba_objectswherecreated 　　6227rowscreated. 　　SQL>commit; 　　Commitcomplete. 　　SQL>selectcount(*)fromdbobjspartition(DBOBJS_06); 　　COUNT(*) 　　---------- 　　6154 　　SQL>selectcount(*)fromdbobjspartition(dbobjs_07); 　　COUNT(*) 　　---------- 　　73

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

SQL>setautotraceon 　　SQL>selectcount(*)fromdbobjswherecreated<to_date('01/01/2008','dd/mm/yyyy'); 　　COUNT(*) 　　---------- 　　6227 　　ExecutionPlan 　　---------------------------------------------------------- 　　0SELECTSTATEMENTptimizer=CHOOSE(Cost=1Card=1Bytes=9) 　　10SORT(AGGREGATE) 　　21PARTITIONRANGE(ALL) 　　32INDEX(RANGESCAN)OF'DBOBJS_IDX'(NON-UNIQUE)(Cost=2Card=8Bytes=72) 　　Statistics 　　---------------------------------------------------------- 　　0recursivecalls 　　0dbblockgets 　　25consistentgets 　　0physicalreads 　　0redosize 　　380bytessentviaSQL*Nettoclient 　　503bytesreceivedviaSQL*Netfromclient 　　2SQL*Netroundtripsto/fromclient 　　0sorts(memory) 　　0sorts(disk) 　　1rowsprocessed 　　SQL>selectcount(*)fromdbobjswherecreated<to_date('01/01/2007','dd/mm/yyyy'); 　　COUNT(*) 　　---------- 　　6154 　　ExecutionPlan 　　---------------------------------------------------------- 　　0SELECTSTATEMENTptimizer=CHOOSE(Cost=1Card=1Bytes=9) 　　10SORT(AGGREGATE) 　　21INDEX(RANGESCAN)OF'DBOBJS_IDX'(NON-UNIQUE)(Cost=2Card=4Bytes=36) 　　Statistics 　　---------------------------------------------------------- 　　0recursivecalls 　　0dbblockgets 　　24consistentgets 　　0physicalreads 　　0redosize 　　380bytessentviaSQL*Nettoclient 　　503bytesreceivedviaSQL*Netfromclient 　　2SQL*Netroundtripsto/fromclient 　　0sorts(memory) 　　0sorts(disk) 　　1rowsprocessed 　　SQL>selectcount(distinct(object_name))fromdbobjswherecreated<to_date('01/01/2007','dd/mm/yyyy'); 　　COUNT(DISTINCT(OBJECT_NAME)) 　　---------------------------- 　　4753 　　ExecutionPlan 　　---------------------------------------------------------- 　　0SELECTSTATEMENTptimizer=CHOOSE(Cost=1Card=1Bytes=75) 　　10SORT(GROUPBY) 　　21TABLEACCESS(BYLOCALINDEXROWID)OF'DBOBJS'(Cost=1Card=4Bytes=300) 　　32INDEX(RANGESCAN)OF'DBOBJS_IDX'(NON-UNIQUE)(Cost=2Card=1) 　　Statistics 　　---------------------------------------------------------- 　　0recursivecalls 　　0dbblockgets 　　101consistentgets 　　0physicalreads 　　0redosize 　　400bytessentviaSQL*Nettoclient 　　503bytesreceivedviaSQL*Netfromclient 　　2SQL*Netroundtripsto/fromclient 　　1sorts(memory) 　　0sorts(disk) 　　1rowsprocessed

对于非分区表的测试：

SQL>CREATETABLEdbobjs2 　　2(object_idNUMBERNOTNULL, 　　3object_nameVARCHAR2(128), 　　4createdDATENOTNULL 　　5); 　　Tablecreated. 　　SQL>CREATEINDEXdbobjs_idx2ONdbobjs2(created); 　　Indexcreated. 　　SQL>insertintodbobjs2 　　2selectobject_id,object_name,created 　　3fromdba_objectswherecreated 　　6227rowscreated. 　　SQL>commit; 　　Commitcomplete. 　　SQL>selectcount(distinct(object_name))fromdbobjs2wherecreated<to_date('01/01/2007','dd/mm/yyyy'); 　　COUNT(DISTINCT(OBJECT_NAME)) 　　---------------------------- 　　4753 　　ExecutionPlan 　　---------------------------------------------------------- 　　0SELECTSTATEMENTptimizer=CHOOSE 　　10SORT(GROUPBY) 　　21TABLEACCESS(BYINDEXROWID)OF'DBOBJS2' 　　32INDEX(RANGESCAN)OF'DBOBJS_IDX2'(NON-UNIQUE) 　　Statistics 　　---------------------------------------------------------- 　　0recursivecalls 　　0dbblockgets 　　2670consistentgets 　　0physicalreads 　　1332redosize 　　400bytessentviaSQL*Nettoclient 　　503bytesreceivedviaSQL*Netfromclient 　　2SQL*Netroundtripsto/fromclient 　　1sorts(memory) 　　0sorts(disk) 　　1rowsprocessed

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

SQL>ALTERTABLEdbobjs 　　2ADDPARTITIONdbobjs_08VALUESLESSTHAN(TO_DATE('01/01/2009','DD/MM/YYYY')); 　　Tablealtered. 　　SQL>setautotraceoff 　　SQL>COLsegment_namefora20 　　SQL>COLPARTITION_NAMEfora20 　　SQL>SELECTsegment_name,partition_name,tablespace_name 　　2FROMdba_segments 　　3WHEREsegment_name='DBOBJS_IDX'; 　　SEGMENT_NAMEPARTITION_NAMETABLESPACE_NAME 　　---------------------------------------------------------------------- 　　DBOBJS_IDXDBOBJS_06USERS 　　DBOBJS_IDXDBOBJS_07USERS 　　DBOBJS_IDXDBOBJS_08EYGLE 　　SQL>SELECTsegment_name,partition_name,tablespace_name 　　2FROMdba_segments 　　3WHEREsegment_name='DBOBJS'; 　　SEGMENT_NAMEPARTITION_NAMETABLESPACE_NAME 　　---------------------------------------------------------------------- 　　DBOBJSDBOBJS_06EYGLE 　　DBOBJSDBOBJS_07EYGLE 　　DBOBJSDBOBJS_08EYGLE

　　

PS：我建了几个oracle，欢迎数据库爱好者加入。

Oracle专家QQ1群：60632593

Oracle专家QQ2群：60618621

Oracle专家QQ3群：23145225

-The End-