-------
2 - filter(MHO_DATE=TO_DATE(' 2012-12-01 00:00:00','syyyy-mm-dd hh24:mi:ss'))
3 - access(MHO_TYP_ID=0)
这次LC_NON_PREFIXED_TYP_I只执行了一次
那我们换成global index呢？
SQL>select * from partitioned_tab where mho_typ_id = 0;
------------------------------------------------------------------------------------------------------------------
| Id | Operation |Name | Starts | E-Rows | A-Rows |Buffers | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | | 1493 | 1496 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PARTITIONED_TAB | 1 | 1493 | 1493 | 1496 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | GL_TYP_I | 1 | 1493 | 1493 | 4 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identifiedby operation id):
---------------------------------------------------
2 - access(MHO_TYP_ID=0)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1496 consistent gets
1493 physical reads
0 redosize
28937 bytes sent via SQL*Netto client
372 bytes received via SQL*Netfrom client
4 SQL*Net roundtripsto/fromclient
0 sorts (memory)
0 sorts (disk)
1493 rows processed
索引只执行了一次，consistent gets为1496 是用local的一半。索引建立global或local index 我们需要根据分区数量、分区稳定性、sql语句综合考虑。
?