Create Index Oracle with Real Life Industrial Examples :

In my previous article I have given the idea about indexing in SQL with real life examples. In this article I would like to give the basic idea about the Create Index oracle in SQL with some examples. I will explain the different ways to create index Oracle with its syntax. The main motive of creating the indexes in SQL is for improving the performance of SQL statements. User can create indexes on SQL columns for different scenarios. I would like to explain each and every scenario of creating index in detail.

Where to Create Index Oracle ?

Everybody has question in the mind that where exactly do we need to create indexes? In this section I would like to explain the different scenarios with syntaxes and examples to create index oracle. These scenarios will be helpful to user to create index on table column.

Scenario 1 : Create index Oracle on simple table

The performance tuning is most important factor for any of the application. If the performance of application is not good then that application will led to fail. To improve the performance user can create the index on table. To create index on table columns is first scenario. User can create the index on one or more columns of single table. User needs to create indexes by analyzing the explain plan of the SQL statement.After checking the explain plan user needs to create indexes accordingly.

Syntax :

Create index Indexname

On tableName(Column_name);

Real life example :

Lets say Employee_No from Employee table does not have primary key. It will not create the unique index on employee table.User needs to create index externally on that column.

Create index SI_Employee_No

on Employee(Employee_No);

The above statement will create the index on Employee table. The name of the index is SI_Employee_No which is b-tree index.

Scenario 2 : Create Index on Partitioned table

User can create the index on partitioned tables as well. The partitioning of table is most important concept to improve the performance of the query. The main thing is the partition should be on correct column. After partition user can create index on column. For that user need to rebuild the local indexes on partitioned table. The local indexes are also called as Partitioned indexes. User needs to use partitioned indexes where the table is partitioned. The query will not take the global indexes on partitioned tables. User needs to create either the local indexes or global partitioned indexes on partitioned tables. I have already explained the table partitioning concepts in my previous article.

There are two types of partitioned indexes :

1.Global Partitioned Indexes

2.Local Partitioned Indexes

Global Partitioned Indexes are used to partition the table and allocate the index on the partitioned table. The global_partition_index clause is used to partition the indexes globally. There are different types of partitioning techniques in oracle.According to that partitioning techniques the global partitioning indexes are created.

1.Range Global Indexes :

When the range partitioning is used in the table at that time the indexes created on that table should be range global indexes.

Syntax :

Create Index IndexName

On Table_Name(Column_Name)

Global Partition by Range(column_name)

Range Specified;

User needs to check the Range partition examples before creating or studying about the range Global Index.

Real Life Example :

Lets say user have huge employee table and needs to create range partition on Salary column of employee table then following statement will be useful,

Create Index RP_Employee_Salary

On Employee(salary)

Global Partition by Range(salary)

(Partition RP1 Values Less than (10000),

Partition RP2 Values Less than(50000),

Partition RP3 Values less than(MAXVALUE));

The above statement will create Global partitioning index for range partition. The Explain plan will take this index.

2.Hash Global Indexes :

There are so many scenarios where user is using the hash partition for improving the performance of the sql query.

Syntax :

Create Index IndexName

On Table_Name(Column_Name)

Global Partition by Hash(column_name)

Partition Number_Of_Partitions;

The hash partition is useful when user does not have any range value in the column of the table.User can create the Hash partition using Hash keyword and giving the number of partition needs to be created.

Real Life Example :

Lets say user needs to add the hash partition index of Employee_name from Employee table,

Create Index HP_Employee_Name

On Employee(Employee_Name)

Global Partition by hash(Employee_Name)

Partition 4;

The above statement will create HP_Employee_Name index which is Hash Partitioned index.

3.Local Partitioned index :

User can specify the local partitioned index by using the Local keyword or by using the local_partitioned_index keyword.My experience is the local partitioned indexes are more useful than global partitioned indexes.User needs to create these indexes carefully.

Syntax :

Create index IndexName on table_name(Column_name) Local;

Just local keyword denotes the local partitioned indexes.

Real Life Example :

Lets say user needs to create index on Employee_name column and Employee table is hash partitioned,

Create index HP_Employee_name

on Employee(Employee_name) Local;

The above statement will create the local partitioned index on Employee table.

4.Index with Compression :

Sometimes user facing the issues of memory space due to huge data.User can compress the index using compress keyword in the syntax of the index.

Syntax :

Create index indexname on table_name(column_name) Compress;

Real life Example :

Lets say user needs to created the Index with compression for Employee_ID Column for composite index with Employee_Id and Employee_name,

Create Index CI_Employee_name On Employee(Employee_id,Employee_name ) COMPRESS 1;

The above statement will compress repeated occurances of Employee_id column.

5.Index With NOLOG AND NO SORT :

Sometimes user needs to create index with NOLOH and NO SORT as the columns are already sorted.Here the primary condition is the table column should be already sorted.

Syntax :

Create Index Indexname

On Table_name (Column_name)

NOLOGGING

NOSORT;

Real Life Example :

If Employee_No Column is already sorted and user needs to create index on Employee table with NO Logs and NO sort,

Create Index NL_Employee_No

On Employee(Employee_No)

NOSORT

NOLOGGING;

The above statement will create index with NO Log and Sort.

6.Function Based Index :

I have already written one article specifically for functional  based indexes before. would like to give One of functional based n this section.

Syntax :

Create index Index_name

On Table_name (Function_with _column_name);

Real Life Example :

If  User wants to create index on  Upper values of Employee_ Name column of Employee table

Create Index FI_Ename On Employee(Upper(Employee_Name));

The above statement will create the index on Employee name with Upper function.

These are some important ways to create index oracle with some real life examples. If you like this article on Create index oracle or if you have issues or concerns with the same kindly comment in to comments section.