When Oracle makes adjustments to partitions, add update global indexes and oracleindexes.
When you perform ddl operations on a partition, the global index of the partition is invalid. You need to add the keyword update global indexes.
SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
Drop 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 ('2017-11-01 ', 'yyyy-mm-dd ')),
Partition p_2014_12 values less than (to_date ('2017-12-01 ', 'yyyy-mm-dd ')),
Partition p_2015_01 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd ')),
Partition p_2015_02 values less than (to_date ('2017-02-01 ', 'yyyy-mm-dd ')),
Partition p_2015_03 values less than (to_date ('2017-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 dual
Connect 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 traceonly
Select/* + index (t_range ind_t_range_date) */count (1) from t_range where
Test_date = TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
Execution Plan
----------------------------------------------------------
Plan hash value: 2542800765
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("TEST_DATE" = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss '))
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
6 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL * Net to client
415 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Set autotrace off
Alter table t_range drop partition p_2014_11;
Set autotrace traceonly
Select/* + index (t_range ind_t_range_date) */count (1) from t_range where
Test_date = TO_DATE ('2017-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
*
Row 3 has an error:
ORA-01502: Index 'test. IND_T_RANGE_DATE 'or the partition of this type of index is unavailable
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 traceonly
Select/* + index (t_range ind_t_range_date) */count (1) from t_range where
Test_date = TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');
Execution Plan
----------------------------------------------------------
Plan hash value: 2542800765
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("TEST_DATE" = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss '))
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
6 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL * Net to client
415 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Set autotrace off;