Archives of the TeradataForum

Message Posted: Wed, 01 Dec 2004 @ 16:42:47 GMT

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

All,

We are starting to use our warehouse in more of an "active" manner by allow certain user to make changes to our Teradata data directly via a web-based front end application. These analyst don't actually change the data being reported on by the reporting users; instead, they make changes to data in shadow tables. If no changes are made to a particular table, then the respective shadow table will be empty. If 20 changes are made to 9 rows, then 9 rows will show up in the shadow table and a total of 20 cells will be populated. The rest of the cells are null.

The analyst can use special views (joining the shadow table to its respective base table based on the primary key and we coalesce the shadow columns with the base columns) for reporting which reflect the "current" view of the data.

This is all fine and dandy for simple queries; however, once queries involving these special views start to get complex, then performance gets very bad very fast (because the optimizer has no good statistics in regards to these special views.)

I've tried pre-joining the table with a Join Index; however, I cannot replicate the coalece logic in the JI defintion....so this approach didn't help much.

Can anyone think of a good solution to my poor statisitcs problem? Or, perhaps, an alternative solution allowing our analysts to report using the "current" view of the data.

Thanks,

Clay

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