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_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:
- Split incident table into two partitions:
active_incidents, using list-partition and
statusas partition key. Also, I manually enabled row movements on
incident_area_infotable, so that I can close the incident.
incident_area_infousing reference partition.
- Drop the original indexes and replace them with corresponding partitioned local indexes
- 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.