Archives of the TeradataForum

Message Posted: Thu, 07 Jun 2007 @ 16:36:38 GMT

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

You could implement a vertical partitioning scheme. This feature was introduced in v2r5.0 as a part of SATTC (query Satisfiability and Transitive Closure).

You could split the table in to the most queried columns in one and the less utilized into another (something like an 80-20 rule). Place a 1:1 relationship key between them and use a foreign key referential integrity constraint to logically link them together.

A view could be created inner joining the two tables together utilizing the foreign key columns and specifying all the columns of both tables. This allows the user community to logically see the data as one table.

However when a query asks for the columns that are the most utilized and does not reference columns from the other (less-utilized) parent table, the optimizer will not join the tables together since the query can be satisfied by one table (so sayeth the foreign key constraint).

This makes the IO operations much lighter by eliminating unnecessary columns of data (smaller rows sizes leading to more rows per data block and cylinder) - thus increasing query performance. Utilizing an 80-20 rule, 80% of your queries could benefit from this. Teradata DBQL and access logs can aid in the identification of the most utilized columns in queries.

Some talk of wanting to incorporate hints to the optimizer. This is a way (along with good index choices, sound statistics, proper use of column attributes, and global constraints) to feed the optimizer tips to allow for optimal query performance.

Kyle Prescott

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