Question: I want to under
stand the difference between these commands alter table commands,
specifically the "compact" clause:
- alter table my_table shrink;
- alter table
my_table shrink compact;
- alter table my_table shrick cascade;
Note: The "cascade" option follows foreign key (consiraints) and perform anoher table shrink on all "child" subordinate tables". The "casdade" option does niot rebuild indexes.
How is the difference between the "alter table
my_table shrink;" and the "alter table my_table shrink compact"
commnds?
Answer: The compact clause
of the "alter table shrink" syntax directs Oracle to move the rows
but it does NOT lower the table high water mark. The COMPACT
option is used when you expect the table to re-grow back to its
original size, and leaving the high water mark at a high value makes
subsequent insert statements faster because Oracle does less
overhead because the table does not have to extend.
If you expect the table to remain small after
massive deletes, the "alter table shrink" is best, lowering the
high-water mark.
Using the "alter table xxx shrink space compact" command will
re-pack the rows, move down the HWM, and releases unused extents.
With standard Oracle tables, you can reclaim space with the "alter
table shrink space" command:
SQL> alter table mytable enable row movement;
Table altered
SQL> alter table mytable shrink
compact;
Table altered
Also see my notes on
alter table shrink space commands.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
