I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as possible in this table so that we can construct month-to-month change reports that go as far back as possible.

for example, the data looks similar to this:

| p_key | monthend_id | val |
| 0     | 8/31/2018   | 100 | 
| 0     | 7/31/2018   | 90  | 
| 0     | 6/30/2018   | 110 | 
| 1     | 8/31/2018   | 25  | 
| 1     | 7/31/2018   | 31  | 
| 1     | 6/30/2018   | 22  | 

To do this, I need to match the table to itself where the two are exactly 1 month apart. Right now I have a query that runs with a join like this:

FROM monthend_table t1 LEFT JOIN monthend_table t2
ON t1.p_id = t2.p_id 
AND to_char(add_months(t1.monthend_source, -1), 'YYYY/MM') = 
to_char(t2.monthend_source, 'YYYY/MM')

This works fine, and I may even be able to take out the character formatting, however I notice in my execution plan that the index on monthend_source isn't being used, and so it's doing 2 full scans.

How would I best construct indexes that would facilitate the fastest join possible?

Should I create one for (p_id, to_char(monthend_source, 'YYYY/MM')) and another for (pid, to_char(add_months(monthend_source, -1), 'YYYY/MM'))? Is a single index better?

I often find I struggle to know when and how to create effective indices in (what is to me) more complicated situations like these, so any advice would be greatly appreciated!

  • To create a "month-to-month change report", I recommend using Oracle 12c's MATCH_RECOGNIZE feature. – Michael Kutz Sep 4 '18 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.