Databases Reference
In-Depth Information
optionally the equivalent
ALL
or
DBA
views), and then issue the
ALTER TABLE...MOVE PARTITION
command
in order to move each partition of an IOT. See the following example:
SQL> select partition_name
2 from user_ind_partitions
3* where index_name = 'EMPLOYEES_IOT_PK';
PARTITION_NAME
------------------------------
P1990
...
P1999
P2000
PMAX
SQL> ALTER TABLE employees_iot MOVE PARTITION p1990;
Table altered.
You must rebuild the IOT with an
ALTER TABLE
command. If you attempt to rebuild an IOT via the
primary key index, you will receive the following error:
SQL> alter index employees_iot_pk rebuild;
alter index employees_iot_pk rebuild
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
Reporting on Index-Organized Tables
Getting information from the Oracle data dictionary on index-organized tables is straightforward.
Look at the following query, which gives the fundamental information regarding the IOT's within your
database:
SQL> select i.table_name, i.index_name, i.index_type, i.pct_threshold,
2 nvl(column_name,'NONE') include_column
3 from user_indexes i left join user_tab_columns c
4 on (i.table_name = c.table_name)
5 and (i.include_column = c.column_id)
6 where index_type = 'IOT - TOP';
TABLE_NAME INDEX_NAME INDEX_TYPE PCT_THRESHOLD INCLUDE_COLUMN
--------------- ------------------ ---------- ------------- ------------------
LOCATIONS_IOT LOCATIONS_IOT_PK IOT - TOP 50 NONE
EMPLOYEES_PART EMPLOYEES_PART_PK IOT - TOP 50 NONE
COUNTRIES COUNTRY_C_ID_PK IOT - TOP 50 NONE
EMPLOYEES_IOT EMPLOYEES_IOT_PK IOT - TOP 40 SALARY
