RE: Rebuilding Index Organised Tables (IOTs)

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM

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: ListGuru_at_fatcity.com (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).