Types of Indexes

Oracle Database provides several indexing schemes, which provide complementary performance functionality.

itil certification training courses malaysia

B-tree indexes are the standard index type. They are excellent for highly selective indexes (few rows correspond to each index entry) and primary key indexes. Used as concatenated indexes, a B-tree index can retrieve data sorted by the indexed columns. B-tree indexes have the subtypes shown in the following table.

istqb software testing certification training courses malaysia

Table 3-1 B-Tree Index Subtypes

B-Tree Index Subtype	Description	To Learn More
Index-organized tables	An index-organized table differs from a heap-organized because the data is itself the index.	“Overview of Index-Organized Tables”
Reverse key indexes	In this type of index, the bytes of the index key are reversed, for example, 103 is stored as 301. The reversal of bytes spreads out inserts into the index over many blocks.	“Reverse Key Indexes”
Descending indexes	This type of index stores data on a particular column or columns in descending order.	“Ascending and Descending Indexes”
B-tree cluster indexes	This type of index stores data on a particular column or columns in descending order.	“Ascending and Descending Indexes”

The following table shows types of indexes that do not use a B-tree structure.

iso iec 20000 certification training courses malaysia

Table 3-2 Indexes Not Using a B-Tree Structure

Type	Description	To Learn More
Bitmap and bitmap join indexes	In a bitmap index, an index entry uses a bitmap to point to multiple rows. In contrast, a B-tree index entry points to a single row. A bitmap join index is a bitmap index for the join of two or more tables.	“Overview of Bitmap Indexes”
Function-based indexes	This type of index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression. B-tree or bitmap indexes can be function-based.	“Overview of Function-Based Indexes”
Application domain indexes	A user creates this type of index for data in an application-specific domain. The physical index need not use a traditional index structure and can be stored either in the Oracle database as tables or externally as a file.	“Overview of Application Domain Indexes”

isaca certification training courses malaysia