DBA_IOTS: View to manage Index Organized Tables

An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.

The structure of an index-organized table provides the following benefits:

  • Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.
  • Fast range access on the primary key because the rows are clustered in primary key order.
  • Lower storage requirements because duplication of primary keys is avoided. They are not stored both in the index and underlying table, as is true with heap-organized tables.

Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:

  • Key compression
  • Overflow storage area and specific column placement
  • Secondary indexes, including bitmap indexes.

Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. Queries and DML on an orders table used in electronic order processing are predominantly primary-key based and heavy volume causes fragmentation resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated.

Index-organized tables are suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. A fundamental component of an internet search engine is an inverted index that can be modeled using index-organized tables.

These are but a few of the applications for index-organized tables.

From a database (storage) perspective there is no placeholder for these type of objects.
There are views for most – if not all – type of objects (tables, indexes, constraints, lobs, etc …) but not for IOT’s.
You may find the index part of the IOT in the DBA_INDEXES view and the overflow part in the DBA_TABLES view while the top IOT segment will not be found in any of these though but in the DBA_OBJECTS view.

The following query is delivered as is to provide a single point of access for IOT information within database to provide storage information for IOT’s.

-----------------------           DBA_IOTS           -----------------------
Create Or Replace View DBA_IOTS
(
  OWNER                                                   ,
  IOT_NAME                                                ,
  OBJECT_ID                                               ,
  DATA_OBJECT_ID                                          ,
  PARTITION_POSITION                                      ,
  PARTITION_NAME                                          ,
  OVERFLOW_OBJECT_ID                                      ,
  OVERFLOW_DATA_OBJECT_ID                                 ,
  OVERFLOW_NAME                                           ,
  OVERFLOW_TS                                             ,
  OVERFLOW_FILE                                           ,
  OVERFLOW_BLOCK                                          ,
  INDEX_OBJECT_ID                                         ,
  INDEX_DATA_OBJECT_ID                                    ,
  INDEX_NAME                                              ,
  INDEX_TS                                                ,
  INDEX_FILE                                              ,
  INDEX_BLOCK
)
As
-------------------------- IOTS (Non Partitioned) --------------------------
Select u.name                                             ,
       o.name                                             ,
       o.obj#                                             ,
       o.dataobj#                                         ,
       null                                               ,
       null                                               ,
       t.bobj#                                            ,
       t1.dataobj#                                        ,
       o1.name                                            ,
       t1.ts#                                             ,
       t1.file#                                           ,
       t1.block#                                          ,
       i.dataobj#                                         ,
       o2.dataobj#                                        ,
       o2.name                                            ,
       i.ts#                                              ,
       i.file#                                            ,
       i.block#
From   tab$      t                                        ,
       tab$      t1                                       ,
       ind$      i                                        ,
       obj$      o                                        ,
       obj$      o1                                       ,
       obj$      o2                                       ,
       user$     u
Where  t.obj#                                  = i.bo#
And    t.bobj#                                 = t1.obj# (+)
And    o.obj#                                  = i.bo#
And    o.owner#                                = u.user#
And    bitand(t.property,64)                   = 64
And    t.bobj#                                 = o1.obj# (+)
And    o2.obj#                                 = i.dataobj#
Union All
-------------------------- IOTS   (Partitioned)   --------------------------
Select u.name                                             ,
       o.name                                             ,
       o.obj#                                             ,
       o.dataobj#                                         ,
       tp.part#                                           ,
       o3.subname                                         ,
       To_number(Decode(t.bobj#, null, null, tp.obj#))    ,
       tp.dataobj#                                        ,
       Decode(t.bobj#, null, null, o1.name)               ,
       To_number(Decode(t.bobj#, null, null, tp.ts#))     ,
       To_number(Decode(t.bobj#, null, null, tp.file#))   ,
       To_number(Decode(t.bobj#, null, null, tp.block#))  ,
       ip.obj#                                            ,
       ip.dataobj#                                        ,
       o2.name                                            ,
       ip.ts#                                             ,
       ip.file#                                           ,
       ip.block#
From   obj$      o                                        ,
       obj$      o1                                       ,
       obj$      o2                                       ,
       obj$      o3                                       ,
       tab$      t                                        ,
       tabpartv$ tp                                       ,
       ind$      i                                        ,
       indpartv$ ip                                       ,
       user$     u
Where  o.obj#                                  = t.obj#
And    Decode (t.bobj#, null, t.obj#, t.bobj#) = tp.bo#
And    i.bo#                                   = o.obj#
And    ip.bo#                                  = i.obj#
And    ip.part#                                = tp.part#
And    o.owner#                                = u.user#
And    o1.obj#                                 = tp.bo#
And    o2.obj#                                 = i.obj#
And    o3.obj#                                 = tp.obj#
And    bitand(t.property,64)                   = 64
And    bitand(t.trigflag, 1073741824)         != 1073741824
;

-------------------------- IOTS (SubPartitioning) --------------------------
-- ORA-25198, 00000, "only range, list, and hash partitioning are supported for index-organized table"
-- // *Cause:  System, or Composite partitioning schemes are not supported yet
-- // *Action: Select a different partitioning scheme
----------------------------------------------------------------------------