Index-organized tables, or IOTs, combine a primary key index and a table into a single structure. They allow for fast data access since the table is organized and accessed much like a standard B-tree index. To make an IOT even more efficient many of the non-key columns can be stored in an overflow segment; doing this preseves the dense packing for the key columns. The percent of the row, including the key, that is to remain in the index segment is determined by the PCTTHRESHOLD setting; the default is 50 and if the maximum size of the row exceeds this default limit the IOT is not created. The columns to include in the index segment can be specified by the INCLUDING clause; when this clause is not used Oracle puts the key and the non-key columns that can fit within the threshold limit in the index block and moves the remaining non-key columns to the overflow segment. There are times when it may be necessary to move an IOT to a new tablespace, and with it the overflow segment. That’s a farily simple task with a not-so-common syntax; let’s see how that can be done.
Moving tables to a different tablespace is nothing new:
alter table emp move tablespace new_data;
Unfortunately it doesn’t include any overflow tablespaces in use. Remember that both LOB segments and IOTs can have overflow segments defined. To move the overflow segment to a new tablespace there is an option:
alter table emp move tablespace new_data overflow tablespace ovrflow_ts;
This allows us to move both the IOT and the overflow segment. You can’t move the overflow segment by itself, as the following example shows:
SQL> create table driver(
2 driver_name varchar2(40),
3 other_stuff varchar2(100),
4 recnum number,
5 constraint driver_pk
6 primary key(recnum)
7 )
8 organization index
9 tablespace indx
10 pctthreshold 20
11 overflow tablespace indx;
Table created.
Elapsed: 00:00:00.01
SQL>
SQL> begin
2 for i in 1..1000 loop
3 insert into driver
4 values (TO_CHAR ( TO_DATE ( TO_CHAR ( i, '99999999999') , 'J'), 'JSP'), 'blather and junk '||i, i);
5 end loop;
6
7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL>
SQL> column table_name new_value ot_name
SQL>
SQL> select table_name, iot_name, iot_type, tablespace_name
2 from user_tables
3 where iot_type = 'IOT_OVERFLOW';
TABLE_NAME IOT_NAME IOT_TYPE TABLESPACE_NAME
------------------------------ ------------------------------ ------------ ------------------------------
SYS_IOT_OVER_19430 DRIVER IOT_OVERFLOW INDX
Elapsed: 00:00:00.05
SQL>
SQL> alter table &ot_name move tablespace users;
old 1: alter table &ot_name move tablespace users
new 1: alter table SYS_IOT_OVER_19430 move tablespace users
alter table SYS_IOT_OVER_19430 move tablespace users
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
Elapsed: 00:00:00.00
SQL>
Using the alter table syntax provided earlier, both the IOT and the overflow segment can be successfully relocated:
SQL> alter table driver move tablespace users overflow tablespace users;
Table altered.
Elapsed: 00:00:00.02
SQL>
SQL> select table_name, iot_name, iot_type, tablespace_name
2 from user_tables
3 where iot_type = 'IOT_OVERFLOW';
TABLE_NAME IOT_NAME IOT_TYPE TABLESPACE_NAME
------------------------------ ------------------------------ ------------ ------------------------------
SYS_IOT_OVER_19430 DRIVER IOT_OVERFLOW USERS
Elapsed: 00:00:00.00
SQL>
Again, this works for both IOTs and heap tables containing LOB segments with overflow segments defined.
Creating an IOT takes planning, so it’s not very likely that one will need to be moved for storage reasons. It is nice to know how to do that, though, in the event such a move is necessary.
