Script categories

Wednesday, 4 December 2013

Exchange partition and ensuring indexes are usable


In my previous post I highlighted a few of the issues one can come across when dealing with indexes and the Partition Exchange Technique (PET). The index was marked as unusable, even though the index columns and constraint type (primary) matched.

For example:

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load;

Table altered.

SQL> select uix1.index_name
  2       ,null as partition_name
  3       ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9       ,uip.partition_name
 10       ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                             and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        UNUSABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.


As can be seen, partition P131113 is marked as unusable. The local partion index would have to therefore be rebuilt, as follows:

SQL> alter index TRADE_ACCOUNT_PK rebuild partition P131113;

Index altered.

SQL> select uix1.index_name
  2       ,null as partition_name
  3       ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9       ,uip.partition_name
 10       ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                             and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ -------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        USABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.



The local partion index is now shown as usable. Assuming the indexes/constraints match, one can skip the rebuild by including INCLUDE INDEXES in the exchange statement, for example:

SQL> select uix1.index_name
  2       ,null as partition_name
  3       ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9       ,uip.partition_name
 10       ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                             and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        USABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load
  3  including indexes;

Table altered.

SQL> select uix1.index_name
  2       ,null as partition_name
  3       ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9       ,uip.partition_name
 10       ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                             and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        USABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.


The good news is that the local partitioned index remains usable. The same however does not apply to global indexes. For global indexes one can specify the UPDATE GLOBAL INDEXES, that will ensure they are usable as well, as follows:

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load
  3  including indexes
  4  update global indexes;

Table altered.



Bear in mind, specifying that global indexes are updated as part of the exchange will add a significant workload for large volumed tables and might take a considerable amount of time. For global indexes there are several parallel techniques that can be employed instead, that can be initiated after the exchange. The only downside is that execution plans that refer to the global index will no longer be reproducible whilst the index remains in an unusable state. 

It is however possible to force Oracle to ignore the fact that an index is unusable, which will ensure that the optimizer will continue to use the same execution plans where unusable indexes are referenced. The initilisation parameter to modify in order to enable such behaviour is SKIP_UNUSABLE_INDEXES and can be set at either the session or system level as follows;

SQL> alter system set SKIP_UNUSABLE_INDEXES = false;

System altered.


SQL> alter session set SKIP_UNUSABLE_INDEXES = false;

Session altered.


It is advisable to keep to the default behaviour, which is SKIP_UNUSABLE_INDEXES set to TRUE, as the impact on execution and application behaviour would be unpredictable.

Another option is to specify PARALLEL along with the UPDATE GLOBAL INDEXES, which to a degree mitigates the expected workload, but should be used with caution as one cannot guarantee the overall Oracle workload and resource usage at the time of issuing the statement, and may ultimately cause disruption to other users. Use of the PARALLEL clause, looks as follows:

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load
  3  including indexes
  4  update global indexes parallel;

Table altered.


No comments:

Post a Comment