Skip to content

SQL Developer Create Index Partition

Partitioned Index

To align with the partitioned table we created by SQL developer previously, we can add a partitioned index for it to improve data search performance.

Let's see the procedure.

  1. New an Index
  2. Index Definition
  3. Partition Definition
  4. Preview DDL
  5. Index Created

New an Index

Right-click on the table that we want it to have the index, it shows a function menu, then we "Create index..." in "Index" section.

SQL Developer - Create a New Index on the Table
SQL Developer - Create a New Index on the Table

Index Definition

We enter the index name and choose TIME_ID as the indexed column.

SQL Developer - Create Index - Index Definition
SQL Developer - Create Index - Index Definition

Partition Definition

We choose "Local" as our partition type.

SQL Developer - Create Index - Partition Type
SQL Developer - Create Index - Partition Type

Then check the item "Define Local Index Partitions".

SQL Developer - Create Index - Define Local Index Partitions
SQL Developer - Create Index - Define Local Index Partitions

Preview DDL

We review DDL for making sure that everything is ready.

SQL Developer - Create Index - Preview DDL
SQL Developer - Create Index - Preview DDL

Index Created

To check if the new index is used, we explain plan on a SQL statement.

SQL Developer - Explain Plan
SQL Developer - Explain Plan

As we can see, the new index has been used.

Leave a Reply

Your email address will not be published. Required fields are marked *