RE: tuning an index build

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_at_rsiz.com>                                                
                                                                        To 
             07/20/2007 01:28          <genegurevich_at_discover.com>,        
             PM                        <oracle-l_at_freelists.org>            
                                                                        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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Friday, July 20, 2007 11:34 AM
To: oracle-l_at_freelists.org
Subject: Re: tunning an index build

<snip>

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