Indexes still valid?
Mark J. Bobak, April 11, 2004 - 10:15 pm UTC
Tom,
As we know, alter table <table_name> move; will cause all
indexes to be marked UNUSABLE. What effect does the
shrink operation have on indexes? Does it update them on
the fly, or will they have to be rebuilt?
Thanks,
-Mark
April 12, 2004 - 7:11 am UTC
the online segment shrink works conceptually as if it did a delete and insert ( hence the enable row movement -- ROWIDS will change). This maintains the indexes in real time.
A reader, April 12, 2004 - 1:45 am UTC
Could you explain:
1) ENABLE ROW MOVEMENT
2) the difference between SUM(blocks) OVER and SUM(blocks) ?
3) The difference between both operations:
ops$tkyte@ORA10G> alter table t shrink space compact;
Table altered.
ops$tkyte@ORA10G> alter table t shrink space;
Table altered.
April 12, 2004 - 7:14 am UTC
1) introduced in oracle 8i -- it allows rows to move and change rowids. in 8i, this was to allow the updating of a partition key that caused the row to move from partition to partition. with the online segment shrink it allows the row to move from the 'bottom' of the table to the 'top' of the table and have its rowid change.
without enable row movement -- a rowid is imutable.
2) sum() over is an analytic function. sum() is an aggregate. See the data warehouseing guide for details on analytics (or just keep reading this site, I use them all of the time...)
3) </code>
http://docs.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_3001.htm#i2192484 <code>
A reader, April 12, 2004 - 7:40 am UTC
I'm confused:
1) You said:
ops$tkyte@ORA10G> alter table t shrink space compact;
Table altered.
ops$tkyte@ORA10G> alter table t shrink space;
Table altered.
The SQL ref said:
<quote>
If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.
</quote>
Why should I want to do this in 2 steps instead of 1 ?
2) If rowids change, like with MOVE, shouldn't I rebuild the indexes ? It's not clear to me.
3) sum(blocks) over () works like a the grand total for the blocks columns here, right ?
April 12, 2004 - 8:12 am UTC
<quote>
This clause is useful if you want to accomplish the shrink operation
in two shorter steps rather than one longer step.
</quote>
???
2) no, no need to rebuild indexes, as I said -- it does it for you!
3) yes, sum(blocks) over () works like a grand total (it is a 'grand total')
A reader, April 12, 2004 - 8:26 am UTC
<quote>
This clause is useful if you want to accomplish the shrink operation
in two shorter steps rather than one longer step.
</quote>
???
One step, though longer, isn't simpler ??? In which specific situation would I prefer 2 shorter steps ?
April 12, 2004 - 8:32 am UTC
when you want to.
The second clause actually takes a short table lock, so you might want to break it in two -- one to move the rows, the other basically to redraw the HWM.
Tis a matter of choice in the end.
Excellent followup
Arun Mathur, April 12, 2004 - 9:38 am UTC
Couldn't have asked for a better answer.
Cheers,
Arun
Deleting rows in 10g
Lu Cheng, April 12, 2004 - 11:42 am UTC
This is good in telling what happens to the rows in the table. But it did not tell what happens to the index and index space. Will the index get more fragmented?
April 12, 2004 - 2:05 pm UTC
a "perfect" index might -- by perfect I mean totally compact.
A "normal index" probably not. Here is an example of each. What I did was to put an index build after the delete -- before the compact to see what would happen (this is the perfect index -- it is totally "perfect" before the compact, perfect to the point of being pctfree=0). I then coalesced it, and then did a rebuild online -- just to compare the 4 states -- before, after, coalesced, rebuilt -- to see what we see. The net result was:
delete from t where mod(object_id,2) = 0;
commit;
<b>create index t_idx on t(object_id) pctfree 0;
analyze index t_idx validate structure;
-- save stats value1</b>
alter table t shrink space compact;
alter table t shrink space;
analyze index t_idx validate structure;
<b>-- save stats value2
alter index t_idx coalesce;
analyze index t_idx validate structure;
-- save stats value3
alter index t_idx rebuild pctfree 0 online;
analyze index t_idx validate structure;
-- save stats value4</b>
ops$tkyte@ORA10G> select * from stats;
CNAME VALUE1 VALUE2 VALUE3 VALUE4
----------- ---------- ---------- ---------- ----------
height 2 2 2 2
blocks 56 80 80 56
lf_rows 23717 24712 23717 23717
lf_blks 47 72 47 47
br_rows 46 71 46 46
br_blks 1 1 1 1
del_lf_rows 0 995 0 0
btree_space 383840 583740 383840 383840
used_space 375262 391481 375262 375262
9 rows selected.
as you can see, the effect of "delete + insert" with the modified rowid did cause the index to "grow" in this case. The change from value1 to value2 shows the effect of the compaction on this index. But, notice that the coalesce (online operation) fixed things up (did not release storage, but fixed them up). The rebuild (online operation) reclaimed the storage.
Now, taking a more "real world" case -- where the index was already in existence (not perfect, not pctfree = 0 ):
<b>create index t_idx on t(object_id) pctfree 0;</b>
delete from t where mod(object_id,2) = 0;
commit;
<b>
analyze index t_idx validate structure;
-- save stats value1</b>
... (and so on)...
ops$tkyte@ORA10G> select * from stats;
CNAME VALUE1 VALUE2 VALUE3 VALUE4
----------- ---------- ---------- ---------- ----------
height 2 2 2 2
blocks 104 104 104 56
lf_rows 25691 25994 23702 23702
lf_blks 94 94 47 47
br_rows 93 93 46 46
br_blks 1 1 1 1
del_lf_rows 1989 2292 0 0
btree_space 759652 759652 383840 383840
used_space 406508 411371 375022 375022
9 rows selected.
<b>we see it had no material affect -- since we had plenty of whitespace in there to begin with -- let us move stuff around readily</b>
You will see results that fall IN BETWEEN these two extreme cases (they were both "perfect" in their own sense). But -- it makes sense that if you shrink a table, you'll be doing the same to the indexes afterwards. Just realize that you have coalesce as well as rebuild to use -- depending on your needs.
(i should mention -- you can add CASCADE to the shrink/compact commands and they will reorg the indexes as well)
ORA-01735: invalid ALTER TABLE option
Judy, November 03, 2004 - 5:02 pm UTC
Hi Tom,
I have my tablespace set segment management automatic
and I did ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
However, when I run:
ALTER TABLE scott.emp SHRINK SPACE;
It gives me:
ORA-01735: invalid ALTER TABLE option
I am puzzeled,
Thanks for your help.
November 05, 2004 - 11:18 am UTC
ops$tkyte@ORA9IR2> alter table t shrink space compact;
alter table t shrink space compact
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
that is what you would expect in 9i -- as this is a new 10g feature.
ops$tkyte@ORA10G> alter table t shrink space compact;
Table altered.
ops$tkyte@ORA10G> alter table t shrink space;
Table altered.
is what you would expect in 10g only
hmmmmmmmmmm
Connor McDonald, May 09, 2005 - 10:47 am UTC
so you have to have an assm tablespace, ie a facility which 'scatters' rows to assist concurrency in order to use a facility that 'de-scatters' rows to pack them more densely...
I'm still trying to think through the possible useful scenarios
a) if you're trying to fix full table scans, then you'd really only look at doing this if the percentage benefit is "significant" (lets say a drop of 30% in the hwm). On evenly distributed data in the rows, at 30%, you'd have to be thinking about unload/reload (say using dbms_redef...) being more efficient and timely.
b) Unless of course, you know in advance that you're going to get 'bang for your back', eg, a small number of rows up the near the hwm with a lot of empty space underneath. But to work that out, I've doubled up my work already (scan the table to check the block ids, then scan again to do the shrink).
c) Last time I checked, the shrink did sequential not scattered read. That's another big slow down
d) I'm not at a machine at the moment, but I recall doing a similar test to yours with various modulos, and you could get to around 15-20% free space (modulo 6 or 7?) without actually getting any returned space on a shrink command.
e) its not really gonna help index lookups *directly*, since the hwm isn't relevant
f) if we think about maybe *indirectly* helping index lookups (through increased data density), well, then shrink might make a serious mess of your clustering factor (or it might not - you really can't tell). Since v10 has the order-by clause in dbms_redef, you'd have more confidence in its preservation (or improvement) in the clust fact.
I'm still sitting at: a really cute and cool feature that doesn't really have a lot of usefullness...
problem using SHRINK,
sns, January 11, 2006 - 3:18 pm UTC
First we tried the SHRINK feature on one of our tables and it run for almost 8 hours and then completed. It couldn't gained much space then (we knew there are lots of free blocks in it).
Again we tried this option with another BIG table and it got failed with
ORA-00607: Internal error occurred while making a change to a data block error.
I tried with a different table this morning and again failed with below error.
ERROR at line 1:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [74], [434872],
[18038], [], [], [], []
We tried doing reorg on individual partitons and able to gain more space faster than this option.
Is SHRINK command has any issues?
Thanks,
January 12, 2006 - 10:47 am UTC
the action of ora-607 seems very concise and clear:
$ oerr ora 607
00607, 00000, "Internal error occurred while making a change to a data block"
// *Cause: An internal error or memory exception occurred while Oracle was
// applying redo to a data block.
// *Action: call Oracle Support
online segment shrink on an index or IOT
A reader, January 06, 2007 - 3:30 pm UTC
Hi Tom,
At the top of this thread you described what happens conceptually when an online segment shrink is done on
a heap table.
Could you also describe what happens conceptually when an online segment shrink is done:
1. on an index
2. on an IOT
Does the index/IOT get coalesced as part of the online segment shrink?
Thanks!
January 07, 2007 - 8:05 pm UTC
you do not do online shrinks of those, you do online rebuilds.
online segment shrink on an index or IOT
A reader, January 07, 2007 - 10:01 pm UTC
> you do not do online shrinks of those, you do online
> rebuilds.
Tom,
The 10GR2 documentation indicates you can do online segment shinks of IOTs and indexes. Quotes from the documentation are included below. Is the documentation incorrect?
Database Concepts
10g Release 2 (10.2)
14 Manageability
Segment shrink works on heaps, IOTs, IOT overflow segments, LOBs, LOB segments, materialized views, and indexes with row movement enabled in tablespaces with automatic segment space management.
Database Administrator's Guide
10g Release 2 (10.2)
14 Managing Space for Schema Objects
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
* IOT mapping tables
* Tables with rowid based materialized views
* Tables with function-based indexes
...
The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk.
...
Shrink an IOT index segment and the overflow segment:
ALTER TABLE cities SHRINK SPACE CASCADE;
January 08, 2007 - 11:56 am UTC
ops$tkyte%ORA10GR2> create table t ( x int primary key, y int, z int ) organization index;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t shrink space compact;
Table altered.
ops$tkyte%ORA10GR2> alter table t shrink space;
Table altered.
guess so - seems for those structures, you would likely benefit more from a rebuild in that particular case however (and index rebuild of the structure, not just trying to compact and then release space)
online segment shrink on an index or IOT
A reader, January 08, 2007 - 12:27 pm UTC
> guess so - seems for those structures, you would likely
> benefit more from a rebuild in that particular case
> however (and index rebuild of the structure, not just
> trying to compact and then release space)
The 10.2 Database New Features Guide documents it as an alternative to other types of rebuilds:
"Online Segments Shrink capability is now extended to all types of segments. This will further reduce the need to perform other more advanced reorganization methods such as Online Redefinition."
What exactly does an online segment shrink of an index or IOT do? Does it deallocate completely empty blocks and also coalesce partially full blocks? Or just deallocate completely empty blocks?
Questions on shrink space
Drew, September 09, 2007 - 11:04 am UTC
Hi Tom,
A few questions regarding the usage of shrink space.
1. To perform "shrink space" on a table, we need to "enable row movement" on a table. After the shrink, should we leave the "row movement" enabled or should we disable the "row movement" again? Is there any difference?
2. You mentioned we don't need to perform "rebuild index" after shrinking the space. Should we perform the "shrink space" the indexes of the table?
3. Should we collect stats after shrinking the table?
4. For a table that performs the "delete" and "insert" in the similar amount, if we just perform a "shrink space compact" and leave the HWM there, the space below the HWM will be filled first and we never need to perform the second step, "shrink space". Is that concept correct?
Thanks!
September 12, 2007 - 10:44 am UTC
1) only you can answer that. enable row movement is simply granting us permission to move the row - do you want us to have that permission after you are done?
2) how do you "shrink space" on an index?
3) depends, clustering factors will change, full scan information will change. would you have gathered stats if the table started compact and became what it was before the shrink?
if you do gather statistics, please expect query plans to change and do not be surprised when they do.
4) Not following you, if you delete and insert the same amount, assuming table was compact in the first place, why would you shrink?
Delete rows and Shrink space
Drew, September 13, 2007 - 10:24 am UTC
1) only you can answer that. enable row movement is simply granting us permission to move the row - do you want us to have that permission after you are done?
I think I did not ask the question clearly. I want to know if there any performance or file manage issues with leaving the row movement enabled. Are the rows will move "at their will" or are they going to move only after we issue a "shrink space"? As long as there is no performance issues, we should be ok leaving it there.
2) how do you "shrink space" on an index?
alter index index_name shrink space;
3) depends, clustering factors will change, full scan information will change. would you have gathered stats if the table started compact and became what it was before the shrink?
if you do gather statistics, please expect query plans to change and do not be surprised when they do.
Thank you!
4) Not following you, if you delete and insert the same amount, assuming table was compact in the first place, why would you shrink?
We have a very large log table that log the user activities. We used to keep a month's log. Because it's too large and some queries run relatively slow. We determine to keep just one week's log in the active log table and move the older records to an archive table. Then the moving from active to the archive needs to be done every day on a cron job. We now run the delete and shrink every day. The shrink takes a few hours every day. I am thinking we don't need to run the shrink after the deleting because similar amount are added and deleted every day. I want to listen to your expert advice on this. All of the above questions are related this move.
Thank you for all your help.
September 15, 2007 - 7:41 pm UTC
1) it is simply a granting to us the permission to do so, it affects nothing at runtime performance wise.
4) you should look into partitioning. I don't think you need to shrink, no.
You don't need to shrink
Jason Martin, September 13, 2007 - 4:34 pm UTC
There is no need to shrink a table that you know will almost immediately grow back up to its previous size. That table's natural size is the larger size, and a daily shrink is just making Oracle do unnecessary work. Perform the deletes like you already are, and Oracle will reuse the free (but already allocated) space for the new inserts -- assuming the application isn't giving an APPEND hint or anything like that.
You could also consider partitioning & local indexes, whereby you could just exchange the old partition into the archive table and save a lot of IO & redo. A daily partition is probably excessive, but you might be able to do it with a weekly or monthly one.
Deleting from millions of records
Dulal, February 07, 2008 - 3:51 am UTC
Hi Tom,
I have production database on 10g on linux with 24x7 supports and a table with 10 millions of record.
Now I have to delete 1 million records from that table.
Anyway I cann't stop the database.
So what are the BEST ways to delete the records and how?
Please advise me.
Best regards.
February 07, 2008 - 8:37 am UTC
insufficient data to answer.
do you have to do this again and again? eg: in N months, will you be asking this same thing?
if so, they we want to partition this table - using dbms_redefinition (online) so that we can simply truncate a partition later.
if this is one time - are there dozens of indexes? are you going to hit lots of people trying to lock the data you are removing (a batch contending with transactional applications)? How is the data organized - like are you deleting really old data (where dt < sysdate-1000 is the predicate for example) so all of the data you are removing is likely contigous on disk. Will your where clause require a full scan. Etc.
You have at your option:
a) redefine the table, make it partitioned and never use delete
b) use delete
c) if this table is just inserted into (eg: it is like an audit trail or something) you could a) create a new empty audit trail, b) rename (flip flop) the tables, c) insert /*+ append */ into new audit trail select what you want to keep from old
among others. If there were a single simple best way - there would only be one way - the others would never have been invented.
Deleting from millions of records
Dulal, February 11, 2008 - 5:56 am UTC
Hi Tom,
Thanks for your early response.
But, I want to delete the records for ONE time only
with quickly, easiest way, with/without minimum disturbance
of normal transaction and structured way.
February 11, 2008 - 10:19 pm UTC
and you answered nothing I asked.... oh well.
just delete then I guess, given what you've told me, I have nothing else to say really.
For ASSM Only?
A reader, September 12, 2008 - 1:48 pm UTC
In the beginning of this thread, you mentioned that online segment shrink is available for tables in ASSM (automatic
segment space mgmt) tablespaces. I tried it on a table not in an ASSM tablespace and it seemed to work. Did I do something wrong?
September 16, 2008 - 1:58 pm UTC
Yes, you did something wrong - but since you didn't share your work - we cannot really guide you further
ops$tkyte%ORA11GR1> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name = 'MSSM';
SEGMEN
------
MANUAL
ops$tkyte%ORA11GR1> create table t tablespace mssm enable row movement as select * from all_users;
Table created.
ops$tkyte%ORA11GR1> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
ops$tkyte%ORA11GR1> !oerr ora 10635
10635, 00000, "Invalid segment or tablespace type"
// *Cause: Cannot shrink the segment because it is not in auto segment space
// managed tablespace or it is not a data, index or lob segment.
// *Action: Check the tablespace and segment type and reissue the statement
ops$tkyte%ORA11GR1> drop table t;
Table dropped.
ops$tkyte%ORA11GR1> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name = 'ASSM';
SEGMEN
------
AUTO
ops$tkyte%ORA11GR1> create table t tablespace Assm enable row movement as select * from all_users;
Table created.
ops$tkyte%ORA11GR1> alter table t shrink space;
Table altered.
A reader, September 12, 2008 - 5:39 pm UTC
Never mind my question above. You meant ASSM not ASM. Sorry.
How to Monitor Online Segment Shrink?
A reader, September 15, 2008 - 2:05 pm UTC
I ran the command to shrink a table.
alter table <table_name> shrink space cascade;
It took a few hours to complete. While it was still running, I tried to monitor the progress using v$session_longops system view. However, I couldn't find anything in this view for the online segment shrink job. Is there anyway I can monitor the progress of an online segment shrink job?
Defference between move and shrink
Khalid, June 03, 2009 - 3:11 pm UTC
I hope this is not new question. what is the defference between alter table move and shrink
and what is faster.
June 04, 2009 - 12:30 pm UTC
alter table t move - reads old segment, writes new segment, invalidates all indexes (which need to be rebuilt). entirely offline operation - need to suspend access to table during the move and subsequent index rebuilds.
alter table t shrink space compact/alter table t shrink space - first one moves rows from 'bottom' to 'top' of table - putting all free space at 'bottom' of table. It is entirely ONLINE - you never stop accessing the table. the second one deallocates the free space (optional), this does lock the table BRIEFLY.
shrinking is more online, and you don't care how long it takes, it is online.
risks of enable row movement
SAL, June 05, 2009 - 1:44 am UTC
Tom,
I have a few questions:
1. Are there any situations where enabling row movement will not be advisable? We have a high transactional OLTP 800 GB database (10.2.04 version). I have recently started to administer it and is used by a third party vendor product.
2. How to tell if there are rowid based triggers? I ran compact command on a table in one of the test databases and it right away disabled triggers on that table automatically. Is that normal behaviour?
3. While compact command is running, will there be any effect on performance of the database?
Thanks!
June 08, 2009 - 11:47 am UTC
1) if you have any code that relies on a rowid never changing for a row ( as it used to be - rowids never changed in the past ), then enabling row movement would be bad.
otherwise, it lets you do something you cannot otherwise do - if you need to do that thing, then you need to do that thing.
2) what is a rowid based trigger?
3) of course, you are doing work that you would not otherwise be doing. You'll be generating undo that would not otherwise be generated, you will generate redo that would not otherwise be generated, you will modify every index on the table with a delete+insert operation to move the row in the index as well as move it in the table.
risks of enable row movement
SAL, June 08, 2009 - 5:23 pm UTC
Thanks for the rowid explanation.
What is a rowid based trigger: I got that term from
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2141 The above link has: Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object.
Since the segment advisor is reporting a ton of tables that can take benefit from shrink, I want to determine which triggers are to be disabled (basically giving me a downtime).
Also one more question: if shrink runs for say one hour when I decide to terminate it, have I lost all work done so far? We have a small maintenace window, so I will need to break up the shrink of the same table in parts..
Thanks.
June 08, 2009 - 6:06 pm UTC
it sounds like they are talking about triggers that capture the rowid - but they wouldn't need to be disabled since - well - triggers won't fire (so disabling would do what? nothing)
Basically, if you rely on the rowid to be persistent - immutable - you cannot do this (enable row movement)
If you do not, you can.
Not sure what the documentation is trying to say with "rowid based trigger" - I see a lot of people have copied it - but no one demonstrates it :)
You do not need a maintenance window for this - it is online, do not kill it - just let it run.
How to monitor shrink progress
SAL, June 15, 2009 - 6:19 pm UTC
Tom,
Since the application is classified as mission critcal, no changes are permitted (I know this is not a "change", but ..) without appropriate approvals etc. That is why I will have to stick to application designated maintenance window.
In light of that, answers to two questions will be very helpful:
1. Can the shrink process be restarted such that it will not loose the work already done.
2. How to monitor the progress of a long running shrink and be able get % completion while shrink is running.
thanks!
June 15, 2009 - 6:25 pm UTC
1) there is no supported, documented way to do that.
2) ditto
Delete based on ROWID
Deepak, October 05, 2009 - 4:38 am UTC
Hi Tom,
Need your help in understanding the concept.
We have a range-hash partitioned table containing 120 million rows.
The table has local partition indexes. Our application tries to delete individual rows from the table using ROWIDs. e.g., DELETE t1 WHERE rowid='<rowid>';
When I verified the execution plan for these delete statements I found that Oracle is doing "index full scan".
DELETE STATEMENT
DELETE
PARTITION RANGE ALL
PARTITION HASH ALL
INDEX FULL SCAN MO1_NDX INDEX. I need your help in understanding this behaviour. When it comes deleting rows based on ROWIDs on an indexed table how the entries from the B-Tree index gets deleted?
October 08, 2009 - 6:42 am UTC
what you say does not correspond with what I know - you'll need to provide A LOT MORE DETAIL - like a create and delete statement that we all can run to see what you see. If you do that - make sure the create statement is one anyone can run (eg: reasonable number of partitions - NOT A SINGLE REFERENCE TO A TABLESPACE SINCE WE DON"T HAVE YOUR TABLESPACES and so on)
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy')),
11 PARTITION part3 VALUES LESS THAN (to_date('01-jan-2010','dd-mon-yyyy'))
12 )
13 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx1 on t(x) local;
Index created.
ops$tkyte%ORA10GR2> create index t_idx2 on t(y) local;
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( sysdate, 1, 'hello world' );
1 row created.
ops$tkyte%ORA10GR2> column r new_val r
ops$tkyte%ORA10GR2> select rowid r from t;
R
------------------
AAAsB1AAEAAAJG+AAA
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> delete from t where rowid = '&R';
old 1: delete from t where rowid = '&R'
new 1: delete from t where rowid = 'AAAsB1AAEAAAJG+AAA'
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 2776708790
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 42 | 1 (0)| 00:00:01 | | |
| 1 | DELETE | T | | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T | 1 | 42 | 1 (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------
ops$tkyte%ORA10GR2> set autotrace off
Indexes in table shrink operation
HK, June 06, 2012 - 9:21 am UTC
If we do "alter table <table> shrink space" indexes are maintained also. Rows of the table are moved, so also indexes must me somehow "rebuilded" automatically during this operation.
My question is how indexes are after table shrink (without cascade). Indexes are valid and useful of course but are they in worse shape than before the table shrink. If this is the case we should rebuild indexes after table shrink, right?
Or is it usually better to use shrink with cascade option which is online operation also in standard edition. Should we ever consider to rebuild indexes even we do shrink table cascade?
June 06, 2012 - 11:30 am UTC
not 'rebuilt', just maintained.
the shrink conceptually reads the table from the bottom up. When it finds the last row, it will delete it and do a positioned insert of that row at the top of the table - as long as there is free space above the last row. So, the index will get a delete+insert operation - just like it would for any update (think of this like an update to the rowid).
The indexes might be
in worse shape
the same shape
better shape
after the operation, it depends. You probably do not need to rebuild indexes.
there are no online capabilities in SE.