Hoping for some design expertise - any assistance welcome.
We have a Savings Balance Tracking table that previously held a balance per day for each account. As most savings balances do not change on a daily basis, the developers amended the design of the table to include a Start and End Dt for when that particular balance is valid eg if the balance was static for a 10 day period, what used to be represented by 10 rows is now represented by 1 row. I suspect the design change was diskspace saving driven but don't know for sure. While it does indeed significantly reduce the diskspace requirements for the table, it represents some challenges. The table holds 13 months of historical data so there used to be a fairly obvious requirement for a PPI on Balance_Date to support weekly or monthyl reporting.
However, now there is a Start Date and End Date, the indexing to support the weekly and monthyl reporting is less obvious. The Development team have implemented a Dates look up table which presumably they will use to drive the reports for the particvular period they are interested in
Sample query would be as follows:
SELECT t2.AGRMNT_ID , t2.PARTY_ID
, T2.Brand , t2.PRODUCT_NUMBER ......... t2.ACC_STATUS, t2.DATE_ACC_OPENED, t2.PROD_START_DT
FROM SAVINGS_FACT t2
INNER JOIN DATES_TBL t1
ON t1.Effective_date BETWEEN t2.start_date AND t2.end_date
AND T1.EFFECTIVE_DATE BETWEEN 1150101 and 1150131
Don't think a PPI on either or both Fact date columns will help here in trms of partiton elimination and not yet tested Sec indexes properly but not hopeful.
Is there an obviously efficient way of doing this ?
Before you rule out anything, check the explain to see if there is any improvement. You might also try a JI across these two tables to "pre-join" them
Partitoning will not be usable for queries like this.
It seems you want to recreate the previous data, i.e. one row per day/account and this can be done easily using EXPAND ON.
EXPAND ON only works on PERIODs, but you can easily create them on the fly:
EXPAND ON PERIOD(start_date, end_date) AS pd
FOR PERIOD(DATE '2015-01-01', DATE '2015-01-31')