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

Hi Joshua,

  To answer your first question, I guess you can build an index without locking the table in Oracle 8i. I haven't tried this out yet.

Regards,
Denny

In article <37856997.890E4C35_at_chamas.com>,   Joshua Chamas <joshua_at_chamas.com> wrote:
> 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
>

--
Denny Koovakattu
denny_at_vitalsol.com
http://vitalsol.com/

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 09 1999 - 16:42:03 CDT