HI,
I have a audit table which stores the data for the last 8 years in it and the customer would like to keep the data for the last 8 years. Now since the addition of data to this table is increasing, there is a delay in the report runtime that are acquired by querying against this table.
Basically they pass on a from date and to date. Then those date are compared against two dates columns in the table. The condition of the query will be like below
TRUNC(NVL(DATE1, DATE2)) >= TRUNC(TO_DATE(?, 'dd/mm/yyyy'))
AND TRUNC(NVL(DATE1, DATE2)) <= TRUNC(TO_DATE(?, 'dd/mm/yyyy'))
Users can query for any date range.
I am thinking of creating a Range Partition on this table for each month for the 8 years of data. Please suggest on whether i should use Interval Range partition to make it more effective. And would also create a Index on that partition for the above two date columns.
So 12(months) * 8(years) = 98 partitions would be created. Will this not overload the Database schema/Tablespace? Is there any other efficient way to tune the table.
Please suggest and let me know for any clarification.