alter table move (containing clobs) to clean up chained rows....
ji liNov 27 2012 — edited Nov 29 2012Hello colleagues,
I am running Oracle 11.2.0.2 EE on a Solaris 10 platform. (in DG configuration)
My question is regarding chained rows.
I have a slightly large table of approx. 3 Million records (16G in size).
According to dba_tables, my chained_cnt = 2221064.
Wow! That is excessive.
Now, I'm assuming the easiest and quickest way to resolve the chained rows is to do an "alter table ... move" command (using same tablespace).
From my research, this appears easy enough, and I've done this on tables in the past.
Two caveats are that I need to be sure to drop and rebuild all indexes immediately after I'm done, and gather fresh statistics (including indexes).
The question I have is regarding that this table contains about 20 CLOB fields, and 20 LOB indexes.
Will I be able to 'move' the table if it has clob datatypes?
Next question is how do I rebuild the lob indexes? (assuming I need to).
The table also has one Function Based index. Do I need to rebuilt it as well after doing the alter table move?
Thanks in advance for your guideance.