An index is basically a structure that maps keys (values) in columns to the physical location of their corresponding rows in a table. So if you move the rows (ie, change the physical location of a row) then the index entries for those rows need to be updated, or the index is no longer usable. And as most people are aware, the latter is the default when you perform a partition maintenance operation on a table that re-locates rows. For example, we’ll create a simple partitioned table, add both a local and a global index, split one of the partitions, and then see what the impact on those indexes is.
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t (x int, y int, z int )
2 partition by range (x)
3 (
4 partition p1 values less than (4000),
5 partition p2 values less than (8000)
6 );
Table created.
SQL>
SQL> insert into t select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level <= 7 );
548338 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create index ix_local on t ( y ) local;
Index created.
SQL> create index ix_global on t ( z ) ;
Index created.
SQL>
SQL> alter table t split partition p2 at (6000)
2 into ( partition p2a, partition p2b ) ;
Table altered.
SQL>
SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name like 'IX%';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
IX_LOCAL P1 USABLE
IX_LOCAL P2A UNUSABLE
IX_LOCAL P2B UNUSABLE
3 rows selected.
SQL>
SQL> select index_name, status
2 from user_indexes
3 where index_name like 'IX%';
INDEX_NAME STATUS
------------------------------ --------
IX_GLOBAL UNUSABLE
IX_LOCAL N/A
2 rows selected.
SQL>
SQL>
SQL>
The result is what we would expect. The global index has become unusable because a portion of the rows that had entries in the index have been moved as part of the partition split operation. The local index has also become unusable but only for those partitions that were impacted by the split. Partition P1 was not affected by the split, and hence the corresponding local index partition is still valid. Just a quick footnote – the “N/A” in USER_INDEXES for the local index represents that the STATUS of the local index should be determined by looking at the status for each underlying partition. In early releases of Oracle this could create problems in terms of availability for partitioned tables, because if you tried to use an index that had become unusable, you would get an error: ORA-01502: index or partition of such index is in unusable state, which is not the most pleasant thing to be sending back to your application users 
In Oracle 9, a workaround to the problem was introduced, the UPDATE GLOBAL INDEXES clause. This keeps the (global) index entries valid by correcting the index entries as the partition operation is performed. This has some costs in terms of resource consumption, because obviously maintaining a large amount of index entries will consume CPU and redo, but it kept the index available to application users. Here’s a demo of that in action:
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t (x int, y int, z int )
2 partition by range (x)
3 (
4 partition p1 values less than (4000),
5 partition p2 values less than (8000)
6 );
Table created.
SQL>
SQL> insert into t select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level <= 7 );
548338 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create index ix_local on t ( y ) local;
Index created.
SQL> create index ix_global on t ( z ) ;
Index created.
SQL>
SQL> alter table t split partition p2 at (6000)
2 into ( partition p2a, partition p2b )
3 update global indexes;
Table altered.
SQL>
SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name like 'IX%';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
IX_LOCAL P1 USABLE
IX_LOCAL P2A UNUSABLE
IX_LOCAL P2B UNUSABLE
3 rows selected.
SQL>
SQL> select index_name, status
2 from user_indexes
3 where index_name like 'IX%';
INDEX_NAME STATUS
------------------------------ --------
IX_GLOBAL VALID
IX_LOCAL N/A
2 rows selected.
SQL>
SQL> alter index ix_local rebuild partition P2A;
Index altered.
SQL> alter index ix_local rebuild partition P2B;
Index altered.
But notice that the local index has not been spared from being marked unusable. We had to rebuild each local partition after the operation. You might be thinking that only having UPDATE GLOBAL INDEXES was a hence a waste of time, but don’t forget that some partition operations, such as DROP and TRUNCATE do not impact local indexes, so updating the global index entries is all that is required:
SQL> alter table t drop partition p2a update global indexes;
Table altered.
SQL>
SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name like 'IX%';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
IX_LOCAL P1B USABLE
IX_LOCAL P2B USABLE
2 rows selected.
SQL>
SQL> select index_name, status
2 from user_indexes
3 where index_name like 'IX%';
INDEX_NAME STATUS
------------------------------ --------
IX_GLOBAL VALID
IX_LOCAL N/A
2 rows selected.
SQL>
SQL>
But if you do have existing scripts that contain the UPDATE GLOBAL INDEXES clause, it is perhaps worth revisiting them to take advantage of the more complete implementation of this facility, which arrived in Oracle 11. The UPDATE INDEXES clause will take care of both global and local indexes during partition maintenance operations.
SQL>
SQL> alter table t split partition p1 at (2000)
2 into ( partition p1a, partition p1b )
3 update indexes;
Table altered.
SQL>
SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name like 'IX%';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
IX_LOCAL P1A USABLE
IX_LOCAL P1B USABLE
IX_LOCAL P2A USABLE
IX_LOCAL P2B USABLE
4 rows selected.
SQL>
SQL> select index_name, status
2 from user_indexes
3 where index_name like 'IX%';
INDEX_NAME STATUS
------------------------------ --------
IX_GLOBAL VALID
IX_LOCAL N/A
2 rows selected.
SQL>
SQL> alter table t drop partition p1a update indexes;
Table altered.
SQL>
SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name like 'IX%';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
IX_LOCAL P1B USABLE
IX_LOCAL P2A USABLE
IX_LOCAL P2B USABLE
3 rows selected.
SQL>
SQL> select index_name, status
2 from user_indexes
3 where index_name like 'IX%';
INDEX_NAME STATUS
------------------------------ --------
IX_GLOBAL VALID
IX_LOCAL N/A
2 rows selected.
SQL>
Not that I can see a real need for it, but it’s worth noting that these are your only two options. There is no such thing as keeping only the local indexes entries updated and not the global ones.
SQL> alter table t split partition p2 at (6000)
2 into ( partition p2a, partition p2b ) update local indexes;
into ( partition p2a, partition p2b ) update local indexes
*
ERROR at line 2:
ORA-00905: missing keyword
Finally, remember that since Oracle 10g we decided that it is better to allow a query to run slowly rather than have it crash with an error, so the default value for “skip_unusable_indexes” is now TRUE. So if you have indexes that have a status of UNUSABLE, you will not detect this via errors in your SQL queries. The queries will just be optimized as if the indexes did not exist, which might have an impact on their execution time. Here is a a simple starting point for a monitoring query to keep an eye on unusable indexes (and rebuild them if necessary):
SQL> set serverout on
SQL> declare
2 procedure ddl(p_cmd varchar2) is
3 begin
4 --execute immediate p_cmd;
5 dbms_output.put_line(p_cmd);
6 end;
7 begin
8 for i in (
9 select index_owner, index_name, partition_name, 'partition' ddl_type
10 from all_ind_partitions
11 where status = 'UNUSABLE'
12 union all
13 select index_owner, index_name, subpartition_name, 'subpartition' ddl_type
14 from all_ind_subpartitions
15 where status = 'UNUSABLE'
16 union all
17 select owner, index_name, null, null
18 from all_indexes
19 where status = 'UNUSABLE'
20 )
21 loop
22 if i.ddl_type is null then
23 ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild');
24 else
25 ddl('alter index '||i.index_owner||'.'||i.index_name||' modify '||i.ddl_type||' '||i.partition_name||' rebuild');
26 end if;
27 end loop;
28 end;
29 /
alter index MCDONAC.T_PAR_IX modify partition P1 rebuild
alter index MCDONAC.T_PAR_IX modify partition P2 rebuild
PL/SQL procedure successfully completed.
And finally, as Tim reminded me, with 12c Release 2, many partition operations (and many other maintenance operations as well) can now be done online simply by specifying ONLINE as a suffix to the operation. That (as the name suggests) will keep all indexes in a USABLE state.
SQL> create table t (x int, y int, z int )
2 partition by range (x)
3 (
4 partition p1 values less than (4000),
5 partition p2 values less than (8000)
6 );
Table created.
SQL>
SQL> insert into t select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level <= 7 );
547862 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create index ix_local on t ( y ) local;
Index created.
SQL>
SQL> create index ix_global on t ( z ) ;
Index created.
SQL>
SQL> alter table t split partition p2 at (6000)
2 into ( partition p2a, partition p2b ) online;
Table altered.
SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name like 'IX%';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IX_LOCAL P1 USABLE
IX_LOCAL P2A USABLE
IX_LOCAL P2B USABLE
3 rows selected.
SQL>
SQL> select index_name, status
2 from user_indexes
3 where index_name like 'IX%';
INDEX_NAME STATUS
------------------------------ --------
IX_GLOBAL VALID
IX_LOCAL N/A
2 rows selected.
. I’m sure that as I type this post, there is probably an Oracle Apps Functional Analyst preparing a blog post about how database content has gone overboard and there should be more Apps content. It’s a fact of life that you can’t please everyone. 
explain the benefit.
