Hi Jonathan,
Even this may not be the case (in my opinion I could be mistaken, my
respects for every one).
As you know any index is made of branching blocks and leaf blocks.
When we build an index, it's mainly to get rid of deleted rows/entries in
the leaf blocks and reclaim that space. Also reorganize the branching blocks
(index tree).
The way I understand IOT it's made of PK index (ONLY THE BRANCHING BLOCKS
PART) plugged into the table segment which could be considered as the leaf
blocks.
Moving the IOT will rebuild only the branching blocks or the tree part of
the index.
But the table segment or the leaf blocks will not be changed and will be
moved as it's (including deleted entries) to the new place.
Overflow is a different issue.
Again any feedback and corrections will be appreciated.
Unfortunately do not have time now to test what I said.
Regards,
Waleed
-----Original Message-----
Sent: Thursday, March 21, 2002 12:08 PM
To: Multiple recipients of list ORACLE-L
Correct, and apologies, it's too easy to give
the quick and simple answer.
If you want to rebuild the whole thing, without
changing tablespaces, then:
alter table iot_tab move overflow;
You can't move the overflow with an online move,
of course (except in Oracle 9, I expect).
If you also have LOB columns you may have to
add further details to make those move too, such as
alter table iot_lob_tab move lob(my_lob) store as my_lob_seg;
which would rebuild the index layer, overflow layer, and
lobsegment layers.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 20 March 2002 22:41
|It seems to me that alter table <iot_table> move --> just rebuilds
the the
|branching part of the index.
|So if this is the case---> export & import or create table <iot> as
... are
|the best way to do the real job.
|
|Corrections and feedback are welcomed.
|
|Waleed
|
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
