Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Indexes calculation??

Re: Indexes calculation??

From: Chuck Hamilton <>
Date: Fri, 16 Feb 2001 08:40:00 -0800
Message-ID: <>

 Here's what I do. (These general rules apply to both indexes and tables so they should be of some help to you.)
1. Pctincrease is ALWAYS 0. If they did away with that parameter and defaulted it to 0 I'd be happy. It makes for messy tablespaces with lots of unusable space, and objects with lost of wasted space. 2 Create tablespaces where the next extent of every object in the tablespace is the same. The intitial can be different if you like but so long as it's an exact multiple of next. The easiest way to do this is to assign the extent sizes as the tablespace's defaults, and don't specify extent sizes when creating objects in it. The best way to enforce this practice is to use uniformly sized locally managed tablespaces (only available from 8i on). This prevents you from ending up with unusable free spaces in the tablespace.

3. Decide on a few standard sizes for extents and use these for all objects. Create a tablespace for each extent size you come up with (i.e. date32k, index10m, etc.). I usually pick something like 32k for the diddly little things, 1m, 10m, 50m, and 100m. I decide which objects will use which extent sizes (and therefore which tablespaces they will go into) by how fast they grow. We run a report every night that tells us when an object won't be able to allocate it's next extent. So in order be sure I get at least a few days advance warning, I make sure that objects get extent sizes that will hold them over for at least 3 days. If a table will grow by approx 1m/day, I'll put it into the tablespace with 10m next extents. If it grows by 5m/day I'll put it in the 50 or 100m tablespace.
4. For availability reasons, I put the largest objects (usually those that would fit into the 100m tablespace) into their own separate tablespaces. These are the ones that are most likely going to need to be moved eventually to another filesystem, so when I offline the tablespace to move a file, I only effect one object.
5. For really big objects you should also consider partitioning. When you go to reorg an index or rebuild a table you don't have to makethe whole thing unavailable then, just the one partition you're working on. Try to come up with a partitioning scheme so that the "hottest" data isn't in the partitions you're likely to be reorganizing. For example in an order entry application you might consider partitioning based on the order date. Most of the activity will then be inserts at the end of one partition. The other partitions can be reorged with minimal impact to your users.
6. Always try to equipartition tables and indexes. Global indexes are invalidated if you do any sort of truncate or reorg on a partitioned table requiring the entire index to be rebuilt. Local indexes still need to be rebuilt, but they reduce the impact of such operations to one partition.
    Chuck Hamilton
  Raghu Kota <> wrote:
Hi GurusI have a big fat Indexes, I need to recalculate their storage clauses, What is best approach to calculate next extent, min extents and other stuff like pctfree and pctused. I hope mine is insert based database with 150gigs data, I need to set up pctused @30-40.Any body can threw light on it??ThanksRaghu._________________________________________________________________________Get Your Private, Free E-mail from MSN Hotmail at Please see the official ORACLE-L FAQ: Author: Raghu KotaINET: raghukota@hotmail.comFat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send !
an E-Mail messageto: (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do You Yahoo!? - Get personalized email addresses from Yahoo! Mail Personal Address - only $35 a year! Received on Fri Feb 16 2001 - 10:40:00 CST

Original text of this message