Hey, Mark

Thank you for your post. I have not compared the timing the way you suggest
because I don't have a "production
like" environment and I don't want to add extra load to the production
server at this time. What I do is I watch the v$sort_usage view and monitor
the size of the sort segments used by a specific user id. My reasoning is
that as long as the total size of the temp segments is growing, there is
still a fecth going on. When the size stops growing
the sort and merge start. I know that the specific ID i am watching does
not run any other commands besides the index
build. One thing that I am seeing is however that the size of the temp
segments is significantly larger than the size
of the index (3G versus 400M) an I don't know what is the reason for that

I'll need to see if building the indices concurrently will help. I did not
test that

thank you

Gene Gurevich



                                                 
        "Mark W. Farnham"                              
        <mwf@(protected)>                                
                                               To
        07/20/2007 01:28       <genegurevich@(protected)>,    
        PM                <oracle-l@(protected)>        
                                               cc
                                                 
                                            Subject
                         RE: tuning an index build      
                                                 
                                                 
                                                 
                                                 
                                                 
                                                 




Gene:

Have you already explored whether the time is in the fetching of data from
the table as opposed to the chunk sorting and match merging?

For a given index build, if you select the requisite columns for a given
index and time that, you should be able to quickly sort that out.

I thought you mentioned that one of your indexes ended up only 84M or
something. The maximum merge pool for that should have been at most 168M
without parallel query side effects of reassembly, so I guess I would
compare the scan time with parallel current versus parallel 1. You might be
better off building two or more indexes on the same table in parallel since
you should benefit from the blocks you need being in the cache rather than
trying to build a single index as a parallel operation. Your mileage may
vary.

If you've got (uncorrectible) multiblock rows such that columns in a given
index are routinely in different blocks of the row, then it may be possible
to speed things up enormously by re-ordering the definition of columns in
the table so they are in the same block. I've never benchmarked this, so
I'm
speculating. Most designs end up with the indexed columns nearly adjacent
as
a side effect of the way people think, but if Oracle is smart enough to
know
it doesn't need to reference the other blocks the savings could be huge in
the case where the columns are far apart in the definition, as when a
column
is scabbed on to a design as an afterthought.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce@(protected)]
On Behalf Of genegurevich@(protected)
Sent: Friday, July 20, 2007 11:34 AM
To: oracle-l@(protected)
Subject: Re: tunning an index build

<snip>







--
http://www.freelists.org/webpage/oracle-l