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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted February 23, 2015

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

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