Re: Indexing & data warehousing

Doug Johnson wrote: > > I don't think it's quite that simple. > > 1. Informix uses lots of indexes for their TPC-D benchmarks. We've been handed a lot of the latest information which counters the above assertion. I'd like to see some documentation that shows the above statement as fact. > 2. The Data Warehouse Optimizer will (soon?) recommend a set of indexes > based on analysis of MetaCube query audits. It already does this, as part of the process. > 3. GK indexes (bit map, join, etc.) can significantly reduce query elapsed > times and resource consumption when selectivity is less than 1 in 1000. GK indexes have a very limited scope, see notes below. > Physical scans of fragmented tables can burn up every processor in the > cluster. > Scanning is not going to "burn up" CPUs, but it might head up the disks and disk controllers bit. How you set up the fragmentation across the clusters is everything. XPS, as much as I know, and still learning, from actually using it daily, is different from the other engines, in that it wants to build hash indexes on the fly, and thusly, it is redundant to put indexes on columns yourself--for the most part. Yes, integrity indexes are important, but they must be removed for the high-performance loader to work, if you want speed and want to use a RAW table. What's the point to using a unique index if you have to take it off for a data load? Hmmmmm... Using GK indexes has a limited scope of possibility as well, and not applicable in all instances. GK indexes are also a function of a query and not in a permanent design of the table. In other words, you build GK indexes in queries, not in creating tables. You can only use GK indexes on static tables without detached indexes, and they can't be used on unique or clustered indexes. So.... :-) And in XPS 8.2 we can't use detached indexes, so indexes are an afterthought. There are limitations to much of these cool features of XPS, and in practical use we find that building tables with as few joins as possible, and eliminating the need as much as possible to build integrity and other indexes makes for a fast query machine. Sometimes indexes are unavoidable, but they are the exception, not the rule. If you follow classic data warehousing rules, the data is put through integrity checking in the transformation phase, and thus it is a bad idea to put things like unique indexes on data. Indexes are built by the query not by you. :-) It is imperative to have good data base design at this point, obviously, in order to get that optimizer to think correctly. Integrity is a function of good team work through to good data transformations. Data Warehouses don't follow the same rules as OLTP anyway, and this isn't the place for writing a book on it. The key to fast loading is in bypassing the buffer cache, ( all engines here ) and that all-important LIGHT APPEND. Loading speeds that I dare say are unheard of with the other engines are possible with XPS. We would not be able to complete our weekly cycles for data loading if the loading took any appreciable time. Loading speeds of less than 10 MB per second are simply unacceptable. This is only possible with a cluster, loading a single node of XPS is what you'd expect for 7.x, but probably a bit faster. I look forward to the day when XPS is ported for platforms like Linux, because the high science is not in using just one multicpu system, but clustering several multicpu systems together. When XPS becomes more widely available, and understood, it will change a lot of thinking. Of course XPS is available for NT, but this is the ultimate oxymoron. Clustering a non-scaling OS is definitely looking for gridlock. It could be called simply a clusterf**k. :-) To learn more, get the documentation on XPS, and read on. It's an amazing engine. If you plan on buying it, get it for UNIX. Thanks, And Happy Holidays! Tim -- - -- --- Tim Schaefer ---- tschaefe@mindspring.com --- http://www.inxutil.com -- -