Archives of the TeradataForum

Message Posted: Wed, 27 Mar 2002 @ 11:36:08 GMT

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

We parition our tables by date within our ROLAP reporting environment. Though the tool isn't smart enough to know which of the partitioned tables to scan based upon the user's selection, the net result is faster overall query response times. This is because the user typically queries recent data a majority of the time. It doesn't take the optimizer very long to determine whether or not data exists in a table for the dates specified by the user. You can play around with the Primary Index to help the optimizer look at the dates provided by the user (in comparison to what is stored in the partitioned tables) as soon as possible via the use of 'Key Tables' hidden within views.

We used to peform a 'Union All' of these partitioned tables; however, the Optimizer would often attempt to perform the 'Union All' in spool before applying the filters (thus creating a huge spool file with no statistics or NUSIs.) With the assistance of an OLAP layer, we are now able to 'Union' just the answer sets of the mulitple SQL statements in cache. Without this layer, I don't know if I'd consider partitioning my larger tables.

Also, regarding the new date partitionig feature in V2R5, it sounds more like a maintenance feature (for dropping and updating rows) than a run-time query performance feature. Any thoughts, maybe I need to do some more reading?

Thanks,

Clay

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