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?
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 occurredWhat 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/