Re: Faster index creation

From: joel garry <>
Date: Wed, 14 May 2008 14:34:10 -0700 (PDT)
Message-ID: <>

On May 14, 12:47pm, Tom <> wrote:
> On May 14, 2:43pm, "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 quoted text -
> > - Show quoted text -
> Thank you all!! It is always humbling to realize how little I know.

Even more so when you've known and forgotten and known again and the details change...

> 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

Please always state version, and state it including patch set, ie, I wrote a mini-faq for newbies here:

> What is the maximum OS IO size that is supported? (e.g. 1 MB) - I
> don't know

That can usually be discovered through research online and local. Often, it's easiest to ask the admin. A quick google found (old, but shows where to look). There's more detail around, but that's your job... :-) (and especially if the admin is wrong).

> 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.

System stats can make a difference. Hey, Jonathan wrote an article about it: - so it's on by default on yours.

> 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.

You would look for parameters with name like db_nK_cache_size where nK is a number different than your db block size. From the sql prompt: SQL> show parameter cache_si

You normally don't want to use different block size tablespaces unless you are using the transportable tablespace feature. Beware of myths/ misapprehensions floating about regarding these settings, including performance with indices.

> 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!

There are discursions and discussions about I/O configuration in linux floating about, including in this group. Most Oracle tuning methods seem to assume that is already done right, but I wouldn't assume that, especially if it can mean an order of magnitude difference in how long it takes what you are trying to do. Specific OS versions and hardware can make a difference too, so let people know.

See for the intro to the one-pass that Jonathan mentioned, and google for more he has written on it (including on his site). Since this is an exceptional case for you, you may want special settings just for it.


-- is bogus.  "Would you like law enforcement decisions  a
decision as to what force was necessary ... to be made by a medieval
philosopher? " - San Diego State University President Stephen Weber.
Well, if the alternative is people who are so out of it they don't
know when they decided they should tell the boss they called in the
DEA, maybe so.
Received on Wed May 14 2008 - 16:34:10 CDT

Original text of this message