DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, July 26, 2016

Creating Multiple Indexes on the Same Set of Columns

You can create multiple indexes on the same set of columns when the indexes are different in some way.


To create multiple indexes on the same set of columns, the following prerequisites must be met:
  • The prerequisites for required privileges in "Creating Indexes".
  • Only one index on the same set of columns can be visible at any point in time.
    If you are creating a visible index, then any existing indexes on the set of columns must be invisible.
Alternatively, you can create an invisible index on the set of columns

For example, the following steps create a B-tree index and a bitmap index on the same set of columns in the oe.orders table:

Create a B-tree index on the customer_id and sales_rep_id columns in the oe.orders table:

CREATE INDEX oe.ord_customer_ix1    ON oe.orders (customer_id, sales_rep_id);


The oe.ord_customer_ix1 index is visible by default.



Alter the index created in Step 1 to make it invisible:

ALTER INDEX oe.ord_customer_ix1 INVISIBLE;


Alternatively, you can add the INVISIBLE clause in Step 1 to avoid this step.

Create a bitmap index on the customer_id and sales_rep_id columns in the oe.orders table:

CREATE BITMAP INDEX oe.ord_customer_ix2    ON oe.orders (customer_id, sales_rep_id);
The oe.ord_customer_ix2 index is visible by default.



If the oe.ord_customer_ix1 index created in Step 1 is visible, then the CREATE BITMAP INDEX statement in this step returns an error.

No comments: