Oracle Indexes

Types of indexes in oracle with example

There are 6 different types of indexes in oracle

1) B-Tree

2) Compressed B-Tree

3)  Bitmap

4) Function-Based

5) Reverse Key (RKI)

6) Index organized table (IOT).

Lets find out each of them in detail and how to create index in oracle for each of these types

B – Tree Index:

• B-Tree Indexes (balanced tree) are the most common type of index.
• B-Tree index stored the ROWID and the index key value in a tree structure.
• When creating an index, a ROOT block is created, then BRANCH blocks are created and finally LEAF blocks.
• Each branch holds the range of data its leaf blocks hold, and each root holds the range of data its branches hold:
• B-Tree indexes are most useful on columns that appear in the where clause (SELECT … WHERE EMPNO=1).
• The Oracle server, keeps the tree balanced by splitting index blocks, when new data is inserted to the table.
• Whenever a DML statement is performed on the index’s table, index activity occurs, making the index to grow (add leaf and branches).

Advantages

• All leaf blocks of the tree are at the same depth.
• B-tree indexes automatically stay balanced.
• All blocks of the B-tree are three-quarters full on the average.
• B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
• Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
• B-tree performance is good for both small and large tables, and does not degrade as the size of a table grows.

CREATE <UNIQUE|NON UNIQUE>  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

TABLESPACE <tablespace_name>;

Example

Create index   scott.exp_idx   on table scott.example(  name)

Tablespace TOOLS;

What is compressed B-tree Indexes

Compressed B-Tree Indexes are built on large tables, in a data warehouse environment. In this type of index, duplicate occurrences of the same value are eliminated, thus reducing the amount of storage space, the index requires. In a compressed B-Tree index, for each key value, a list of ROWIDs are kept:

Specifying the COMPRESS keyword when creating an index (CREATE INDEX … COMPRESS) will create a compressed B-Tree index. A regular B-Tree index can be rebuilt using the COMPRESS keyword to compress it.

CREATE <UNIQUE|NON UNIQUE>  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

PCTFREE <integer>

TABLESPACE <tablespace_name>

Compress  <column number>

What is Bitmap Indexes

Bitmap Indexes are most appropriate on low distinct cardinality data (as opposed to B-Tree indexes).

This type of index, creates a binary map of all index values, and store that map in the index blocks, this means that the index will require less space than B-Tree index.

Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient

The Bitmap index is useful on large columns with low-DML activity like marital status (M/S) or gender (M/F).

Bitmap Index structure contain a map of bits which indicate the value in the column, for example, for the GENDER column, the index block will hold the starting ROWID, the ending ROWID and the bit map:

Bitmap indexes are very useful when created on columns with low cardinality, used with the AND & OR operator in the query condition:

CREATE BITMAP  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

PCTFREE <integer>

TABLESPACE <tablespace_name>

Example

CREATE BITMAP INDEX ON emp_data(gender);

SELECT COUNT(*) FROM emp_data

WHERE GENDER=’M”;

Advantages Of Bitmap Indexes

• Reduced response time for large classes of queries
• A substantial reduction of space usage compared to other indexing techniques
• Dramatic performance gains even on very low end hardware
• Very efficient parallel DML and loads

Function Based Indexes

Function-Based Indexes are indexes created on columns that a function is usually applied on.

When using a function on an indexed column, the index is ignored, therefore a function-based index is very useful for these operations.

CREATE INDEX <index_name>

ON <table_name> [ Function(<column_name>,<column_name.)]

TABLESPACE <tablespace_name>;

Example

CREATE INDEX EMP_IDX on EMP(UPPER(ENAME));

SELECT *

FROM Emp

WHERE UPPER(Ename) like ‘JOHN`;

What is Reverse-Key Indexes

They  are special types of B-Tree indexes and are very useful when created on columns contain sequential numbers.

When using a regular B-Tree, the index will grow to have many branches and perhaps several levels, thus causing performance degradation, the RKI solve the problem by reversing the bytes of each column key and indexing the new data.

This method distributes the data evenly in the index. Creating a RKI is done using the REVERSE keyword: CREATE INDEX … ON … REVERSE;

CREATE INDEX <index_name>

ON <table_name> (<column_name>)

TABLESPACE <tablespace_name>

REVERSE;

Example

CREATE INDEX emp_idx i ON emp_table (firstname,lastname) REVERSE;

What is Index Organized Tables (IOT) –

When we are  using B-Tree, Bitmap and Reverse key  indexes are used for tables that store data in an unordered fashion (Heap Tables).

These indexes contain the location of the ROWID of required table row, thus allowing direct access to row data

An index-organized table differs from an ordinary table because the data for the table is held in its associated index. Changes to the table data, such as adding new rows, updating rows, or deleting rows, result in updating the index.

The index-organized table is like an ordinary table with an index on one or more of its columns, but instead of maintaining two separate storage for the table and the B-tree index, the database system maintains only a single B-tree index which contains both the encoded key value and the associated column values for the corresponding row. Rather than having a row’s rowid as the second element of the index entry, the actual data row is stored in the B-tree index. The data rows are built on the primary key for the table, and each B-tree index entry contains <primary_key_value, non_primary_key_column_values>. Index-organized tables are suitable for accessing data by the primary key or any key that is a valid prefix of the primary key.

There is no duplication of key values because only non-key column values are stored with the key. You can build secondary indexes to provide efficient access by other columns. Applications manipulate the index-organized table just like an ordinary table, using SQL statements. However, the database system performs all operations by manipulating the corresponding B-tree index.

Features of Index organized  table

Primary key uniquely identifies a row; primary key must be specified

Primary key based access

Logical rowid in ROWID pseudocolumn allows building secondary indexes

UNIQUE constraint not allowed but triggers are allowed

Cannot be stored in a cluster

Can contain LOB columns but not LONG columns

Distribution and replication not supported

There are 2 benefits of using IOT: 1. table rows are indexes, access to table is done using its primary key, the row is returned quickly from IOT than heap tables. 2.

CREATE TABLE command:

CREATE TABLE …

ORGANIZATION INDEX TABLESPACE … (specify this is an IOT)

PCTTHRESHOLD … (specify % of block to hold in order to store row data, valid 0-50 (default 50))

INCLUDING … (specify which column to break a row when row length exceeds PCTTHRESHOLD) OVERFLOW TABLESPACE … (specify the tablespace where the second part of the row will be stored) MAPPING TABLE; (cause creation of a mapping table, needed when creating Bitmap index on IOT)

The Mapping Table maps the index’s physical ROWIDs to logical ROWIDs in the IOT. IOT use logical ROWIDs to manage table access by index because physical ROWIDs are changed whenever data is added to or removed from the table. In order to distinct the IOT from other indexes, query the USER_INDEXES view using the pct_direct_access column. Only IOT will have a non-NULL value for this column.

Application Domain Indexes

Oracle provides extensible indexing to accommodate indexes on complex data types such as documents, spatial data, images, and video clips and to make use of specialized indexing techniques.

With extensible indexing, you can encapsulate application-specific index management routines as an indextype schema object and define a domain index (an application-specific index) on table columns or attributes of an object type. Extensible indexing also provides efficient processing of application-specific operators.

The application software, called the cartridge, controls the structure and content of a domain index. The Oracle server interacts with the application to build, maintain, and search the domain index. The index structure itself can be stored in the Oracle database as an index-organized table or externally as a file.

Using Domain Indexes

Domain indexes are built using the indexing logic supplied by a user-defined indextype. An indextype provides an efficient mechanism to access data that satisfy certain operator predicates. Typically, the user-defined indextype is part of an Oracle option, like the Spatial option.

For example, the SpatialIndextype allows efficient search and retrieval of spatial data that overlap a given bounding box.

The cartridge determines the parameters you can specify in creating and maintaining the domain index. Similarly, the performance and storage characteristics of the domain index are presented in the specific cartridge documentation.

So far we have covered different types of indexes in oracle with example,lets now check how to alter/drop/recreate them

How to recreate the Indexes/rebuild index in oracle

We can Use the ALTER INDEX … REBUILD statement to reorganize or compact an existing index or to change its storage characteristics

The REBUILD statement uses the existing index as the basis for the new one.

ALTER INDEX … REBUILD is usually faster than dropping and re-creating an index.

It reads all the index blocks using multi block I/O then discards the branch blocks.

A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.

Alter index  <index name>  rebuild ;

Alter index  <index name>  rebuild  tablespace <name>;

How to Write Statements that Avoid Using Indexes

• You can use the NO_INDEX optimizer hint to give the CBO maximum flexibility while disallowing the use of a certain index.
• You can use the FULL hint to force the optimizer to choose a full table scan instead of an index scan.
• You can use the INDEX, INDEX_COMBINE, or AND_EQUAL hints to force the optimizer to use one index or a set of listed indexes instead of another.

How to gather statistics for Indexes

Index statistics are gathered using the ANALYZE INDEX or dbms_stats statement.

Available options are COMPUTE/ESTIMATE STATISTICS or VALIDATE STRUCTURE.

With 10g onwards, when the index is created, compute statistics is done automatically

When using the validate structure, Oracle populates the INDEX_STATS view with statistics related to analyzed index. The statistics contain number of leaf rows & blocks (LF_ROWS, LF_BLKS), number branch rows & blocks (BR_ROWS, BR_BLKS), number of deleted leaf rows (DEL_LF_ROWS), used space (USED_SPACE), number of distinct keys (DISTINCT_KEYS) etc. These statistics can be used to determine if the  index should be rebuild or not

How does Oracle decide about the usage of index?

Oracle automatically decides about whether index should be used by Optimizer engine.

Oracle decides whether to use an index or not depending upon the query.

Oracle can understand whether using an index will improve the performance in the given query. If Oracle thinks using an index will improve performance, it will use the index otherwise it will ignore the index.

Let us   understand by this example

We have a table emp   which contains emp_name, salary,dept_no ,emp_no,date_of_joining and we have an index on emp_name

Query 1

select * from emp  where emp_name = 'John';

The above query will use the index as we are trying to get information about a emp based on the name.

Query 2

select * from emp;

The above query will not use index as we are trying to find all the rows in the table and we don’t have  where clause in the query

Query 3

select * from emp where dept_no =5;

The above query will not use index as the where clause does not select the column  which has index

Query 4

select * from emp where substr(emp_name,1,4) =’XYZW’;

The above query will not use index as the where clause uses the function on the column and we don’t have functional index on emp_name

How to create or rebuild the index Online?

Oracle used to lock the table on which index is being created throughout creation process  in older versions. This makes table unavailable for data manipulation during the creation of index.

Now  with 8i , Oracle introduced online rebuilding of index where Oracle doesn’t lock the table on which index is being built.

Online indexing is provided through the keyword ONLINE.

CREATE <UNIQUE|NON UNIQUE>  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

PCTFREE <integer>

TABLESPACE <tablespace_name>

Online;

Alter index <index name> rebuild online;