database - Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2) -

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

our database version oracle 11g r2 enterprise edition(partition option added). concerned 2 large tables are: incident , incident_area_info(one incident corresponds multiple incident_area_info, , they're joined using incident_id). want use following strategy:

1. split incident table 2 partitions: closed_incidents, active_incidents, using list-partition , status partition key. also, manually enabled row movements on incident table , incident_area_info table, can close incident.
2. split incident_area_info using reference partition.
3. drop original indexes , replace them corresponding partitioned local indexes
4. search open incidents default

i have applied strategy in developing environment , search operation's execution time reduces 10% of original on average(we have 4 million closed incidents , 40,000 active incidents).

but "row movements" disabled default, maybe enabling cause performance problems. well, of course, when row moves – updated, deleted , re-inserted relevant index entries adjusted accordingly. , rowid modified after row moved(i'm quite sure not use rowid in our system, won't problem).

question1:

besides mentioned above, there other bad side effects when enabling row movements?

question2:

i suspect moving rows create space holes in original partition , data file fragmented after long-term running. true?

question3

if question2 true, there way remove these space holes, alter table mytable shrink space;

question4

here 1 guy said 'everybody should carefull when enabling row movement in production system since enabling row movement invalidates dependent views, result plenty invalidate objects', in developing environment, after moving rows in incident table, materialized view counting on incident table still works, , in dba_mviews seems fine. so, did misunderstand means?

any suggestion appreciated.

as long don't have code uses rowid , status changes aren't causing many rows move between partitions (which seems unlikely if you're creating 2 partitions), should fine. other downsides of "someone might causes issues because didn't understand implications" such shrinking table.

any space freed when row deleted active partition should reused on subsequent inserts table (assuming have standard oltp application doing conventional-path inserts). shouldn't need worry "space holes".

i read comment quote noting actual "alter table <> enable row movement` command ddl invalidates dependent objects , forces oracle recompile them. unfortunate if ran ddl @ noon on busy day in oltp system.