In order to speed up index creation of large tables with millions of rows, you normally put the index in nologging mode and use parallelism degree.
Here's a tip to do it even faster: use the UNUSABLE feature, which puts the
definition of the index in the data dictionary
and also allocates the necessary segments without creating a usable index.
In order to make it usable, of course, you have to REBUILD that index.
The point here is that "ALTER INDEX .... REBUILD" is faster than CREATE, since
REBUILD uses Fast Full Scan (FFS) and CREATE uses Full Table Scan (FTS). This works on Oracle 8i and 9i.
CREATE INDEX cust_idx on customer(id) UNSUABLE;
ALTER INDEX cust_idx NOLOGGING parallel 6;
Reader Feedback
Dave K. writes: There is a critical error in this script. The second line should be:
ALTER INDEX cust_idx REBUILD parallel 6 NOLOGGING;
The keyword "REBUILD" is missing.
Raju A. writes: This will only work on Oracle 9i. You made the statement that "This works on Oracle 8i and 9i." The UNUSABLE option was introduced in Oracle 9i. Overall, the script is good for building large indexes.
The author responds to the comment by Dave K: This is not a critical error -- it's a syntax or typo error, though it is a good catch. My intention was to show the process of speeding up index creation by first
creating an index with UNUSABLE and later to REBUILD that index.
Thanasis B. writes: The UNUSABLE option in the CREATE INDEX statement is not supported on 8i.
The author responds: The UNUSABLE feature works fine in version 8.1.7.0.0 and onwards. It also works in 8.1.6.0. but make sure by checking the status column of user_indexes.
Mike C. writes: If the database is up and active, this method has the potential to cause insert/update/deletes to fail when this index exists in an unusable state, and before it is reorged. While there is a session level command that will tell Oracle to skip unusable indexes for purposes of inserts/updates/deletes, I haven't found an init.ora or global setting that will prevent this issue.
Boris M. writes: The information provided in this tip is simply incorrect.
Your first mis-statement: "UNUSABLE feature, which puts the definition of the index in the data dictionary and also allocates the necessary segments without creating a usable index..." UNUSABLE does create an index, but it does not allocate the necessary segments for the index. I create a 200K-row table, and CREATE INDEX... UNUSABLE operation was instant. Then I checked user_segments for that index: 1 extent/8 blocks had been allocated: select blocks, extents from user_segments where segment_name = 'T_IND1'; BLOCKS EXTENTS 8 1. Obviously, that's not all "necessary segments" for three columns of a 200K index. I rebuilt the index and ran that query again: BLOCKS EXTENTS 1536 27.
However, the main problem with your tip is the sequence of the following two commands: CREATE INDEX ... UNUSABLE; ALTER INDEX ... REBUILD NOLOGGING PARALLEL; is not faster than CREATE INDEX ... NOLOGGING PARALLEL; It's taking the same time (if it's not, send me a test case, because I was not able to reproduce it). The reason for that is that in both cases, index has to be rebuilt via full table scan and there is no way around it. Your statement "REBUILD uses Fast Full Scan (FFS)" is wrong in this context. As soon as you make your index UNUSABLE, it can not be used as a source for index rebuilding. That's just common sense. Unusable index can not be used as a source for any operation - its UNUSABLE! ALTER INDEX ... REBUILD operation will use the index as a source for usable index, and Explain Plan shows Index Fast Full Scan (FFS). But as soon as you make index unusable, Explain Plan gets changed to full table scan (TABLE ACCESS FULL). In this case, it's identical to the plan for CREATE INDEX.
I did a simple test that shows the following results: ALTER INDEX ... REBUILD operation (for usable index) is faster than CREATE INDEX ..., and that's the way it's supposed to be. Index Fast Full Scan (FFS) is faster that full table scan. However, CREATE INDEX ... UNUSABLE and ALTER INDEX ... REBUILD took the same time as CREATE INDEX ..., and that's the way it's supposed to be. Both use full table scan. I used the technique of disabling indexes (making them unusable) many times during massive data load: make indexes unusable -- alter session set skip_unusable_indexes = true -- load data, usually in direct path mode (insert with APPEND hint) -- rebuild indexes in NOLOGGING PARALLEL mode. Obviously, indexes are disabled and not getting updated during data load, which is what we want. So, if you have one million rows before, and you loaded five million rows, then your table has six million. But your indexes still have entries for only one million rows. That means the database has to use table to rebuild indexes. They are simply missing five million entries. In this case, rebuild is the same as create. It has to go to the table and build index from scratch via full table scan (or fast full scan of another index). This technique is very useful but for the reasons absent from your tip: it's safety and convenience.
Safety: you will never lose an index. If the system crashes in the middle of data load, you'll end up with unusable index and you will be notified by the first user who tries to run DML against your table. If you drop an index in order to create it after load, then you may not notice that you lost it until a couple of days or weeks later. Certain queries will start running really slow.
Convenience: ALTER INDEX ... REBUILD is a really simple command. It's very easy to do programmatically for all indexes in unusable state loop and rebuild. On the other hand, create index requires much more information. Obviously you know index name, table name and column names. But what about tablespace name, INITTRANS, MAXTRANS, multiple STORAGE parameters, etc. By putting index in unusable state, you keep all of this information in data dictionary, and after data load you just want to rebuild with the same parameters that existed before: no headache at all. That's why it's so useful.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
