RE: Reorganizing columns in an Index Organized Table

The default is to create a heap table, yes, but you can specify "organization index" in the CTAS statement to get an IOT.

Search AskTom for specific examples.

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

Ours is the age that is proud of machines that can think and suspicious
of men who try to.  --H. Mumford Jones, 1892-1980


-----Original Message-----
From: Nirmalya Das [mailto:nirmalya_at_hln.com] 
Sent: Tuesday, August 08, 2006 1:12 PM
To: Bobak, Mark
Cc: oracle-l_at_freelists.org
Subject: RE: Reorganizing columns in an Index Organized Table

If I am not mistaken CTAS will create a HEAP table....not an IOT.

Quoting "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>:



> You might try CTAS.  That way, you'll be able to do nologging direct 

> load.

>

>

> --

> Mark J. Bobak

> Senior Oracle Architect

> ProQuest Information & Learning

>

> Ours is the age that is proud of machines that can think and 

> suspicious of men who try to.  --H. Mumford Jones, 1892-1980

>

>

> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nirmalya Das

> Sent: Tuesday, August 08, 2006 12:54 PM

> To: oracle-l_at_freelists.org

> Subject: Reorganizing columns in an Index Organized Table

>

> Hi All,

>

> I am in the process of reorganizing an IOT columns so the active 

> columns (which has been added later) can be grouped together and non 

> active (future

> enhancement) columns can be kept in the overflow section of the IOT.

>

> Environment:

>

> RDBMS: Oracle 10.1.0.4 Standard Edition OS : RHEL ES 4

>

> This is what I am doing:

>

> 1. Create a new Index Organized Table

> 2. Insert into <NEWIOT> Select (column1...column80) from the <OLDIOT>

3.


> Drop table <OLDIOT> 4. Rename table <NEWIOT> to <OLDIOT>

>

> Is there a better procedure that anyone can suggest?

>

> TIA,

>

> Nirmalya

> --

> http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l