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.
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.