Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Saivishnu.

Asked: March 30, 2020 - 6:29 am UTC

Answered by: Chris Saxon - Last updated: March 30, 2020 - 10:40 am UTC

Category: Database Administration - Version: 11.2.0.2.0

Viewed 100+ times

You Asked

Hi,
We have partitions in our table. We want to build index for just one partition. If we do that, then does it affects the application? One time we got an error like index in unusable state.

First we create index in unusable state like:
Create index idx_name on table(a) local unusable;


Then rebuild for just 1 partition:
Alter index idx_name rebuild partition one online;


Suppose there is partition two. Then, will it affect the appln when it tries to use idx_name for partition two?
Like i mentioned, we did get an error once but after that no such error occurred. So, we don't understand the reason why it happened.

I have one more doubt:
When we rebuild the index offline, exclusive table lock happens.
If we rebuild online, does the table lock happen?

and we said...

Provided you have skip_unusable_indexes set to true (the default), DML should continue fine. If it's false, then you won't be able ins/upd/del on the unusuable partitions:

create table t (
  c1 int, c2 int
) partition by list ( c1 ) (
  partition p1 values ( 1 ),
  partition p2 values ( 2 )
);

create index i 
  on t ( c2 )
  local unusable;
  
alter index i
  rebuild partition p1 online;
  
alter session set skip_unusable_indexes = false;
  
insert into t values ( 1, 1 );
insert into t values ( 2, 2 );

ORA-01502: index 'CHRIS.I' or partition of such index is in unusable state

alter session set skip_unusable_indexes = true;

insert into t values ( 1, 1 );
insert into t values ( 2, 2 );

select * from t;

C1    C2   
    1     1 
    1     1 
    2     2 


The optimizer also won't be able to use the unusable partitions. Which could lead to some funky looking plans for queries without the partition key:

set serveroutput off

select * from t
where  c2 = 2;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

--------------------------------------------------------    
| Id  | Operation                            | Name    |    
--------------------------------------------------------    
|   0 | SELECT STATEMENT                     |         |    
|   1 |  VIEW                                | VW_TE_2 |    
|   2 |   UNION-ALL                          |         |    
|   3 |    PARTITION LIST SINGLE             |         |    
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T       |    
|   5 |      INDEX RANGE SCAN                | I       |    
|   6 |    PARTITION LIST SINGLE             |         |    
|   7 |     TABLE ACCESS FULL                | T       |    
--------------------------------------------------------


we did get an error once but after that no such error occurred

What exactly was the error?

If we rebuild online, does the table lock happen?

The database still takes out locks at the start and end of the rebuild, but not exclusive ones. Richard Foote discusses this further:

https://richardfoote.wordpress.com/2008/02/11/index-create-and-rebuild-locking-improvements-in-11g-ch-ch-ch-changes/

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.