Oracle Database Administration: The Essential Refe

Indexes

Perhaps no other single feature of Oracle can provide as much performance improvement as the proper use of indexes. While many performance gains will result from tuning SQL statements (see Chapter 8), there are also several configuration guidelines we suggest you follow:

• Create a separate tablespace for indexes, and make certain that the datafiles for this index tablespace are not on the same disk device as any datafiles for tablespaces that contain indexed tables.

• Try to estimate the size of an index and allocate a sufficient INITIAL extent to hold the entire index, unless you are using Parallel Query, in which case you should allocate the total space across as many datafiles as the degree of parallelism for the index.

• If possible, always allocate INITIAL and NEXT extents in multiples of the same size units; for example, allocate in multiples of 512K. This way, extents will be of uniform size and it will be easier to allocate additional extents without fragmenting the tablespace.

• Set PCTINCREASE to in order to prevent runaway extent allocation and to preserve uniform extent sizes.

• Set MAXEXTENTS to UNLIMITED. This guideline will prevent your running out of extents, since multiple extents have little performance impact in and of themselves (although widely scattered extents can negatively affect performance). Do this to prevent errors, but do not use it as a substitute for proper INITIAL sizing.