HELP! Create Index on Oracle VLDB takes 12+ hours !!

Hey,

I would like to add an index to a table containing some 5+ millions rows. Is there any way to build this index without exclusively locking the table on which the index is building ???

[ if you have the answer to the above question, read no more ]

Its seems that building this index requires the table be locked, even blocking read access such that the web site that runs off the database effectively gets shut down during the "create index", for both reads and writes.

I tried building the index, but it took 12+ hours during which the site was down, and still wasn't finished. I had to kill that process, and still have no index. :(

How are we DBA's supposed to build a large index for a live web site, if they exclusively lock the tables ? I'm not looking for "parallel" or "partition" option solutions. If these are the solutions, I have other less expensive / non-enterprise work arounds to the problem.

I've tried some tuning for "create index" including UNRECOVERABLE option and SORT_AREA_SIZE to 10M. I've read about tweaking the temp table space, but I don't think these performance tweaks are the answer because my db is running 100% CPU and only 5-10% DISK when doing the build, so it seems to be mostly sorting.

I just hope there is some way to create an index on a "hot" table. Maybe there is an ARCHIVE LOG mode for index creation? I know a stretch.

Thank you very much ahead of time.

• Joshua

  ---

  Joshua Chamas Chamas Enterprises Inc. NODEWORKS - web link monitoring Long Beach, CA USA 1-562-432-2469 http://www.nodeworks.com http://www.chamas.com