This article explains how to calculate the space used by an IOT (Index Organized Table).

How To Determine the Space Allocated to an IOT
----------------------------------------------

A. In general, to get the amount of space allocated or consumed by any segment (table, index, etc.), you query the DBA_SEGMENTS or DBA_EXTENTS view,
specifying the segment_name as the name of the object.

Example:

To know the amount of space used by the table SCOTT.EMP:

SQL> select segment_name, segment_type, bytes, blocks
2 from dba_segments
3 where segment_name = 'EMP' and owner='SCOTT';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------- ------------ ---------- --------
EMP TABLE 65536 8

B. Creating an IOT generates 2 segments:

--> an index named SYS_IOT_TOP_
--> optionally an overflow table named SYS_IOT_OVER_

The logical TEST_CHECK iot has no space allocated to it, but the SYS_IOT_OVER_27372 table and SYS_IOT_TOP_27372 index both have space allocated.

Example:

SQL> create table TEST_CHECK (x number primary key, y number)
2 ORGANIZATION INDEX
3 tablespace tools pctthreshold 20
4 OVERFLOW tablespace users;

SQL> select segment_name, segment_type, bytes, blocks
2 from dba_segments
3 where segment_name = 'TEST_CHECK' and owner='SCOTT' ;
no rows selected

SQL> select object_name, object_id
2 from dba_objects
3 where object_name='TEST_CHECK' and owner='SCOTT';

OBJECT_NAME OBJECT_ID
------------------------ ---------------
TEST_CHECK 27372

SQL> select segment_name, segment_type, bytes, blocks
2 from dba_segments
3 where segment_name like '%27372%';

NAME SEGMENT_TYPE BYTES BLOCKS
------------------- --------------- ---------- ----------
SYS_IOT_OVER_27372 TABLE 65536 8
SYS_IOT_TOP_27372 INDEX 65536 8

SQL> select table_name, iot_type, iot_name, tablespace_name
2 from dba_tables
3 where table_name = 'TEST_CHECK'
4 or (iot_name = 'TEST_CHECK' and iot_type = 'IOT_OVERFLOW');

TABLE_NAME IOT_TYPE TABLESPACE_NAME IOT_NAME
------------------ ------------- ---------------- -----------
SYS_IOT_OVER_27372 IOT_OVERFLOW USERS TEST_CHECK
TEST_CHECK IOT

SQL> SELECT index_name, index_type, tablespace_name, table_name
2 FROM dba_indexes
3 where table_name = 'TEST_CHECK';

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME
----------------- -------------- --------------- --------------
SYS_IOT_TOP_27372 IOT - TOP TOOLS TEST_CHECK

C. Computation

A = IOT Index ( SYS_IOT_TOP_27372)
+ B = OVERFLOW segment( SYS_IOT_OVER_27372)
----
T = Total Physical Storage Space used by an IOT

The total space used by an IOT is as follows ==>

T = A + B

T = 8 + 8 = 16 Oracle Blocks.[@more@]

ITPub博客

How To Determine The Amount of Space Used by an IOT

最新文章