Archives of the TeradataForum

Message Posted: Thu, 08 Mar 2001 @ 18:24:22 GMT

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

Dave and Lee,

I'm not sure why you are running out of spool; however, I think I know a way that you can help "persuade" the optimizer to take the path you are hoping for (ie. product join between Time and Product followed by a merge with the fact)

**********************************************

Keep the PI of the Fact table the same (TimeValues, ProductValues) Make sure there is no NUSI for either the Time or Product columns in the Fact table

Create 2 new tables:
1 = Just the distinct values (1 column) from the lowest level of the time dimension table (I would assume that this table would have the same number of records as your current product dimension table.) TimeUniqValue 2 = Just the distinct values (1 column) from the lowest level of the product dimension table (I would assume that this table would have the same number of records as your current prodict dimension table.)

ProductUniqValue

collect statistics on the 2 new tables

     Create 1 new view:
     ~
     (
     Select    fact.OfferValues,
          timeuniqvalue.TimeValues,
          productuniquevalue.ProductValues,
          fact.measure#1,
          .....
     From fact,
          timeuniqvalue,
          productuniqvalue
     Where     fact.TimeValues = timeuniqvalues.TimeValues and
          fact.ProductValues   = productuniqvalues.ProductValues
     )

This is the idea:

- when you write a query against the Fact table, you must use the new view described above.

- the optimizer will first limit the 2 new tables (by way of each tables UPI) according to the user's selection

- the optimizer will then take a cross product of the 2 new tables

- the resulting cross product of the 2 new tables will be merge joined back to the PI of the Fact table

- more than likely, any Offer values will be filtered by way of a residual condition

done....

Let me know if this works.

Have a great day,

Clay

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