Indexes in Oracle

Indexes in Oracle

In a relational database, an index is a database structure that is related to a table and is another important schema object other than a table. An index is a helper object that is built on one or more columns of a table to increase the speed of data access in the table.

Another way of representing data at index time, which provides a different order of data than the physical storage of data on disk. It rearranges the physical location of the data so that its value is a list of ordered key values, and each key value is a pointer to the table row, so that it is arranged so that its search becomes more efficient.

If a primary key constraint is defined in the table, and no index exists on the primary key column, Oracle automatically creates one.

The index types commonly used in Oracle are: B-tree index, reverse key index, bitmap index, function-based index, cluster index, global index, and local index.

The syntax for creating an index is as follows:

CREATE unique| Btimap INDEX<schema>.<index_name>On<schema>.<table_name>(<column_name>|<expression>asc| DESC,<column_name>|<expression>asc|    DESC,...) Tablespace<tablespace_name>STORAGE<storage_settings>Logging| Nologging COMPUTE STATISTICS nocompress| COMPRESS<nn>Nosort| REVERSE patition| GLOBAL patition<patition_setting>;

Indexes in Oracle