Fragmentation vs. Index

Hi, I have a large table (800 million rows) fragmented by expression on a single column, let's call it fieldA, across 50 dbspaces. FieldA is of integer type and is a unique constant value for each fragment. FieldA is joined to a corresponding code table on the same column from within the user's query tool. Do I still need to create an index on this column in the large table or is the engine's query plan smart enough to know this information? It seems that queries run much faster when I do not link to the code table outside of the user query tool. (btw, I'm using Informix 7.31) Thank you, Tony

If every query runs with PDQPRIORITY 2 or higher then fragment elimination is enabled and there is likely to be little if any gain from indexing the fragmentation column. Indeed I'd doubt the optimizer would ever select that index. On the other hand, for PDQPRIORITY in (0,1) (so with fragment elimination disabled) one or more indexes beginning with the fragmentation column plus a few common keys would prevent searching all the fragments serially (PDQ=0) or even in parallel (PDQ=1). Art S. Kagel ----- Original Message ----- From: Tony Demeis <Tony.Demeis@moh.gov.on.ca> At: 5/16 10:41 > Hi, > I have a large table (800 million rows) fragmented by expression on a single > column, let's call it fieldA, across 50 dbspaces. FieldA is of integer type > and is a unique constant value for each fragment. FieldA is joined to a > corresponding code table on the same column from within the user's query > tool. > > Do I still need to create an index on this column in the large table or is > the engine's query plan smart enough to know this information? > It seems that queries run much faster when I do not link to the code table > outside of the user query tool. > > (btw, I'm using Informix 7.31) > > Thank you, > Tony

Hi , I have a similar situation. I have fragmented my index also with the same strategy. Now will the index be followed or not( PDQPRIORITY> 2)?? Rgds Preetinder "ART KAGEL, ...." wrote: > If every query runs with PDQPRIORITY 2 or higher then fragment elimination is > enabled and there is likely to be little if any gain from indexing the > fragmentation column. Indeed I'd doubt the optimizer would ever select that > index. On the other hand, for PDQPRIORITY in (0,1) (so with fragment > elimination disabled) one or more indexes beginning with the fragmentation > column plus a few common keys would prevent searching all the fragments serially > (PDQ=0) or even in parallel (PDQ=1). > > Art S. Kagel > ----- Original Message ----- > From: Tony Demeis <Tony.Demeis@moh.gov.on.ca> > At: 5/16 10:41 > > > Hi, > > I have a large table (800 million rows) fragmented by expression on a single > > column, let's call it fieldA, across 50 dbspaces. FieldA is of integer type > > and is a unique constant value for each fragment. FieldA is joined to a > > corresponding code table on the same column from within the user's query > > tool. > > > > Do I still need to create an index on this column in the large table or is > > the engine's query plan smart enough to know this information? > > It seems that queries run much faster when I do not link to the code table > > outside of the user query tool. > > > > (btw, I'm using Informix 7.31) > > > > Thank you, > > Tony