A question about Indexing.

Hi In my work environment we are using Informix Dynamic Server AD and XP Options 8.2. Here they don't use indexes at all. (Not even primary key indexes) 1. Is that true if you access more than 15% rows of a table you don't need any indexes? (What about Group by / Order by?) 2. Or, is there some thing particular about indexing in this version of Informix? 3. What is Informix XPS? Is it different from the above version? Thanks.

Vijith Gunawardhana wrote: > > Hi > > In my work environment we are using Informix Dynamic Server AD and XP > Options 8.2. > Seems that nobody else has taken a crack at this, so I'll have a go (and try to leave my prejudices at the door :-)). > > Here they don't use indexes at all. (Not even primary key indexes) > > 1. Is that true if you access more than 15% rows of a table you don't need > any indexes? (What about Group by / Order by?) Most modern cost based optimizers will make the decision for you - so in most environments, you will index data, and let the database decide to use an index or not. However, in large VLDB's, such as those built for Data Warehouses, indexes can be more trouble and cost than they are worth. For instance, I have a site that has around 2TB of raw data. Indexing this data (even simple primary key indexes) could cost anywhere up to an additional 1 - 2 terabytes of disk space, which costs loadsofmoney(TM), and the indexes themselves can take loadsoftime(TM) to build/maintain. So on very large data volumes, indexes can prove to be very expensive, with limited ROI. So often, large data warehouse databases are specifically built to avoid indexes, typically by spreading the data across as many I/O devices and machines as possible, and then utilizing the simple grunt power provided by these type of architectures to quickly scan all or most of the data with high degrees of parallelism. In order to get as many machines and i/o devices 'into the mix' as possible, some sites will use hardware cluster technologies - clustered, multi-cpu machines with lotsofdisk(TM) all interconnected together. This architectural approach is useful for data warehouses that grow, as it is relatively easy to add new nodes and drives to the cluster as data volumes increase. However, clustered hardware architectures typically require specialized versions of databases that support the technology, and the databases themselves must be able to recognize that a full parallel table scan may be more efficient than a index probe and lookup. So the architects building these types of data warehouses often have to look for specialized databases that are aware of some of the issues associated with large data volumes > 2. Or, is there some thing particular about indexing in this version of > Informix? No, other than this is the specialized 'version' of Informix that supports the afore-mentioned clustered hardware technology. > 3. What is Informix XPS? Is it different from the above version? <Flame Suit> No </Flame Suit>. <Troll> Others may disagree.. </Troll> > Thanks. -- Regards, Mark Townsend