Databases Reference
In-Depth Information
The B-tree structure of an index-organized table is based on the primary key values. If you don't
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 can't 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
