| Lesson 4 | Creating an index-organized table |
| Objective | Create an index-organized table. |
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.
IOT Syntax
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.
IOT 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.
Creating an Index-Organized Table
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
Partition IOT
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
This error clearly indicates that composite partitioning is not supported. For more information on the features of IOT''s and their limitations, see the Oracle Database Administrator's Guide for your release of the database.
Creating Index Organized Table - Exercise
