How to Rebuild an Index in Oracle 12c

By: Bob Bryla, Kevin Loney


Oracle provides a fast index rebuild capability that allows you to re-create an index without having to drop the existing index. The currently available index is used as the data source for the index, instead of the table’s being used as the data source. During the index rebuild, you can change its STORAGE parameters and TABLESPACE assignment.

In the following example, the BA_PK index is rebuilt (via the REBUILD clause). Its storage parameters are changed to use an initial extent size of 8MB and a next extent size of 4MB in the BA_INDEXES tablespace.

p0337-03

NOTE

When the BA_PK index is rebuilt, there must be enough space for both the old index and the new index to exist simultaneously. After the new index has been created, the old index will be dropped.When you create an index that is based on previously indexed columns, Oracle may be able to use the existing indexes as data sources for the new index. The Oracle Optimizer may use parts of existing composite indexes as needed for queries, so you may not need to create many indexes to support the most common queries.

You can rebuild indexes while they are being accessed via the REBUILD ONLINE clause of the ALTER INDEX command.

Comments

  1. Hi sir,
    i want to display the output like
    id region output
    1 north 1 0 0 0
    2 south 0 1 0 0
    3 east 0 0 1 0
    4 west 0 0 0 1

    by using the bitmap index and give the syntax for this problem.

    • Ravi,
      I’m not sure of your question? Do you want to display the results of your bitmap? If so, you can always dump the index block. There are several ways you can do this which I show an example of how to dump an index block in my ‘Everything You Need to know about Oracle 12.2 Indexes’ presentation. You can download a copy here: https://thwack.solarwinds.com/pages/dpa-resources

Leave a Reply