Re: indexing with partition question(s)
Posted in 2010
Topics: Performance & Tuning
Fragment elimination on the table is independent of that of the index for the most part. Also, you certainly could fragment the index by rec_typ as well essentially dividing it into an index on deleted rows and a separate index on active rows. Fragment elimination on the index itself would also help. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 8, 2010 at 12:42 PM, Floyd Wellershaus <floyd@fwellers.com>wrote: > Hello, > We are going to have a long denormalized table soon. It will be filled > with records that get marked deleted ( rec_type=d ), and of course other > rec_types. But the largest part of the table that I want to eliminate in > queries are the deleted records. > So I was thinking of fragmenting the table by rec_type. > > Now, they want to query on rec_type and other fields also. So how would the > indexes work ? > If I make a composite index with rec_type as the first column, and say ssn > and school_token as the second and third columns, will fragmentation > elimination occur to eliminate all the deleted records ? > I am thinking it won't because the index can't be created to follow the > fragmentation scheme since it has other columns in it. Is that right ? > > Should I just make an index on rec_type to follow the scheme, and hope the > optimizer uses that index and then the others ? > > A little confused about how it works. > > Thanks, > floyd > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >