Register Login
Python Photoshop SAP Java PHP Android C++ Hadoop Oracle Interview Questions Articles Other

Index Organized Tables (IOTs) Interview Questions and Answers

Updated May 18, 2018

Oracle Index Organized Tables (IOTs) FAQs

1. What is an Index Organized Table (IOT)?

An Index Organized Table (IOT) is a table that is sorted internally as an index.

Other database systems such as MAXDB or DB2 use IOTs by default, whereas on Oracle, you must configure this function explicitly.

2. What are the advantages of IOTs?

An IOT offers advantages over a normal table:

  • The IOT combines a table and index in one, saving you the effort of creating an index.
  • Queries using the fields of this index result in fewer block accesses, since all of the information is already in the index blocks and an additional access to table blocks is no longer necessary. This fact means there are significant performance gains, especially when compared to indexes with a high clustering factor. The higher the number of blocks that have to be read on average from the hard disk (see also Note 832343), the more important these gains.

Related: Indexes in SAP: What are They and How to Create and Use them?

3. What are the technical attributes of an IOT?

The IOT is always defined on the basis of a PRIMARY KEY constraint. In just the same way as the UNIQUE KEY constraint, a PRIMARY KEY constraint requires unique combinations of the indexed columns. However, NULL values are not allowed in the indexed columns.

All data is stored in the index. The IOT table is in the database, but does not occupy any space in the database and is therefore not a segment.

To avoid having to store too large data records in the index, you can define an overflow segment when creating an IOT in which the last columns of long data records are stored.

If secondary indexes are defined on the IOT, the fields of the PRIMARY KEYs are always stored at the end of the secondary index entries.

Secondary indexes accessed the IOT using Logical ROWIDs that provide a "Guess" for the correct data block of a data record in the IOT.

4. What restrictions are associated with using IOTs?

Note the following restrictions when using IOTs:

  • Before you can create a PRIMARY KEY constraint, there must be one or more columns with unique value combinations that do not contain any NULL values.
  • The maximum length of a table entry is limited to slightly less than half of an index block (for example, 3215 bytes). When you try to define an IOT with table rows or index fields that are too long, errors such as ORA-01429 or ORA-01450 are generated.
  • If there is already a PRIMARY KEY constraint for the table, you cannot create another PRIMARY KEY constraint as the basis for the IOT. In this case, you must check whether the existing constraint is required at all. For example, PRIMARY KEY constraints were added to the SAP primary indices with earlier R/3 releases. However, these constraints are not required for SAP operation and can be deleted if required. With newer releases, PRIMARY KEY constraints are no longer created on SAP tables.
  • The storage requirement and the growth of the IOT may be significantly higher than for a normal table because indexes generally fragment more easily than tables.
  • Since the PRIMARY KEY columns are also included in the secondary indexes, these indexes require proportionately more space.
  • A defragmentation of the relevant index using REBUILD or COALESCE is not possible. A MOVE ONLINE can be carried out instead, however.
  • Columns of the ROWID type cannot be defined.
  • Parallel Data Manipulation Language (DML) is not possible for IOTs that are not partitioned. Therefore, for example, long unavoidable runtimes may occur with DBMS_REDEFINITION.START_REDEF_TABLE if the target object is defined as an IOT and a large number of data records must be inserted.
  • If you use IOTs on Oracle 9i and upgrade to 10g, data may become corrupt.  For more information, see document 1135589.

5. How does SAP support IOTs?

The ABAP DDIC supports the creation of IOTs based on the SAP primary index. You can convert a normal table to a primary index IOT as follows:

Transaction SE14
-> Storage Parameters
-> For new Creation
-> INDEX ORGANIZED: X

Then you can convert the table using

Transaction SE14
-> Tools
-> Force conversion

to an IOT.

Creating an IOT based on a secondary index is not supported by ABAP DDIC, but is permitted. In this case, you can expect the following restrictions:

  • Such an IOT cannot be created as part of transports.
  • Activation in an SAP system results in an ORA-02264.
  • A table conversion (for example as part of updates) cannot be executed automatically. Instead, the conversion must be manual.

6. Can the PRIMARY KEY of an IOT differ from the SAP primary index?

The PRIMARY KEY constraint underlying an IOT does not have to be the same as the primary index of the table on the SAP system. For example, if you have performance problems in the availability run, you can support important RESB accesses with MANDT and MATNR using an IOT with the PRIMARY KEY constraint using MANDT, MATNR, RSNUM, RSPOS and RSART, while the primary index only contains the columns MANDT, RSNUM, RSPOS and RSART. Note, however, that such a setup is not supported by ABAP DDIC (see above).

7. How do I create an IOT?

The simplest way to create an IOT is as follows:

CREATE TABLE
   ( ,
     CONSTRAINT PRIMARY KEY ()
   )
   ORGANIZATION INDEX ...;

You can define the primary key constraint using CONSTRAINT. The ORGANIZATION INDEX option indicates that it is an IOT, not a normal table.

If the above IOT is filled with data, these are stored directly in the index and not in the table.

8. How is an IOT accessed?

An IOT is accessed as follows, depending on the execution path used:

Full Table Scan

If Oracle decides on a "Full Table Scan", this corresponds to an Index Full Scan or an Index Fast Full Scan in the case of an IOT because the data records are stored in the form of an index.

Access using PRIMARY KEY

If the PRIMARY KEY on which the IOT is based is used for the access, this is the same as an Index Range Scan or Index Unique Scan on the IOT. There is no need to perform a final table access via ROWID because all data is already in the index.

Access using another index

The following steps occur if an index other than the PRIMARY KEY index is used for the access:

  • The index is searched (as part of an Index Range Scan or similar) for appropriate data records. Both columns from the index itself and the columns from the PRIMARY KEYs can be analyzed in this case because these are also stored in all indexes.
  • If required, a guess is used to determine a data record directly in the IOT for each suitable data record. The guess is based on a logical ROWID, but this may rendered invalid in certain situations (such as block splits, for example).
  • If the guess is not valid, the related IOT record is determined using an Index Unique Scan on the PRIMARY KEY index.

           This type of access is therefore more like a Nested Loop Join (with the PRIMARY KEY columns as join conditions between PRIMARY KEY index and other index) than a normal index table access.

9. How can I check the validity of logical ROWIDS?

The percentage of valid logical ROWIDS of a secondary index are stored in the CBO statistical value PCT_DIRECT_ACCESS in DBA_INDEXES. You can query this value for all indexes of the IOT using the following command:

SELECT INDEX_NAME, PCT_DIRECT_ACCESS
FROM DBA_INDEXES
WHERE TABLE_NAME = '';

For values close to 100 %, no action is required. For values below 90 %, the logical ROWIDS (see below) may be updated.

 Note that the values are only as current as the corresponding CBO statistics. If in doubt, create new CBO characteristics for the IOT in advance.

10. How can I update the logical ROWIDs?

Over time, more and more logical ROWIDs may become invalid, and an additional Index Unique Scan is required when a non-PRIMARY KEY index accesses the IOT. To minimize this performance overhead, the logical ROWIDs of a secondary index can be updated as follows:

ALTER INDEX UPDATE BLOCK REFERENCES;

11. How can I reorganize an IOT?

You can reorganize an IOT by executing the following command in the current operation:

ALTER TABLE MOVE ONLINE;

Note that double the space is temporarily required in the tablespace for this reorganization.

12. How can I check whether (and which) IOTs exist in the system?

Use the following SELECT to get an overview of all IOTs:

SELECT TABLE_NAME, INDEX_NAME FROM DBA_INDEXES
WHERE INDEX_TYPE = 'IOT - TOP';

 In this case, TABLE_NAME contains the name of the table, while INDEX_NAME is the name of the index that supports the PRIMARY KEY constraint and contains all the data.

13. How can I monitor IOTs?

 Since SAP is not adapted to the monitoring of IOTs, certain information such as space consumption or extent allocation must be determined directly on the database. The following options are available for this purpose ( can be determined as described in the previous question):

Determination of the space consumption of the IOT:

SELECT BYTES FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '';

Determination of the number of the extents of an IOT:

SELECT COUNT(*) FROM DBA_EXTENTS
WHERE SEGMENT_NAME = '';

14. Why does an ALTER INDEX return the error ORA-25176?

Executing an ALTER INDEX on the index of an IOT (to adjust storage parameters, for example) is not authorized. This type of attempt fails with:

ORA-25176: storage specification not permitted for primary key

If you encounter this error, you must adapt the IOT instead (using ALTER TABLE).

If R3UP encounters this problem during an upgrade, import a current version of R3UP that is no longer affected by the problem.

15. Where do IOTs SYS_JOURNAL_ and indexes SYS_IOT_TOP_... come from?

Indexes of IOTs are created under the name SYS_IOT_TOP_ ... if no explicit name was entered during the definition.

If the name of the IOT is SYS_JOURNAL_< object_id>, the IOT frame of an INDEX REBUILD ONLINE is temporarily created. If an unexpected error (for example, tablespace overflow) occurs during the rebuild with Oracle 9.2.0.3, the temporary IOT and their index are no longer tidied up correctly and remain available in the system. If you discover this type of relics, you can delete the IOT and its related index after you have made sure that the index whose rebuild has terminated exists correctly in the system. If you are no longer able to determine the index in question, you can make your conclusions as follows:

With the following statement, you can determine the affected table using the contained in the IOT name:

SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS
WHERE OBJECT_ID = ;

With the following statement, you can determine when the index rebuild terminated:

SELECT TO_CHAR(CREATED, 'dd:mm:yyyy, hh24:mi:ss') FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'SYS_JOURNAL_';

You can now search in the alert log for error entries (from which you can usually see the affected index) for the period in question (and subsequent hours).

16. How exactly is an IOT converted?

If the IOT is based on the SAP primary index, the changeover can be carried out by a coversion based on the SAP DDIC function. If the IOT is based on other columns, however, the conversion is carried out based on an BRSPACE online reorganization (Note 646681). Proceed as described in comment 5 in Note 646681 and edit the DDL script to create the table ("CREATE TABLE ..."):

Call BRSPACE:

brspace -u / -f tbreorg -t -d first

Confirm the selection menu with "c".

If BRSPACE stops with the message "BR1148I: You can check/change the DDL statements now", you must make the following changes to the generated ddl.sql script and save those changes:

  • Replace ") PCTFREE" with:

    , CONSTRAINT "~" PRIMARY KEY
    ()) ORGANIZATION INDEX
  • Remove "PCTUSED" including the number that follows.
  • If the IOT index replaces an older secondary index, you must delete the CREATE INDEX statement from the ddl.sql script.

You can then continue the BRSPACE reorganization using "c".

17. Where can I find more information about IOTs?

Further information about IOTs is available in the Oracle 9i documentation under:

Database Administrator's Guide

-> 15 Managing Tables

Recommended Posts:
×