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 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.
2 exchange partition P131113 with table trade_account_load
3 including indexes
4 update global indexes parallel;
Table altered.
No comments:
Post a Comment