Re: Data Warehouse design

> > 3. Should indexes be stored in dbspaces separate from the data? > Yes. This is recommended, and only available in 7.x, not 8.x. I think one should use detached indexes instead of attached, but I think I'd rather have those indexes in the same dbspace. It results in smaller indexes and makes it pretty easy to attach fragments of indexed data. I don't think you can attach an index fragment if it's stored in a different dbspace...it would have to be rebuilt. I'll have to check to be sure, but I believe all current releases allow detached indexes in the same dbspace. There was a plan to get rid of attached indexes altogether, but I have no idea if it's being implemented... > Curious why you're not using XPS ( 8.2x ). 7.x is acceptable but not > really designed for DW. If the warehouse/mart is under 200GB, 7.3 tends to be more efficient. > One benefit to 7.x is that you get to separate your indexes into a > separate dbspace from the tables whereas in 8.x you cannot. The indexes > must live in the same dbspace as tables in XPS, but in actual practice > we've been told by Informix to avoid indexes as a rule in 8.x whenever > possible. I'm pretty sure attached indexes don't exist in 8.2 any more. Someone can correct me if I'm wrong, but I do know that was in the plans. Detached indexes in the same dbspace reduce or eliminate extent interleaving and take up less space than detached indexes in different spaces. > What would be optimal is to not only fragment wide, but deep. XPS takes > advantage of hybrid fragmentation, I don't know if 7.x does or not, but > this is a way to not only force the fragmentation by expression, but also > by dbspace at the same time. There's no hybrid in 7.3, unfortunately. I've come across a couple of ways to approximate it with intelligent RAID devices and also by adding an additional column to fragmented tables. The 64K statement length limit doesn't make it any easier.