Detached PK Index

Folks, OS: HP-UX 11.0 ODS: 7.30 FC7 Detached PK index. Bad? In an effort to improve performance we detached indexes on hi hit tables and moved them to a separate disk, this included primary key as well. We are noticing a great many extents related to these detached indexes. We are considering re-attaching the PK indexes. There must be a trade-off somewhere, that somewhere is the question. What are your thoughts? Scott H

"Henderson, Scott" wrote: > Folks, > > OS: HP-UX 11.0 > ODS: 7.30 FC7 > > Detached PK index. Bad? > > In an effort to improve performance we detached indexes on hi hit tables and > moved them to a separate disk, this included primary key as well. We are > noticing a great many extents related to these detached indexes. We are > considering re-attaching the PK indexes. There must be a trade-off > somewhere, that somewhere is the question. > Remember that index pages are cached for long periods due to the relatively larger number of rows represented vis-a-vis data pages so the effect of multiple extents on indexes is less than on data pages. This is even more true in 7.30+ due to buffer priority handling which places index pages in MED-HIGH priority buffers while data pages reside in MEDIUM priority buffers. > > What are your thoughts? If you believe the problem is still great enough to cause you grief just reorganize the index using ALTER FRAGMENT ... INIT IN ... possibly after increasing the NEXT SIZE of the underlying table (detached index extents are sized using the ratio of key size to row size time NEXT SIZE). Art S. Kagel