This can be done using 12c new
feature called partial index. Note that indexes are used mainly to speed queries
or reports. There may be still some indexes like unique index, indexes on foreign
key column and some additional indexes which might be required to speed look-ups to do the insert into tables. Rest of the indexes which could wait
to be asynchronously built but still report in real time could use
PARTIAL indexing.
To create partial indexes, you have to set INDEXING parameter on the table and then alter or create index with the PARTIAL clause.
2) indexing ON/OFF at the table
partition level.
and then
Alter or create index with partial clause.
e.g. alter index global_idx indexing partial;
create index local_idx on tabname(colname) local indexing PARTIAL;
The parameter set at table level to OFF will set newer default partitions created with indexing OFF at partition level. Indexing ON at table level will set INDEXING ON at partition level for newly created partition. The key to note is "newly created". We will make use of this feature to implement a performance strategy.
The global partial index will have only one index segment. The size of the global partial index will be smaller than a full index as it will not have data of partitions where indexing is OFF.
The local partial index will have index segments on partitions where indexing is ON and no index segments on partitions where it is set OFF. In 11g, it was possible to make some of the index segments unuseable. however, it was not easy to maintain. It would not work with rebuilds and the index segments were created even if they are unusable.
The query optimizer will use global/local partial index where ever available and do partition scan where indexing is OFF to get the full picture. The execution plan will have a UNION-ALL to combine the data from index range scan and partition range scan. The cost of this operation will be higher than full index range scan but lesser than a full table scan.
Below explain plan shows the UNION-ALL to combine partial index data with partition scan.
The trade off to use partial index will depend on
- the cost & performance of the queries using partial index
- to the improved speed of OLTP transaction.
This is really application dependent.
It is more favorable to use partial index when
- The oltp transaction rate is critical.
- The performance of queries with partial index is not tht different .
- The resource used to run queries with partial index does not impact the overall performance of the system.
When you ALTER the partition level indexing from OFF to ON, the local index will be created and global index rebuild to add the partition's data.
When you have INTERVAL PARTITION table, Oracle will automatically create the next partition when the data inserted into the table is beyond the current high value of range partition. These partitions will pickup the information from the table level.
At the partition level, INDEXING OFF will mean that the indexes will not be created for that partition.
In order to reduce index contention for application with high inserts, one way is to not have any non-essential indexes on the latest partition where concurrent sessions are inserting data. Having no index on the latest partition will speedup inserts. Reports that do use these indexes will continue to use PARTIAL indexes on older partitions and a Full Table Scan (FTS) on the latest partition.
Steps below describes how to do this.
1) Alter current partitioned table to have indexing OFF at table level. This will ensure that all new partitions added will not automatically enable indexes. ( global and local)
2) If the partition scheme is range partition, you can alter table to have interval partition so that you need not manually add partitions. Although this is a optional step,it is better to have partitions created automatically.
3) create or alter all nonessential indexes with the partial index clause.
4) During inserts into these partitioned tables, newly created partitions will not have partial indexes created. With interval partition, the automatically created partition will not have any partial indexes created. it is the last partition where data is inserted and has usually has the highest contention. Now with less indexes on this partition, the inserts will run a lot faster. At the same time , the reports will still run real time with the latest data inserted. It could be slightly more expensive due to the full scan on the last partition.
5) Have a background job or manually turn INDEXING ON for all partitions where the heavy inserting is not happening. This will automatically rebuild global partial indexed on that table as well as create local partial indexes on that table partition.
That is it. This is one of the strategy you can use to speed OLTP inserts and reduce contention in 12c using PARTIAL INDEXING.
Note: although the example below is using interval range partition, partial indexing will work on other partitions as well.
At the time of writing this blog, there is a documented
Bug 14558315 that does a FTS instead of union-all between index scan and partition scan.
5051.jpg)
