How To Determine The Amount of Space Used by an IOT

最新推荐文章于 2024-02-01 11:14:21 发布

csdn_201601

最新推荐文章于 2024-02-01 11:14:21 发布

阅读量83

点赞数

本文介绍了如何计算IOT（Index Organized Table）所占用的空间。通过查询DBA_SEGMENTS或DBA_EXTENTS视图可以获取段（表、索引等）分配或消耗的空间量。创建IOT会产生两个段：一个名为SYS_IOT_TOP_的索引和一个可选的溢出表SYS_IOT_OVER_。文中提供了具体实例来说明如何确定IOT所使用的总物理存储空间。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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:

--&gt an index named SYS_IOT_TOP_
--&gt 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博客 ” ，链接：http://blog.itpub.net/82387/viewspace-1016310/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/82387/viewspace-1016310/