Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: tuning an index build

RE: tuning an index build

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 20 Jul 2007 14:28:55 -0400
Message-ID: <005f01c7cafb$d5770e60$1100a8c0@rsiz.com>


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
Received on Fri Jul 20 2007 - 13:28:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US