Oracle Index Structure

Types of Indexes

BTree Index Subtype

1. Index Organized tables
2. Reverse Key Indexes
3. Descending Indexes
4. B-tree Cluster Indexes

B-Tree Index

B-trees, short name for balanced trees, are the most common type of database index

A B-tree index has two types of blocks: the branch block for searching, and the leaf block for storing key values. 

Index Access Methods.

Full Index Scan

In a full index scan, the database reads the entire index in order. A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified. A full scan can eliminate sorting because the data is ordered by the index key.

Fast Full Index Scan

A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order.

Index Range Scan

An index range scan is an ordered scan of an index in which one or more leading columns of an index are specified in conditions, and 0, 1, or more values are possible for an index key.

Index Unique Scan

In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key.

Index Skip Scan

An index skip scan uses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes.

Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index. The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate.

Reverse Key Indexes

A reverse key index is a type of B-tree index that physically reverses the bytes of each index key while keeping the column order.

Overview of Bitmap Indexes

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

Bitmap Join Indexes

A bitmap join index is a bitmap index for the join of two or more tables.

Bitmap Storage Structure

Oracle Database uses a B-tree index structure to store bitmaps for each indexed key.

Function-Based Indexes

A function-based index computes the value of a function or expression involving one or more columns and stores it in an index. A function-based index can be either a B-tree or a bitmap index.

Index-Organized Tables

An index-organized table is a table stored in a variation of a B-tree index structure. In contrast, a heap-organized table inserts rows where they fit.

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.

Cursor_Sharing

Cursor Sharing	Yes/No	What affects	Bind Peeking
exact	yes	No literal replacement, no change in qry
force	Yes	Yes literal replacement	yes bind peeking
force	No
Similar	Yes	No literal replacement, no change in qry
Similar	No	Yes literal replacement

Application developers should use bind variables in their programs to maximize the reuse

of their previously parsed commands in the shared SQL area. If bind variables are not in use, you

may see many very similar statements in the library cache: queries that differ only in the literal

value in the WHERE clause.

Statements that are identical except for their literal value components are called similar

statements. Similar statements can reuse previously parsed commands in the shared SQL area if

the CURSOR_SHARING initialization parameter is set to FORCE. Use EXACT (the default) if the

SQL statements must match exactly including all literals.