Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted February 23, 2015


How to Help Your Business Become an AI Early Adopter

My Oracle Cup Overflows

By David Fitzjarrell

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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.