Re: Faster index creation

From: Tom <>
Date: Wed, 14 May 2008 12:47:42 -0700 (PDT)
Message-ID: <>

On May 14, 2:43 pm, "Jonathan Lewis" <> wrote:
> "Tom" <> wrote in message
> >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.
> > 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
> Which version ?
> If it's older than 10.2 then you'll be using the old sort option,
> which is very CPU intensive and CPU may (silly though it sounds)
> be more important than the simple cost of a large disc read.
> You've got an 8Gb SGA, so I assume this means a 64bit machine.
> If you allow about 36 bytes overhead per row plus the sum of
> the average columns sizes in the index plus 2 x number of columns.
> That will give you an indication of the size of the pga memory
> you will need to complete the sort in memory.
> If you can't do it in memory, the amount of I/O involved will be
> the same regardless if you can do a one-pass sort - and the smaller
> the memory usage is, the less CPU you use ... and that can make a
> big difference to elapsed time.
> --
> Regards
> Jonathan Lewis
> Author: Cost Based Oracle: Fundamentals
> The Co-operative Oracle Users' FAQ Hide quoted text -
> - Show quoted text -

Thank you all!! It is always humbling to realize how little I know.

To answer a few of your questions:

Is the table partitioned? - No
What degree of parallelism is set for the table? - none What version of the Oracle database server software? - 10.2 What is the maximum OS IO size that is supported? (e.g. 1 MB) - I don't know
What is the db_file_multiblock_read_count set to? (e.g. 16) - it is 16 Have you gathered system stats under workload? - I don't think so but the workload should be minimal there will be no end-users. The db_block_size would also be of interest, as would the tablespace block size if it differs from the db_block_size. - db block size is 8k I'm not sure what the tablespace block size is.

To give a little more background information - we are moving a DB from Sun to Linux. In the process, we are dropping and re-creating the indexes. Most of them are fast - this particular one is not. I do not own the DB but was putting in my two cents to try to help speed things up. I now have a good amount of research to do - but that's OK!

Thank you all again!

. . . Tom Received on Wed May 14 2008 - 14:47:42 CDT

Original text of this message