Hi, we are using version 11.2.0.4 of oracle. We have a requirement in which we compress one historical table partition each day as we move forward, to maintain storage space. And its respective local indexes are being rebuild then as part of same process. We have lot of big partition tables and this way we restrict the space usage. . But during this time we see the indexes remain in UNUSABLE state for few minutes(in between MOVE and REBUILD step) and SELECT query which runs on the table during that exact time suffers due to optimizer not using the index which is in UNUSABLE state. Now even the UNUSABLE index partition belong to historical partition which we are not querying upon but still Optimizer is not using that index in the query plan if any one of the index partition shows to be in UNUSABLE status when it gets parsed.
We want to avoid this issue, so my thought was to use "update indexes" which will ensure the index status to be in USABLE state throughout, but i just got to know and tested its not true. Internally its making those index UNUSABLE and the status is also visible in DBA_IND_PARTITIONS during the compression using "update indexes" method , which means again the SELECT queries are going to suffer.
Then i was thinking if its okay to set "skip_unusable_indexes=false" so that queries can forcibly utilize the unusable index while running the SELECT queries, but then, i was wrong to understand its usage. When i ran the select by setting skip_unusable_indexes=false at session level , i got error "ORA-01502: index 'index1' or partition of such index is in unusable state".
So want to understand from experts is there a way out to handle this or any other work around? I.e. making the indexes available for the READ queries during Compression that to on 11.2.0.4?
Instead below:-
Alter table user1.tab1 move partition part1 compress for query high parallel 12;
Alter index user1.index1 rebuild partition part1 parallel 12;
I was planning to use below, but its making indexes UNUSABLE too:-
Alter table user1.tab1 move partition part1 compress for query high update indexes parallel 12;