I have a few massive tables that I need to partition. I've learned from the "SQL and Database Explained!" series on partitioning that my optimal partitioning approach will be via a interval partition:
...PARTITION BY RANGE(start_date) INTERVAL(NUMTOYMININTERVAL(1, 'YEAR')...
The table getting partitioned will be queried with filters against the start_date field:
SELECT columns
FROM My_Table
WHERE start_date BETWEEN SYSDATE-90 AND SYSDATE
ORDER BY start_date;
Is it redundant to index against a field used for a partition? In my case, since I will be partitioning against start_date, will there be additional benefits if I also index on the start_date column?
Excellent question. The answer depends on the range of dates you will be querying vs. the partitioning interval, and whether you have an Exadata or a conventional Oracle database.
If you don't have Exadata, your partitions are yearly, and you ask for a few days, you will still benefit from a local index on the date column. That's because the cost of scanning that entire year worth is likely more than doing an index scan and pulling the rows by ROWID from the index for just a few days' worth.
But, if you (1) do have Exadata, or (2) your partitions are monthly or smaller and your queries are for a day or more, or (3) you have yearly partitions and you are asking for a month or more in your query, then no, you do not need nor want an index on the date column. The cost of using an index in those situations is potentially much greater than the cost of scanning the partition segment(s).
I would suggest that you partition by month and not by year. Too many queries will probably be asking for much less than a year's worth, and you don't want to have to scan a lot of unnecessary segment blocks. You want partition pruning to prune as much as possible, so a smaller interval will help here. Going smaller than a month will only help if your queries are asking for less days than are in the month.