Archives of the TeradataForum

Message Posted: Wed, 23 Jun 2004 @ 17:33:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Has anyione seen the following behaviour. I am going to submit an incident, but I was wondering if anyone could make sense out of it.

Given:

a datamart in a typical star schema.

A table with five dimensions. The primary index uses three of theose columns in a NUPI. No secondary indices are used, but stats are collected on each of the dimensions and the primary index.

Hierarchy tables exist in each dimension.

Oracle Express Query that extracts and sums the data. Three columns are specified and use the hierarchy tables to aggregate the results. The other two dimensions use the hierarchy tables to aggregate to a higher level. The specification is not particularly selective in any dimension (~10%, 68%, 72% for the individual dimensions and 5% for all three). Two of the specified columns are in the Primary Index.

The original explain wasn't bad, but took a minute to run on a 19 million row base table. It took the non-selective member of the primary index and joined the fact table to a duplicated hierarchy table spool file (spool file had 14 rows on each amp).

I tried several techniques, including adding a secondary index on all three selective columns and modifying the primary index, to no avail. However, when I added secondary indices to each of the identity columns, I had a better explain that produced a product join with values in the primary index and joined this spool file to the Fact table. This resulted in a 66% performance gain. The secondary indices were not used and the only secondary indices needed were on the two selective identity columns in the primary index.

I do not understand why the indices were needed to drive this change. I already had stats on the columns, so the optimizer should have had the same knowledge before and after the indices were built. It sounds like the combination of stats and index gave the optimizer better information. I don't remember anything about that in any class that I have taken.

I'll let you know what NCR says, but if anyone can help me understand this behavior, I would greatly appreciate it.

Thanks,

Glen

	<Prev	Next>		<<First	<Prev	Next>	Last>>