I have a table that has a large amount of CLOB data and a large number of records. Even with indexes, some queries are extremely slow. My assumption is that the reason for this is two fold:
- There's a lot of data being selected.
- The data for these tables is spread out over a large number of pages in the db.
We decided to move the CLOB data to another table for the DATA and modify our queries to no longer select the CLOB columns and don't join to the new DATA table but our queries are still extremely slow. Is there some additional step we need to take to tell Oracle to re-optimize the table that no longer has the CLOBs?
Best Answer
One way to "re-page the table" is to use the ALTER TABLE... MOVE command. Behind the scenes Oracle will make a temporary copy of the table and switch to that copy. If your table has a lot of unused space in blocks then you might be able to reclaim some of that after the move. If your table has any indexes you will need to rebuild them. I struggled to find a good documentation link for MOVE but try this one.