Oracle Index

What is Index in Oracle

what_is_index_in_oracle

An index is a performance-tuning method of allowing faster retrieval of records. An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Oracle Database supports several types of index:

  • Normal indexes. (By default, Oracle Database creates B-tree indexes.)
  • Bitmap indexes, which store rowids associated with a key value as a bitmap. See details here
  • Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table
  • Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
  • Domain indexes, which are instances of an application-specific index of type indextype

Prerequisites

To create an index in your own schema, one of the following conditions must be true:

  • The table or cluster to be indexed must be in your own schema.
  • You must have the INDEX object privilege on the table to be indexed.
  • You must have the CREATE ANY INDEX system privilege.

To create an index in another schema, you must have the CREATE ANY INDEX system privilege. Also, the owner of the schema to contain the index must have either the UNLIMITED TABLESPACE system privilege or space quota on the tablespaces to contain the index or index partitions.

To create a domain index in your own schema, in addition to the prerequisites for creating a conventional index, you must also have the EXECUTE object privilege on the indextype. If you are creating a domain index in another user's schema, then the index owner also must have the EXECUTE object privilege on the indextype and its underlying implementation type. Before creating a domain index, you should first define the indextype.

To create a function-based index, in addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC. Also, you must have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Create an Index

The syntax for creating an index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n)
  [ COMPUTE STATISTICS ];
UNIQUE

It indicates that the combination of values in the indexed columns must be unique.

index_name

The name to assign to the index.

table_name

The name of the table in which to create the index.

column1, column2, ... column_n

The columns to use in the index.

COMPUTE STATISTICS

It tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example

CREATE INDEX supplier_idx
  ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city)
  COMPUTE STATISTICS;

Create a Function-Based Index

The syntax for creating a function-based index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (function1, function2, ... function_n)
  [ COMPUTE STATISTICS ];
UNIQUE

It indicates that the combination of values in the indexed columns must be unique.

index_name

The name to assign to the index.

table_name

The name of the table in which to create the index.

function1, function2, ... function_n

The functions to use in the index.

COMPUTE STATISTICS

It tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example

Let's look at an example of how to create a function-based index in Oracle/PLSQL.

CREATE INDEX supplier_idx
  ON supplier (UPPER(supplier_name));

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

Rename an Index

The syntax for renaming an index in Oracle/PLSQL is:

ALTER INDEX index_name
  RENAME TO new_index_name;
index_name

The name of the index that you wish to rename.

new_index_name

The new name to assign to the index.

Example

Let's look at an example of how to rename an index in Oracle/PLSQL.

ALTER INDEX supplier_idx
  RENAME TO supplier_index_name;

In this example, we're renaming the index called supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

The syntax for collecting statistics on an index in Oracle/PLSQL is:

ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS;
index_name

The index in which to collect statistics.

Example

Let's look at an example of how to collect statistics for an index in Oracle/PLSQL.

ALTER INDEX supplier_idx
  REBUILD COMPUTE STATISTICS;

In this example, we're collecting statistics for the index called supplier_idx.

Drop an Index

The syntax for dropping an index in Oracle/PLSQL is:

DROP INDEX index_name;
index_name

The name of the index to drop.

Example

Let's look at an example of how to drop an index in Oracle/PLSQL.

DROP INDEX supplier_idx;

In this example, we're dropping an index called supplier_idx.


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.