Types of indexes in Oracle

10 pts.
Tags:
Oracle
Hi, what are the different types of indexes in Oracle?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Types of Indexes:

Oracle Database provides several indexing schemes, which provide complementary performance functionality. The indexes can be categorized as follows:

    B-tree indexes

    These indexes are the standard index type. They are excellent for primary key and highly-selective indexes. Used as concatenated indexes, B-tree indexes can retrieve data sorted by the indexed columns. B-tree indexes have the following subtypes:

        Index-organized tables

        An index-organized table differs from a heap-organized because the data is itself the index. See “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. See “Reverse Key Indexes”.

        Descending indexes

        This type of index stores data on a particular column or columns in descending order. See “Ascending and Descending Indexes”.

        B-tree cluster indexes

        This type of index is used to index a table cluster key. Instead of pointing to a row, the key points to the block that contains rows related to the cluster key. See “Overview of Indexed Clusters”.

    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. See “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. See “Function-Based Indexes”.

    Application domain indexes

    This type of index is created by a user 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. See “Application Domain Indexes”.”

Source: Indexes and Index-Organized Tables

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • RACLAPP
    While not technically a "type" of index in Oracle.  Whether an index is partitioned or not can have a huge impact as well.  Many of us treat it in the sense of a sub-type.
    110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: