Archives of the TeradataForum

Message Posted: Mon, 12 Jan 2015 @ 09:35:33 GMT

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

Dear Anonymous,

Possible solution to consider:

1. Implement partitioning on frequently-used date column (the one most frequently used for filtering).

2. Physically split the table, based on same date column (one table per year, or month, depending on volumes), and use a view to re-combine tables (via "union all").

Do not put "no range or unknown" partition clause into annual or monthly tables. Only use this for current table to cater for unknown new incoming values (if required).

3. Add column constraint on partition column that specifies minimum and maximum values found in this column (>= and <=). This will help with table elimination during query parse time.

For current table only have a >= constraint (not "between").

4. Consider adding the partition date column to the Primary Index column list. This is to reduce the number of hash synonyms that would otherwise be increasing over time.

Additional advantage of physically split tables, is that statistics collection overhead would only be on the current/active table.

Static annual (or monthly) tables would have no need for recollection of optimiser statistics, if their data remains unchanged.

Just be aware that there are implications if you are doing any kind of "upsert" processing, and any record to be updated is not present in current table.

Kind regards

Etienne Stieger
DW DBA, Technical Specialist | Data Warehouse PBB IT | Standard Bank |

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