From:Steve Adams
Date:21-Feb-2001 21:50
Subject:   Index freelists?

Yes, contention for index leaf and branch blocks is always due to hot key ranges. A special case of this is when monotonically increasing key values are inserted intensively - this is called the "right-hand index" problem. Having multiple freelists cannot affect contention for blocks in a hot key range, because all the contending activity is constrained to hot blocks by virtue of the key values.

If you have intensive inserts but no hot key ranges, and if distinct block split operations each require a free block simultaneously, then having multiple freelists will not help you either. The processes will contend for the freelist header block (normally the segment header) regardless of whether they are using the same freelist or not. The way the address this problem is to partition the index. Using multiple freelists does not work, and using multiple freelist groups is messy.

There are scripts on your site for calculating the freelists on tables, but am I wrong that it has no sense to set freelists on the indexes?