Search This Blog

Tuesday, January 24, 2012

Oracle Database Row Chaining Solution.

Move the affected table

1) alter table hr.employees move;

Rebuild the indexes on that table because they will become 
invalid because of move operation.

2) SQL> select owner, index_name from dba_indexes where
        table_name = 'EMPLOYEES';


OWNER                          INDEX_NAME
------------------------------ ------------------------------
HR                             EMP_NAME_IX
HR                             EMP_MANAGER_IX
HR                             EMP_JOB_IX
HR                             EMP_DEPARTMENT_IX
HR                             EMP_EMP_ID_PK
HR                             EMP_EMAIL_UK

EXAMPLE:

SQL> alter index HR.EMP_EMAIL_UK rebuild;

Index altered.


Also collect fresh statistics on the table because they will
become stale.

SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',
     tabname=>'EMPLOYEES', cascade=>true);

PL/SQL procedure successfully completed.

0 comments:

Post a Comment