Improving indexing on Balance Tracking table

Database

Improving indexing on Balance Tracking table

All,

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 ?  

Many thanks

2 REPLIES
Enthusiast

Re: Improving indexing on Balance Tracking table

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

Senior Apprentice

Re: Improving indexing on Balance Tracking table

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:

SELECT ...
, BEGIN(pd)
FROM SAVINGS_FACT
EXPAND ON PERIOD(start_date, end_date) AS pd
FOR PERIOD(DATE '2015-01-01', DATE '2015-01-31')