I’ve been rewriting a utility that moves certain tables and their indexes from their old tablespaces (many, many old tablespaces) to a new one. This week I ran into a new (to me) error message:
SQL Error: ORA-25191: cannot reference overflow table of an index-organized table
My utility wasn’t providing enough special handling for index-organized tables (IOTs), which were throwing up this error (and a few others). So what’s an index-organized table, and why are they so difficult to work with?
A refresher: a SQL database is relational, and rows are stored unsorted; Oracle calls standard tables heap-organized tables. For a given SQL query, unless an ORDER BY clause is used, results can be returned in any order. Oracle also has IOTs, which are tables that are instead stored in a B*Tree index structure. This structure stores all columns of the table, sorted by primary key; because this is both the table and the index, accessing rows is much faster and less storage space is needed than for a heap-organized table with a separate index.
There are, however, exceptions to the rule that everything is stored in one place. If some columns are rarely used, they can be moved into an overflow area (which could be in a different tablespace) to speed up access to the main segment. Additionally, any columns that cannot fit into a single block must go into the overflow area.
When an IOT is created using an overflow segment, Oracle will automatically create a second table with a name like SYS_IOT_OVER_XXXXX. Trying to use this table directly results in the above error.
If I query dba_tables, I see that SYS_IOT_OVER_XXXXX has an IOT_TYPE of IOT_OVERFLOW (confirming that it’s an overflow table, as expected) and the IOT_NAME column gives me the name of the original table that this is holding overflow from. I can then resolve the issue by moving the IOT, specifying both the regular and overflow tablespaces:
alter table SCHEMA.”TABLE_NAME” move tablespace TABLESPACE_NAME overflow tablespace OVERFLOW_TABLESPACE_NAME;
Now Oracle moves the overflow table along with the heap-organized table, and everyone is happy.