Thanks, That was interesting. I might research it myself a bit more. For examples a int primary key has 2 billion values yet most tables will be at most a few million. Thanks!
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Ted Roche Sent: Wednesday, 17 December 2008 12:16 PM To: [email protected] Subject: Re: [NF] Who determines the type of app your making? On Tue, Dec 16, 2008 at 5:30 PM, Geoff Flight <[email protected]> wrote: > Does random distribution truly speed up an index? > Under optimal conditions, and fairly heavy use, yes. Simplifying, an index pre-allocates a number of blocks of space and spreads out the index values across the blocks with some spare space (the "fill factor") left open for future inserts. As a block gets full, the block has to be split into two or more blocks with fill factor space and the former leaf node rewritten with pointers to the new blocks, a fairly intensive I/O operation, especially under load. (And this is a simplification; most databases do a more sophisticated rebalancing.) If all of your index keys started with "A" for example, the first few blocks would keep splitting and splitting. But if the values are evenly distributed over the range, each block fills at the same rate, and splits are much less frequent. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
