mysql 本地索引 全局索引_创建本地索引和全局索引

--创建分区索引:

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_MGR

SQL> 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.