Index in Oracle SQL and Types of indexes
An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.
Uses of indexes
When an index exists on one or more columns of a table, the database can in some cases retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O. If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, a query of location 2700 in the unindexed hr.departments table requires the database to search every row in every block. This approach does not scale well as data volumes increase.
In general, consider creating an index on a column in any of the following situations:
- The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.
- A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.
- A unique key constraint will be placed on the table and you want to manually specify the index and all index options.
Types of Indexes
B*-Tree indexes—This is Oracle’s default index type. It is a highly flexible index with well-understood performance characteristics.
Bitmap indexes—These are commonly used in data warehouses; they provide a solution for low-cardinality columns and provide efficient index merges. Locking implications normally rule them out for OLTP, however.
Bitmap join indexes—These are bitmap indexes that are constructed from a join of two tables. Bitmap join indexes are often used in star schemas to join fact and dimension tables.
Index Organized Table (IOT)—An IOT is a table structured as a B*-Tree index. The leaf blocks of the index structure contain the table’s data. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.
Hash clusters—In a hash cluster, the location of a row within the cluster is determined by a hash function of hash key columns. This enables a row to be rapidly located by the hash key without an index.
Index cluster—In an index cluster, multiple tables are stored in the cluster based on the value of a common cluster key. Rows with the same key are stored close together and might be considered “pre-joined.”
Nested tables—In a nested table a column contains an object type that effectively embeds detail rows within the master row. Nested tables can provide similar benefits to an index cluster.
The B*-Tree index has a hierarchical tree structure. At the top of the tree is the header block. This block contains pointers to the appropriate branch block for a given range of key values. The branch block will usually point to the appropriate leaf block for a more specific range or, for a larger index, point to another branch block. The leaf block contains a list of key values and pointers (ROWIDS) to the appropriate rows in the table.
B*-Tree indexes have the following advantages
1. Because each leaf node is at the same depth, performance is predictable. Every row in the table requires the same number of index reads to locate.
2. B*-Trees offer good performance for large tables because the depth is almost never greater than four (one header block, two levels of branch blocks, and one level of leaf block). In fact, because the header block will almost always be already loaded in memory, and branch blocks usually loaded in memory, the actual number of physical disk reads is usually only one or two.
3. The B*-Tree index supports range queries and exact lookups. This is possible because each leaf block is linked to the previous and next leaf block.
CREATE INDEX emp_name_ix ON employees (last_name ,first_name ) ;
Words from btechpath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles