在对分区做ddl操作时，，会使分区全局索引失效，需要加上关键字update global indexes。
SQL> select * from v$version;
BANNER——————————————————————————–Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – ProductionPL/SQL Release 11.2.0.1.0 – ProductionCORE 11.2.0.1.0 ProductionTNS for 32-bit Windows: Version 11.2.0.1.0 – ProductionNLSRTL Version 11.2.0.1.0 – Productiondrop table t_range purge;create table t_range (id number not null PRIMARY KEY, test_date date) partition by range (test_date)( partition p_2014_11 values less than (to_date(‘2014-11-01’, ‘yyyy-mm-dd’)), partition p_2014_12 values less than (to_date(‘2014-12-01’, ‘yyyy-mm-dd’)), partition p_2015_01 values less than (to_date(‘2015-01-01’, ‘yyyy-mm-dd’)), partition p_2015_02 values less than (to_date(‘2015-02-01’, ‘yyyy-mm-dd’)), partition p_2015_03 values less than (to_date(‘2015-03-01’, ‘yyyy-mm-dd’))); insert /*+append */ into t_range select rownum, to_date(to_char(sysdate – 140, ‘J’) + trunc(dbms_random.value(0, 80)), ‘J’) from dualconnect by rownum <= 100000;create index ind_t_range_date on t_range(test_date) nologging;select * from t_range;exec dbms_stats.gather_table_stats(user,’t_range’,cascade => true);set autotrace traceonlyselect /*+index(t_range ind_t_range_date)*/count(1) from t_range wheretest_date = TO_DATE(‘2015-01-01 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’);执行计划———————————————————-Plan hash value: 2542800765————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |————————————————————————————–| 0 | SELECT STATEMENT | | 1 | 8 | 23 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 8 | | ||* 2 | INDEX RANGE SCAN| IND_T_RANGE_DATE | 1243 | 9944 | 23 (0)| 00:00:01 |————————————————————————————–Predicate Information (identified by operation id):————————————————— 2 – access("TEST_DATE"=TO_DATE(‘ 2015-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))统计信息———————————————————- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedset autotrace offalter table t_range drop partition p_2014_11;set autotrace traceonlyselect /*+index(t_range ind_t_range_date)*/count(1) from t_range wheretest_date = TO_DATE(‘2015-01-01 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’);select /*+index(t_range ind_t_range_date)*/count(1) from t_range where*第 1 行出现错误:ORA-01502: 索引 ‘TEST.IND_T_RANGE_DATE’ 或这类索引的分区处于不可用状态set autotrace off;alter index ind_t_range_date rebuild nologging;alter table t_range drop partition p_2014_12 update global indexes;set autotrace traceonlyselect /*+index(t_range ind_t_range_date)*/count(1) from t_range wheretest_date = TO_DATE(‘2015-01-01 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’);执行计划———————————————————-Plan hash value: 2542800765————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |————————————————————————————–| 0 | SELECT STATEMENT | | 1 | 8 | 13 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 8 | | ||* 2 | INDEX RANGE SCAN| IND_T_RANGE_DATE | 1243 | 9944 | 13 (0)| 00:00:01 |————————————————————————————–Predicate Information (identified by operation id):————————————————— 2 – access("TEST_DATE"=TO_DATE(‘ 2015-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))统计信息———————————————————- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedset autotrace off;
蚁穴虽小，溃之千里。
