In previous article we have seen overview of index partitioning and advantages of it.

In this article we will see example of different types of indexes on partitioned tables.

1.Non-partitioned Index:

We can create normal non-partitioned index on partitioned table.

Example : TRANSACTIONS table is partitioned table in my database.

2.Global Range Partitioned Indexes : We can create GLOBAL index on partitioned table :

Example : 

We have already created index on this table and on the same column. Lets drop it and create it again.

3.Global Hash Partitioned Index : Hash partitioned index will distribute data evenly using hashing algorithm.

Note : With Global option we can only create hash and range index partitioning.

4.Local Index on Partitioned Table : 

5.LOCAL Partitioned Index : You need to define same number of partitions for the index as you have defined for the table if you are creating local partitioned index and you want to explicitly define partitions.

6.Create Global Index :

7.Create Local Bitmap Index :

In above code we can see that we are not able to create bitmap index globally as it partitioned table. Let’s try with LOCAL keyword[Local Index].

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

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.