Re: Faster index creation

"Tom" <tzeblisky_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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html