Re: Faster index creation
Date: Wed, 14 May 2008 12:47:42 -0700 (PDT)
On May 14, 2:43 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Tom" <tzebli..._at_autooneins.com> 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.
> Jonathan Lewishttp://jonathanlewis.wordpress.com
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- 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