Archives of the TeradataForum

Message Posted: Fri, 13 Jun 2003 @ 12:42:57 GMT

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

Hi,

The company I am currently contracting for is about to upgrade to V2R5, and one of my immediate tasks is to add MVC and PPI's to the largest table on the datawarehouse. This contains nearly 6 billion rows of transaction level date stored by transaction date.

I'm expecting that partitioning the table by transaction month will give a big performance improvement to user queries that specify hard- coded dates such as the following:

a) Sel * from table1 where txn_dte = '2003-01-01' (uses 1 partition)

b) Sel * from table1 where txn_dte Between '2003-01-01' and '2003-03-31' (uses 3 partitions)

This is fine for many of our user queries, however scheduled production queries almost always take the form:

a) Sel * from table1 where txn_dte = (sel dte1 from controltable);

b) Sel * from table1 where txn_dte between controltable.startdte and controltable.enddate;

Both of these types of request ignore the PPI and revert to a Full Table Scan instead.

I can see a could of potential work-arounds that seem to work in test:

1. Export the date(s) into a file and use an Import & Using statement to feed the date directly into the query. This seems to work for query type (a) but not type (b).

2. Dynamically build the SQL, prior to execution, to resolve all dates into hard-coded form. This option is a little messy to say the least!

I wondered if anyone who has used this functionality for real has found any other ways around this. Otherwise it is rather a shame that PPI's only do their thing when the predicate is effectively hard-coded.

Regards,

Alan Charles
Group Data Warehouse DBA

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