Skip to Main Content
  • Questions

  • Table Re-organization or Re-creation

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Maulesh.

Asked: April 17, 2007 - 8:03 am UTC

Last updated: April 18, 2007 - 2:23 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,

We need to re-organize/re-create/re-build few tables/indices. We are doing it with following commnd:

ALTER TABLE <TABLE_NAME>> MOVE TABLESPACE <<TABLESPACE_NAME>>

Is there any way for table/index rebuild which do not require any extra tablespace i.e. no need to extend the tablespace size due to temporary maintaining old and new copy of the same object while re-creation going on ?

Which is the recommended (requiring least memory size) method for performing this operation ?

Thanks in advance,

Maulesh Jani

and Tom said...

Why are you reorganizing the objects?

In short, the answer is "no, not in 9i". You would use dbms_redefinition or alter table T move - both of which will necessarily cause two copies to exist for a brief moment (there is always the ability to move the objects to another tablespace - not the same one as well)

In 10g, you have the ability to "alter table t shrink space compact" - to move all of the rows to the "top" of the table - it does this by an internal 'delete' and a positioned insert of the row at the "top" of the table. Then you can alter table t shrink space - to redraw the high water mark and release space. That will not require a second temporary copy of the table.

but indexes - they pretty much always will.


My recommended method for performing this operation in most all cases is:

DON'T DO IT

it is rarely necessary.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 17, 2007 - 12:58 pm UTC

I like this "DON'T DO IT" recomendation.
I was trying to explain this to our senior DBA for some time now, but he still wants indexes and tables to be reorganized / rebuilt every night!!!!
??!!??

Regards,
Mirjana





Table / Index Reorg Recommendation

Feroze, April 18, 2007 - 12:25 pm UTC

Thanks Tom ,
Thanks for your support to Oracle Users, really great !

Regarding recommendations on reorg of Table and Indexes, We have a Consultant onsite from Oracle Corp, and the Assessment report and recommendations he had given to our manager's and DBA team says this
" Best Practices
¿ Indexes should be checked regularly if they have more leaf blocks than the table.
¿ Reorganize fragmented indexes.
¿ Rebuild indexes online. Rebuilding indexes online does not affect to table base. The following advantages will be provide using alter index <name> rebuild online:
o DMLs are allowed on the base table
o Although slower compared with coalesce, coalesce cannot be done ¿online¿
o Base table is only locked in shared mode (as opposed to exclusive mode for offline rebuilds)
o Intermediate data is stored changes are recorded in a temporary journal table during the index rebuild, which will be used to update the new index at the end of the ONLINE processing.


Recommendations

¿ Reorganize those indexes where Extra_Blocks is high (potential space to reclaim) and the leaf block density is low (i.e., less that 50-60%).
10.12 Sparse Tables

Many delete or update operations on a table over time can produce a sparse table. That means that the high water mark (HWM) of that table is representing a multiple of the really existing data in the table. During full table scans Oracle reads all blocks up to the High Water Mark (HWM). Those tables should be reorganized to improve performance and to need less disk space.
"

Now manager is running behind this suggestion and want us to complete this reorg of tables and rebuild indexes ASAP, and here we are talking about tables around 14 tables more than 100 million rows, don't know the time it will take, it's an 24/7 OLTP applications, with regular deletes, we can have a window of 6 hrs outage, so he is planning to do few in every outage window every month, Any comments whether we should do or not, I know you already said " Don't Do IT ", But this recommendation from Oracle Corp and that consultant , Really confused.

Thanks,

Tom Kyte
April 18, 2007 - 12:55 pm UTC

well, coalesce is online - that is the advanage of coalese, you might point that out...

ask them to clarify that - and to provide detailed information on how to measure that ---- this advice actually pays off. That is, what should you be doing after the fact to demonstrate what all of this work just did for you :)

Table / Index Reorg Recommendation

A reader, April 18, 2007 - 12:31 pm UTC

Sorry, Forgot to mention Oracle Version for the the previous thing i asked .it's Oracle 9.2.0.5.0

Thanks

shrink space...compact

A reader, April 18, 2007 - 1:40 pm UTC

Tom,
I have few questions about the shrink space compact option of alter table:
a) Would I need to rebuild indexes after doing this operation?
b) Would this generate lot of redo?
c) Would it be a long running operation if I have deleted say 200k rows out of 200 million row table and want to reclaim space?

Thanks
Tom Kyte
April 18, 2007 - 2:23 pm UTC

a) nope
b) possibly
c) depends, how were the rows scattered - totally depends (but doesn't really matter - since it is "online", just let it run)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_REDEFINITION

More on PL/SQL routine DBMS_REDEFINITION here