Creating index-organized Table

As the name implies, an index-organized table is just another variety of a standard table. To create an index-organized table, use a simple variation of the standard commands to create a table. Previously in this series, you learned how to create a table using the CREATE TABLE command. To specify that a table is an index-organized table, you only have to add two keywords at the end of the table definition:

ORGANIZATION INDEX

These words tell Oracle that the table should be created as an index-organized table. The alternative keywords are ORGANIZATION HEAP, which is the default and therefore rarely used, except for documentation purposes. Of course, because an index-organized table must have a primary key, you must also define a primary key constraint for the table. You can also create an index-organized table with the Schema Manager, as shown in the following example.

The following code will create a table called sales figures as an index-organized table:

CREATE TABLE sales_figures (store_id NUMBER, Quarter 
INTEGER, month INTEGER, amount NUMBER
CONSTRAINT pk_sales_figures PRIMARY KEY (store_id, 
quarter, month))
ORGANIZATION INDEX;

Notice that the only additional syntax you need is the ORGANIZATION INDEX keywords, because the primary key constraint has already identified the columns that will be the key for the index-organized table.
You can also create an index-organized table with Schema Manager. In the next lesson, you will learn some additional syntax options to help you create index-organized tables with larger rows.

The data definition language (DDL) for an index-organized table is very similar to the DDL for a heaporganized table. The key difference is the use of the ORGANIZATION INDEX clause, which tells Oracle you are creating an index-organized table. For example,

SQL> CREATE TABLE locations_iot
2 (LOCATION_ID NUMBER(4) NOT NULL
3 ,STREET_ADDRESS VARCHAR2(40)
4 ,POSTAL_CODE VARCHAR2(12)
5 ,CITY VARCHAR2(30) NOT NULL
6 ,STATE_PROVINCE VARCHAR2(25)
7 ,COUNTRY_ID CHAR(2)
8 ,CONSTRAINT locations_iot_pk PRIMARY KEY (location_id)
9 )
10 ORGANIZATION INDEX;
Table created.

As previously stated, you must have a primary key defined on an IOT. Since the IOT is stored in a Btree index structure, there is no physical ROWID stored with each row. That is why you must have a primary key on an IOT so that each row can be uniquely identified. The B-tree structure of an index-organized table is based on the primary key values. If you do not specify a primary key, you will get the following error:

SQL> CREATE TABLE locations_iot
2 (LOCATION_ID NUMBER(4) NOT NULL
3 ,STREET_ADDRESS VARCHAR2(40)
4 ,POSTAL_CODE VARCHAR2(12)
5 ,CITY VARCHAR2(30) NOT NULL
6 ,STATE_PROVINCE VARCHAR2(25)
7 ,COUNTRY_ID CHAR(2)
8 )
9 ORGANIZATION INDEX;
organization index
*
ERROR at line 10:
ORA-25175: no PRIMARY KEY constraint found

For the most part, index-organized tables can be partitioned just like a heap organized table. You can partition index-organized tables using the following partitioning methods: range, list, or hash partitioning. Using the LOCATIONS_IOT from the previous example, you can list partition the table by STATE_PROVINCE based on whether it is a domestic or international state province. See the following DDL:

SQL> CREATE TABLE locations_iot
2 (LOCATION_ID NUMBER(4) NOT NULL
3 ,STREET_ADDRESS VARCHAR2(40)
4 ,POSTAL_CODE VARCHAR2(12)
5 ,CITY VARCHAR2(30) NOT NULL
6 ,STATE_PROVINCE VARCHAR2(25) NOT NULL
7 ,COUNTRY_ID CHAR(2)
8 ,constraint locations_iot_pk primary key 
(location_id, state_province)
9 )
10 ORGANIZATION INDEX
11 partition by list(STATE_PROVINCE)
12 (partition p_intl values
13 ('Maharashtra','Bavaria','New South Wales', 'BE','Geneve',
14 'Tokyo Prefecture', 'Sao Paulo','Manchester','Utrecht',
15 'Ontario','Yukon','Oxford'),
16 partition p_domestic values 
('Texas','New Jersey','Washington','California'));
Table created.

You cannot use composite partitioning in relation to index-organized tables. See this DDL snippet, where you are attempting to create a composite range-list partitioned table:

SQL> CREATE TABLE locations_iot
2 ...
17 organization index
18 partition by range(hire_date)
19 subpartition by list( DEPARTMENT_ID)
20 subpartition template
21 (SUBPARTITION JOB10 VALUES ('10')
22 ,SUBPARTITION JOB20 VALUES ('20')
23 ,SUBPARTITION JOB30 VALUES ('30')
24 ,SUBPARTITION JOB40 VALUES ('40')
25 ,SUBPARTITION JOB50 VALUES ('50')
26 ,SUBPARTITION JOB60 VALUES ('60')
27 ,SUBPARTITION JOB70 VALUES ('70')
28 ,SUBPARTITION JOB80 VALUES ('80')
29 ,SUBPARTITION JOB90 VALUES ('90')
30 ,SUBPARTITION JOB100 VALUES ('100')
31 ,SUBPARTITION JOB110 VALUES ('110')
32 (
33 partition p1990 values less than ('1991-01-01'),
...
45 );
subpartition template
*
ERROR at line 20:
ORA-25198: only range, list, and hash partitioning 
are supported forindex-organized table

Question: When is the Oracle error ORA-25198 initiated?
The Oracle error ORA-25198 is initiated under specific conditions related to the improper use or specification of an Index Organized Table (IOT). This error specifically occurs when there is an attempt to create a freelist group for an Index Organized Table. Oracle does not support freelist groups for IOTs, as the storage structure and management of IOTs differ fundamentally from those of heap-organized tables. In Oracle databases, an Index Organized Table stores its data entries directly within the B-tree structure of its primary key index, enabling faster access for primary key-based queries. This contrasts with the traditional heap-organized tables where data rows are stored in an unordered structure, and indexes are maintained separately.
The ORA-25198 error message typically reads as follows: "ORA-25198: Cannot specify FREELIST GROUPS for Index Organized Table." This error is triggered when a CREATE TABLE or ALTER TABLE statement includes the FREELIST GROUPS storage clause for an IOT. The FREELIST GROUPS parameter is relevant for managing free space in segments associated with heap-organized tables and is not applicable to the structure of an Index Organized Table. To resolve ORA-25198, ensure that any CREATE TABLE or ALTER TABLE statement for an Index Organized Table does not include the FREELIST GROUPS clause. Review the table definition and storage parameters to confirm they are appropriate for an Index Organized Table. If freelist management is a concern, consider other mechanisms provided by Oracle for managing space and performance for IOTs, such as using Automatic Segment Space Management (ASSM), which is more suited to the nature of IOTs and provides efficient space management without the need for manual freelist configuration.

Click the Exercise link below to practice creating an index-organized table.
Creating Index Organized Table - Exercise