Databases Reference
In-Depth Information
10. Inspect the index status in the data dictionary:
SELECT
TABLE_NAME, INDEX_NAME, STATUS
FROM USER_INDEXES
WHERE TABLE_NAME = ‹BIG_CUSTOMERS›
ORDER BY 1,2,3
11. Drop the table (and the index) to clean the objects created by this recipe (we will
create the same table in the next recipe):
DROP TABLE BIG_CUSTOMERS;
How it works...
In this recipe, we have rebuilt an index in both online and offline mode.
In the first steps, we created a table with an index on it. We then used two simultaneous
sessions to verify that an online index rebuild allows us to insert, update, and delete records
in the table on which our index rebuilding is based. If we don't specify the
ONLINE
clause, the
DML operations happen only at the end of the rebuild process.
This behavior is clear when we look at the screenshots with the timings. Looking at the
picture from Session A, we can see that rebuilding an index is faster than creating the
same index—and we will understand why later—while rebuilding an index online is slower
than rebuilding the same index without this option in place.
The drawback of rebuilding an index offline is that while the rebuild is happening, DML
commands are locked until the completion of the other operation. This can be verified
looking at the screenshot in which SESSION B results are shown and comparing it with
the screenshot for SESSION A. The second execution of the query—launched manually
in another SQL*Plus session after the offline index rebuild—terminates
after
the rebuild
operation, while when we are executing the
ONLINE REBUILD
the insert in SESSION B
terminates before the rebuild operation.
There's more...
An index has to be rebuilt when its status is in an
UNUSABLE/INVALID
state. To inspect
the status of an index we can use the statement in step 8.
An index becomes
INVALID
for various reasons, such as a direct path load operation and
an
ALTER TABLE MOVE
command. Every time there is a change in the ROWIDs of a table,
the indexes on that table have to be rebuilt.
