Re: Faster index creation

On May 14, 2:43 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Tom" <tzebli..._at_autooneins.com> wrote in message
>
> news:889e1efa-7a65-4df5-b043-590a1c1e690b_at_k13g2000hse.googlegroups.com...
>
>
>
>
>
> >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 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