Databases Reference
In-Depth Information
clause. In the table example, let's say you determine through user interviews that the most queried
columns on your
EMPLOYEES
table will be the
JOB_ID, DEPARTMENT_ID
, and
MANAGER_ID
column. The initial
DDL
placed
the DEPARTMENT_ID
and
MANAGER_ID
columns in the overflow segment.
Based on the user interviews, it may be beneficial to move these two columns
above
the
INCLUDING
clause and possibly shift some other columns below the
INCLUDING
clause. It may also mean, based on
the necessary queries against the
EMPLOYEES
table, that you decide not to create an overflow segment for
the
EMPLOYEES
table. Creation of the overflow segment, and which columns to place there, should be
done after careful analysis based on the proposed usage of the table columns.
If you choose to specify an
INCLUDING
clause within the DDL for an IOT, you must specify an
OVERFLOW
area, else you will receive the following error:
create table employees_iot
*
ERROR at line 1:
ORA-25186: INCLUDING clause specified for index-organized table without
OVERFLOW
Also, the Oracle data dictionary can become cluttered with entries for overflow areas for index-
organized table that have been dropped. Recyclebin objects are normally seen in the
DBA_SEGMENTS
view,
but for IOT overflow segments, you can see them in the
USER_TABLES
view (or appropriate ALL or DBA
view), including those which have been dropped. See the following query and results as an example:
SQL> select table_name, iot_type from user_tables
2 where iot_type like '%IOT%';
TABLE_NAME IOT_TYPE
------------------------------ ------------
SYS_IOT_OVER_77689 IOT_OVERFLOW
SYS_IOT_OVER_77692 IOT_OVERFLOW
SYS_IOT_OVER_77697 IOT_OVERFLOW
EMPLOYEES_IOT IOT
Therefore, purge the recyclebin to get rid of superfluous overflow entries.
SQL> purge recyclebin;
Recyclebin purged.
After you purge the recyclebin, the dropped overflow objects no longer show in the data dictionary.
SQL> select table_name, iot_type from user_tables
2 where iot_type like '%IOT%''
TABLE_NAME IOT_TYPE
------------------------------ ------------
SYS_IOT_OVER_77697 IOT_OVERFLOW
EMPLOYEES_IOT IOT
