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

reference:http://www.eygle.com/archives/2008/04/partition_local_index.html

 

 

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

　　-The End-

 

reference:http://tech.ddvip.com/2008-08/121974062958157.html