Support ticket system - when/how to move old tickets?

Question

I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting resolved. Obviously, if I tried to keep them all in one table ever since the beginning, the table would become HUGE.

My questions are:

1. Once a ticket gets resolved, should I move it out of the original table; or
2. Should I just move resolved tickets out of the original table after they are 90 months old; or
3. Should I move everything whether resolved or unresolved into another table after 90 months and do some kind of UNION every time unresolved queries are sought?

Answer 1

What is the problem with the table becoming large? Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant. The cost of using an index will grow at an O(log(n)) rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table. And you can potentially limit that further by using function-based indexes to limit the size of the index by doing things like only indexing the active rows.

The only queries that should care about the size of the table are queries where you want to do a full scan on the table in order to do things like produce metrics about how many tickets have been opened since the beginning of time (or, at least, over a significant fraction of history). If you are concerned about those sorts of reporting queries, you can do things like use materialized views to pre-aggregate the data.

Normally, I would suggest keeping a single table and ensuring that appropriate indexes and/or materialized views exist to support the queries about whose performance you are concerned.

Answer 2

It you want to partition the table, you may want to look at Oracle's capabilities. A separate partition per year might make sense. Other partitioning schemes such as by group, division, or other criteria. Table size shouldn't be a problem for active queries, but you may want to have an index which allows you to select unresolved issues. You may need to use constant for the resolved status in queries of unresolved tickets to ensure the index is reliably used.

You should be considering the lifetime of the data. Some used of the data may require longer life times. It may be important to keep records which are frequently referenced to solve new problems.

Consider your timelines:

• Anything unresolved after 90 months (7.5 years) is unlikely to ever be resolved. Is it worth keeping this data?
• Anything unresolved after 90 weeks (1.5 years) has been seriously ignored.
• Anything unresolved after 90 days (3 months) should be close resolution. (From your statement is should already be resolved. Has it been abandoned? or should it be escalated?)
• Anything unresolved after 90 hours (aprox. 4 days) should have been escalated or at least considered for escalation.
• Anything unresolved after 90 minutes (1.5 hours) will likely need follow-up.

Archiving and deleting old records after an appropriate period may make some queries run faster.