Updating indexes with partition maintenance

September 20, 2017

7 minutes

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  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 Smile

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

14 responses to “Updating indexes with partition maintenance”

Lancerique

August 25, 2018 at 5:38 am

Thanks Connor for the blog. Can we do update indexes with online clause?

Reply
Connor McDonald

August 27, 2018 at 6:37 pm

Yes we can. Check out my youtube videos for some examples https://www.youtube.com/c/ConnorMcDonaldOracle

Reply
1. Vitor Jr
  
  March 11, 2020 at 8:11 pm
  
  Hi Connor,
  
  can we specify a new tablespace for indexes with update indexes clause?
  best regards,
  Vitor Jr.
  
  Reply
2018-what grabbed your attention – Learning is not a spectator sport

January 7, 2019 at 10:06 pm

[…] Updating indexes with partition maintenance […]

Reply
Nenad Noveljic (@NenadNoveljic)

September 16, 2019 at 3:01 pm

Hi Connor,
The INSERT in your test case seems to be incomplete. I replaced it with the following:
insert into t select rownum, rownum, rownum from dual connect by level < 8000 ;

Reply
2019-what grabbed your attention – Learning is not a spectator sport

December 9, 2019 at 8:06 pm

[…] Updating indexes with partition maintenance […]

Reply
Vitor Jr

March 11, 2020 at 8:12 pm

Hi Connor,

can we specify a new tablespace for indexes with update indexes clause?
best regards,
Vitor Jr.

Reply
1. Connor McDonald
  
  March 11, 2020 at 9:51 pm
  
  not to me knowledge, because we are modifying the existing index, not rebuilding it
  
  Reply
  1. Vitor Jr
    
    March 11, 2020 at 10:26 pm
    
    Yes, it’s possible 🙂
    
    ALTER TABLE dept_exp MOVE
    COMPRESS TABLESPACE tbs_2
    UPDATE INDEXES
    (i1_deptno TABLESPACE tbs_1,
    i2_dname TABLESPACE tbs_2);
    
    And even for partitions:
    ALTER TABLE costs
    SPLIT PARTITION costs_q4_2003 at
    (TO_DATE(’01-Nov-2003′,’dd-mon-yyyy’))
    INTO (PARTITION c_p1, PARTITION c_p2)
    UPDATE INDEXES (cost_ix (PARTITION c_p1 tablespace tbs_02,
    PARTITION c_p2 tablespace tbs_03));
    
    Finally found this in documentation.
    
    Best regards,
    Vitor Jr.
    
    Reply
    1. Connor McDonald
      
      March 11, 2020 at 10:42 pm
      
      True but I’d contend that somewhat functionally different, ie, you are reorganizing the entire structure.
      
      But thanks for posting it, so others don’t get the wrong impression from my comments.
      
      Reply
Dirty_Harry

May 26, 2020 at 12:24 am

Great article :-))))
A former Oracle Colleague

Reply
nitschke

June 5, 2020 at 10:59 pm

Hello,

IS UPDATE INDEXES fast with big tables ?
Can it be done in parallel (like rebuild ) to speed up ?

thanks

Reply
1. Connor McDonald
  
  June 10, 2020 at 7:53 am
  
  Updating indexes will slow things down because it not too much different from a large delete and insert. 12.2 and above improve things a lot by deferring this to be an asynch task. Note – the work *still* gets done, it just gets done in the background and hence you reduce the outage window
  
  Reply
Adrian

February 8, 2024 at 8:37 pm

Very interesting note with summary.
I am comming with some question regarding Oracle 19c and reorganization task for a table with subpartitions (range, hash), which to move all partitions with update indexes, when the index is global, takes ages.
is it possible to select a set of partitions (let’s say part 1, 2 and 3, which will be moved first) and create a new index with the same structure than the global whole index, and rebuild the original global index for those partitions not involved in the move (partitions 4 to 200 for example) ?

Partial Indexes for Partitioned Tables in Oracle Database.

Thank you

Reply