My Oracle Cup Overflows

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> 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;
  7          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> column table_name new_value ot_name
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> 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

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> 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

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles