In previous article we saw what is partitioning in oracle and the benefits it brings .
http://oracle-help.com/oracle-database/oracle-partitioning/
In this article we will see Index partitioning.
Indexes are the essential part of any SELECT and DML operations which we run on the database. So , when we tune the table we must consider index too. Index partitioning is a magnificent feature when it comes to performance. Just like we saw partitioning in table , indexes can be partitioned in same manner. Index partitioning divides index into smaller , more manageable pieces.
We will discuss types of partitioned indexes and when to partition the index.
The first thing we need to see while partition the index is which type of index we should choose.
There are mainly two types of partition indexes available in the Oracle.
1.GLOBAL Index : Global index can be created on partitioned and non-partitioned table. There is no relation between the partition of index and partition of table.
You can create Global prefixed index only.
Global indexes can be unique or non-unique.
You should go for the global index when you are working on OLTP system and the queries fetch data from the whole table.
We will see global indexes practically in upcoming posts.
2.LOCAL Index : There are two types of LOCAL partitioned indexes are available in the Oracle.
Prefixed LOCAL Index : Each partition in local index is correspond with respective table partition when it is equipartitiond index . It provides greater benefit in execution. LOCAL key word is defined when creating LOCAL prefixed Index.
NONPrefixed LOCAL Index : It does not correspond with table partitions. It is usually used when you want to index non-partitioned index. It does not support partition pruning.
Now When should we use Index Partitioning Feature :
1.Partitioned index brings more manageability, availability, performance, and scalability in db operations.
2.When you are using OLTP system , you should go for GLOBAL index.
3.When you are more interested in fetching the partition wise data and you have already created a table partition with the same concern you should go for LOCAL prefixed index.
4.When you want to index columns which are not subset of table partitioning keys and you are interested in fetching the data from some partitions you should go for LOCAL nonprefixed index.
5.LOCAL indexes provides more flexibility in terms of manageability and availability so if that is your concern you should go for LOCAL indexes only.
We will explore all types of partitioned indexes in upcoming posts.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
