Problem solve Get help with specific problems with your technologies, process and projects.

Creating an Index Organized Table (IOT) using CREATE TABLE AS SELECT (CTAS)

This tip describes how to create an IOT from an existing table using CTAS, which may be a little different from what you are used to.

This tip originates from a response posted by an Oracle support professional, Giribabu Bhamidipati, in the PL/SQL...

forum on Metalink.

This script has been tested on Oracle 8.1.7 for a data migration. Data was taken from multiple tables and combined into a final table. We used CREATE TABLE AS SELECT (CTAS) to create intermediate tables, which were joined to make a final table. Because of the final join, it was necessary to make the intermediate tables Indexed Organized Table (IOT). We used CTAS for speed.

The syntax for creating an IOT from an existing table using CTAS may be a little different from what you are used to when creating a table. The following is an example:

 CREATE TABLE iot_emp ( employee_id PRIMARY KEY, first_name, last_name ) ORGANIZATION INDEX TABLESPACE mytable AS SELECT employee_id, first_name, last_name FROM emp;

When you use CTAS to create an IOT, you do not specify the column data types.

The script above will work for a single column primary key. If you need to create a multicolumn primary key, use the following syntax:

 CREATE TABLE iot_emp ( employee_id, first_name, last_name, CONSTRANT pk_iot_emp PRIMARY KEY(employee_id, last_name) ) ORGANIZATION INDEX TABLESPACE mytable AS SELECT employee_id, first_name, last_name FROM emp;

As in the first example, you do not include the column data types. This format can also be used for a single column primary key.

 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.


Dig Deeper on Using Oracle PL-SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close