Re: A question about Indexing.
---- you wrote: > From: "Vijith Gunawardhana" <vijith.gunawardhana@wcom.com> > > > >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?) > > This is always open to debate, but the reasoning for not having indexes is > largely as follows: > > * If you read more than a certain percentage of rows from a table, indexes > aren't used anyway (unless there's a sort). > * In general, indexes are beneficial for sorting, but you need to weigh up > the performance benefit against the storage cost and the maintenance cost > (loads will be substantially slower). It may cost you too much load time to > justify the sort benefit. > * By the time the data gets into the warehouse, it should be scrubbed and > validated, hence the lack of keys for RI. (This is the theory, anyway! :-) Unless you speak to ex-RedBrick (now Informix) consultants Dude. They insist a Data Warehouse needs RI! But that might be because they have a star index, and ADO does not. AB ---------------------------------------------------------------- Get your free email from AltaVista at http://altavista.iname.com
> > * By the time the data gets into the warehouse, it should be scrubbed and > > validated, hence the lack of keys for RI. (This is the theory, anyway! :-) > > Unless you speak to ex-RedBrick (now Informix) consultants Dude. They insist a Data Warehouse needs RI! But that might be because they have a star index, and ADO does not. It IS possible to have the RI without the indexes in some of the more advanced databases :-). If the DW has been designed well, you will only need to validate new data, and a straight 'new' partition comparison against the existing data will allow you to check PK's and FK's without the overhead of building an index (although in does increase load time). This then provides support for star indexes etc, and is also useful for end-user tools that use the RI information as meta data. -- Regards, Mark Townsend