创建与管理Oracle分区表和本地索引的相关知识是本文我们主要要介绍的内容，我们知道，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/','DD/MM/YYYY')),
8PARTITIONdbobjs_07VALUESLESSTHAN(TO_DATE('01/01/','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
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
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
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
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/','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
关于创建与管理Oracle分区表和本地索引的相关知识及实例就介绍到这里了，如果您想了解更多关于Oracle数据库的知识，可以看一下这里的文章：/oracle/，希望本次的介绍能够对您有所收获！
【编辑推荐】
【责任编辑：赵鹏 TEL：(010)68476606】
点赞 0
