make use of ppi partition elimination without a hardcoded date

Database
Enthusiast

make use of ppi partition elimination without a hardcoded date

Is there any other way of reliably tell the optimizer to use the PPI other than hard-coding the minimal/maximal value (date) in a following case?

Here is a DDL for a big table with customer transactions:

transaction_table
PRIMARY INDEX NUPI_txn ( transaction_id )
PARTITION BY RANGE_N(transaction_date BETWEEN DATE '2008-01-01' AND DATE '2012-12-31' EACH INTERVAL '1' DAY , NO RANGE OR UNKNOWN)
INDEX NUSI_customer_id( customer_id)

I need to get the first first transaction of all customers from a "driver table" (customer_id, status_change_date) on the day of the customer status change.

select customer_id, min(transaction_id) from customer_table
join transaction_table
on transaction_table.customer_id = customer_table.customer_id
and transaction_table.transaction_date = customer_table.status_change_date
group by 1

The query hits the spool space limit.

In a hope to be able to use PPI I split the driver table in half based on their status_change_date. (say before and after the '2010-01-01')
But the partition number is only gets reduced if a hardwired date limit is present in the sql:
where transaction_date between date '2010-01-01' and current_date

The statistics are collected on the (customer_id, status_change_date) but not on (customer_id, transaction_id) because I do not have the rights.

Is there any other way to make optimizer to reduce the number of partitions? Perhaps using a sub-query, volatile table, calendar table?
I hate to hard-code dates into more than one place in the code as it is hard to keep them all in sync.

2 REPLIES
Enthusiast

Re: make use of ppi partition elimination without a hardcoded date

I forgot to mention that the table transaction_table gets redistributed by the hash value of customer_id, transaction_date and this seems to be a problem. If the hard-coded date is present the PPI is used before the redistribution otherwise not.
Junior Contributor

Re: make use of ppi partition elimination without a hardcoded date

This yould be helpful:
http://developer.teradata.com/blog/dnoeth/2011/03/global-and-session-level-parameters-in-sql

Dieter