Our incident management system has run for years, and the large quantity of closed incident tickets makes the table rather huge and slows down the search queries.

Our database version is oracle 11g r2 enterprise edition(partition option added). The concerned two large tables are: incident and incident_area_info(one incident corresponds to multiple incident_area_info, and they're joined using incident_id). So I want to use the following strategy:

  1. Split incident table into two partitions: closed_incidents, active_incidents, using list-partition and status as partition key. Also, I manually enabled row movements on incident table and incident_area_info table, so that I can close the incident.
  2. Split incident_area_info using reference partition.
  3. Drop the original indexes and replace them with corresponding partitioned local indexes
  4. Search open incidents only by default

I have applied this strategy in my developing environment and the search operation's execution time reduces to roughly 10% of the original on average(we have nearly 4 million closed incidents and only about 40,000 active incidents).

But "row movements" is disabled by default, so maybe enabling it will cause some performance problems. Well, of course, when a row moves – it will be updated, deleted and re-inserted with all relevant index entries adjusted accordingly. And rowid will be modified after a row is moved(I'm quite sure we do not use rowid in our system, so this won't be a problem).


Besides those mentioned above, will there be any other bad side effects when enabling row movements?


I suspect that moving rows will create space holes in the original partition and the data file will be fragmented after long-term running. Is this true?


If question2 is true, then is there a way to remove these space holes, like alter table mytable shrink space;


Here one guy said 'everybody should be carefull when enabling row movement in production system since enabling row movement invalidates all dependent views, which could result into plenty invalidate objects', but in my developing environment, after moving rows in incident table, the materialized view counting on incident table still works, and in dba_mviews everything seems to be fine. So, did I misunderstand what he means?

Any suggestion is greatly appreciated.

2 Answers 2

  1. You should be fine; the rowid would be a show-stopper if you were using it
  2. Temporarily, there would be "holes" in the data files, but Oracle will fill them in without any intervention. Ensure you have automatic tablespace management enabled.
  3. You can invoke that command but no need to; let Oracle take care of it
  4. The person that stated that probably meant there was a one-time hit, or he didn't know what he was talking about, or using an old version. You tested; his environment may have had a rat's nest of views.

I would move the old records (closed incidents) to another table, not to another partition in the same table. Why ? Because if there is a LOGICAL reason, in my mind it makes more sense to put them as much seperate as possible, on a physical level. Consider this : you can store incidents, but your data schema can also store "open incidents" and "closed incidents", as a separate component. Partitions are nice, but behold the fact that your closed incidents counts, will continue to grow. So, you can't say that when you create partitions, all partitions are behaving similarly. That on itself, looks wrong to me. But technically, it's possible.

  • Well, we ever considered this choice, but its implementation is too complicated. This choice means tons of modifications in incident update operation, search operation and data mining operation. These operations are all very complicated, so it's not realistic to do this work(the later two operations are both done on closed and active incidents). And, partitioning well and truly stores data in different places in physical level.
    – wander
    Commented Dec 4, 2014 at 13:02
  • That's correct, the complexity would be more a problem. But, not from the viewpoint of the current incidents. In that context, it's just like you would delete records. No code needs to be adapted for that. But, if you have specific query code to look for closed incidents (or, any incidents, including closed), then that code would need a change, indeed. I'm just saying what looks the best solution, quality wise. Basically, your data model is setup wrong, and it's a shame it took that time to figure that out. Now, indeed it's too hard to change, and your solution is limited.
    – tvCa
    Commented Dec 4, 2014 at 14:26
  • I know how reality works. But when being in your role, you may stress the fact that better solutions may be provided if more effort is put into it. If they expect serious performance improvements, they may be disappointed again, some time in near future.
    – tvCa
    Commented Dec 6, 2014 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.