LOCALITY:表明分区索引是LOCAL的还是GLOBAL的。

--创建分区索引:create index IDX_MGR_BASE_MGR on T_PM_MGR_BASE (MGR_CODE) LOCAL parallel 16 nologging ;SQL> select * from T_PM_MGR_BASE  where data_date=20131012 and mgr_code='03319';184 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 949292481-----------------------------------------------------------------------------------------------------------------------| Id  | Operation			   | Name	      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT		   |		      |   195 | 29250 |   196	(0)| 00:00:03 |       |       ||   1 |  PARTITION LIST SINGLE		   |		      |   195 | 29250 |   196	(0)| 00:00:03 |   KEY |   KEY ||   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_MGR_BASE    |   195 | 29250 |   196	(0)| 00:00:03 |   654 |   654 ||*  3 |    INDEX RANGE SCAN		   | IDX_MGR_BASE_MGR |   195 |       |     3	(0)| 00:00:01 |   654 |   654 |-----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("MGR_CODE"='03319')

走了本地索引:

create index IDX_MGR_BASE_MGR on T_PM_MGR_BASE (MGR_CODE)  parallel 16 nologging ;

---创建全局索引

SQL> select index_name from dba_indexes  where index_name='IDX_MGR_BASE_MGR';INDEX_NAME------------------------------IDX_MGR_BASE_MGRSQL>  explain plan for select * from T_PM_MGR_BASE where mgr_code='03319';Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 158496794-----------------------------------------------------------------------------------------------------------------------| Id  | Operation			   | Name	      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT		   |		      | 91239 |    13M| 90120	(1)| 00:18:02 |       |       ||   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_PM_MGR_BASE    | 91239 |    13M| 90120	(1)| 00:18:02 | ROWID | ROWID ||*  2 |   INDEX RANGE SCAN		   | IDX_MGR_BASE_MGR | 91239 |       |   272	(1)| 00:00:04 |       |       |-----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("MGR_CODE"='03319')14 rows selected.