Re: Faster index creation

On May 14, 8:02 am, Tom <tzebli..._at_autooneins.com> wrote:
> I need to create 2 indexes on a 107 GB table.  The indexes have no
> columns in common.  I have a 8 Gig SGA.
>
> I am not looking forward to 2 tablescans so I was thinking of running
> both create index statements at the same time from different sqlplus
> sessions and hoping that  the data for the slower statement is in
> cache for the faster statement.

I think you might have lock issues doing this. See http://richardfoote.wordpress.com/2008/02/11/index-create-and-rebuild-locking-improvements-in-11g-ch-ch-ch-changes/

Remember, DDL is not transaction oriented, so issues commits before and after it runs, and so has to rely on locking to make sense. Also, full table scans put the blocks read on the tail end of the LRU list, so they age out first (see memory use section of performance tuning guide).

>
> Or as an alternative, I was thinking of first creating an index which
> was a superset of the columns in the other 2 indexes and then creating
> the other indexes.  They will just use the first index instead of
> doing table scans.  When they are done, drop the first index.

This sounds good, though the docs say it _may_ use the index to build the others. I would expect it to, but... test.

>
> I'm hoping that someone else has better ideas or that there is just a
> simpler way.

Listen to Mladen.

jg

--
@home.com is bogus.
Half-a-doc: http://www.oracle.com/technology/software/products/database/oracle10g/htdocs