Re: Faster index creation

From: Mark D Powell <>
Date: Wed, 14 May 2008 10:33:24 -0700 (PDT)
Message-ID: <>

On May 14, 11:27 am, Mladen Gogala <> wrote:
> On Wed, 14 May 2008 08:02:03 -0700, Tom wrote:
> > 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.
> > 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.
> > I'm hoping that someone else has better ideas or that there is just a
> > simpler way.
> > . . . Tom
> First of all, if you are to employ "parallel" option, there will be
> no caching. You will have quite a few segment checkpoints, though.
> My advice would be to clench your teeth and take the 2 scans like a
> man. A DBA got to do what a DBA got to do. Increase PGA_AGGREGATE_TARGET,
> create indexes with "nologging" and byte the bullet.
> --

Do not forget that If you run two index creates on the same table at one time you will need to have enough temporary tablespace allocated to handle both index builds and the two tasks will compete for IO capacity for both the reading of the table and the use of sort plus potentially the index target tablespace.

I would set work area management policy to manual, set the largest practical sort_area_size possible and just run one index build at a time.

I do not have experience using the parallel parameter on the index build but I would hesitate to let Oracle use its calculated value for the reasons Mladen mentioned. I might try 2 or 4 depending on how many cpu the server has and how many physical disks temp and the table are spread over. Sometimes less is better.

HTH -- Mark D Powell -- Received on Wed May 14 2008 - 12:33:24 CDT

Original text of this message