索引组织表(index organized table ,IOT)

索引组织表(index organized table ,IOT)

默认情况下所有的表都是堆组织表,对表中的记录不进行排序。堆组织表通过rowid 来访问(定位)表中的记录。IOT 使用b-tree index 的结构存储记录。逻辑上按照主键排序,和正常的主键索引不同的是,主键索引仅仅是存储定义列的值。IOT index 存储所有IOT表中所有的列,并按照主键排序。索引和表合二为一,存储在同一个数据库对象中。表中记录的访问也不是通过传统的rowid来现实,而是通过主键来访问。

创建IOT

IOT中需要存在主键,并且在创建IOT的语句中使用organization index 子句。

创建一个堆组织表,并且给定主键约束的名称方便后面的查找和标识。

SQL> create table test_iot 

  2  ( id number(3),name varchar2(12),

  3  constraints test_iot_id#_pk primary key (id))

  4  organization index;

Table created.

查看刚才创建的IOT中的索引。

SQL> select index_name,index_type,table_name,table_type

  2  from user_indexes

  3  where table_name = 'TEST_IOT';

INDEX_NAME      INDEX_TYPE      TABLE_NAME      TABLE_TYPE

--------------- --------------- --------------- -----------

TEST_IOT_ID#_PK IOT - TOP       TEST_IOT        TABLE

堆组织表中的索引与主键的约束同名。下面这个查询进一步说明堆组织表中索引所在的列。

SQL> select index_name,table_name,column_name

  2  from user_ind_columns

  3  where table_name = 'TEST_IOT';

INDEX_NAME      TABLE_NAME      COLUMN_NAME

--------------- --------------- --------------------

TEST_IOT_ID#_PK TEST_IOT        ID

查看因为创建堆组织表产生的数据库对象。

QL> select object_id,object_name,object_type 

  2  from user_objects

  3  order by object_id desc;

 OBJECT_ID OBJECT_NAME               OBJECT_TYPE

---------- ------------------------- -------------------

     69350 TEST_IOT_ID#_PK           INDEX

     69349 TEST_IOT                  TABLE

查看数据库分配给堆组织表的segment。如果我们给主键约束起了名字

那么堆组织表的segment name 和主键约束的名称相同,否则会使用

系统默认的段名 SYS_IOT_TOP_<object_id>

SQL> select segment_name,segment_type

  2  from user_segments

  3  where segment_name like 'T%';

SEGMENT_NAME              SEGMENT_TYPE

------------------------- ------------------

TEST_IOT_ID#_PK           INDEX

堆组织表是没有 table segment 的。

IOT 的优势

在SQL语句的查询条件中经常需要使用到表中的主键这种情况下使用IOT可以实现更好的性能更快的访问速度。另外索引和表合二为一,只用一个segment 并且不需要存储rowid,只存储一遍primary key values 所有可以节省存储开销。

关于overflow area

如果在堆组织表中的一些列是不经常访问的,或者记录很长应该考虑使用overflow area.把这部分不常用的数据存放在overflow segment 中。可以使用index segment 比较小,从而在检索index segment 中的数据时性能更好。注:primary key values  总是存储在index segment中的,no-key values 可以存储在index segment 中也可以存储在overflow segment 中。index segment 中的row通过rowid 来连接到overflow segment 中的row.所以select 与DML语句不能直接的访问overflow 中的数据。overflow segment 的类型是table not index.

创建带overflow area 的IOT

首先来认识两个很重要的overflow 子句:

overflow pctthreshold 子句 : 指定index block 中保留的空间的百分比。该百分比需要设置的合理,以便能够有足够的空间来存放primary key values.其他的列,如果存储在index block 中操作了指定的阀值,将不会存储在index block 中,而是存储在overflow segment 中。语法格式是:

pctthreshold threshold  

threshold in(1...50),默认值是50.

overflow including 子句:指定那些列应该存储在index block 中。

语法格式是:

including column_name

这里的column_name 可以是最后一个primary key 列,也可以是no primary key 列。

创建一个带overflow area 的堆组织表,其中id,first_name,last_name 存放在index block 中,其他的列存放在overflow segment 的block中。

SQL> create table test_iot_info

  2  ( id number (5),

  3    first_name varchar2 (20),

  4    last_name varchar2 (20),

  5    major varchar2 (30),

  6    current_credits number(3),

  7    grade varchar2(2),

  8    constraints test_iot_info_id#_pk primary key (id))

  9    organization index 

 10    overflow including last_name;

Table created.

查看刚才新建的IOT的索引信息

SQL> select index_name,index_type,table_name

  2  from user_indexes

  3  where table_name = 'TEST_IOT_INFO';

INDEX_NAME      INDEX_TYPE      TABLE_NAME

---------------  --------------- ------------------------------

TEST_IOT_INFO_ID#_PK IOT - TOP       TEST_IOT_INFO

SQL> select index_name,table_name,column_name

  2  from user_ind_columns

  3  where table_name = 'TEST_IOT_INFO';

INDEX_NAME      TABLE_NAME                     COLUMN_NAME

--------------- ------------------------------ ---------------

TEST_IOT_INFO_ID#_PK TEST_IOT_INFO                  ID

查看由创建IOT 所生成的对象,这里因为使用了overflow 所以

多出了一个 SYS_IOT_OVER_69353. overflow 的命名格式是

 SYS_IOT_OVER_<table object_id>.

SQL> select object_id,object_name,object_type 

  2  from user_objects

  3  order by object_id desc;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

---------- ------------------------------ -------------------

     69355 TEST_IOT_INFO_ID#_PK           INDEX

     69354 SYS_IOT_OVER_69353             TABLE

     69353 TEST_IOT_INFO                  TABLE

查看由创建IOT 表生成的segment。

SQL> select segment_name,segment_type

  2  from user_segments

  3  order by segment_name;

SEGMENT_NAME              SEGMENT_TYPE

------------------------- ------------------

SYS_IOT_OVER_69353   TABLE

TEST_IOT_INFO_ID#_PK INDEX

注意 IOT 表的overflow segment name 与 对象名相同。

时间: 2024-10-23 20:05:33

索引组织表(index organized table ,IOT)的相关文章

oracle点知识8——索引组织表

关于索引的入门参照博客:http://blog.csdn.net/changyanmanman/article/details/7097318 索引组织表(index organized table, IOT)就是存储在一个索引结构中的表.存储在堆中的表是无组织的(也就是说,只要有可用的空间,数据可以放在任何地方),IOT中的数据则按主键存储和排序.对你的应用来说,IOT表和一个"常规"表并无二致. IOT有什么意义呢?使用堆组织表时,我们必须为表和表主键上的索引分别留出空间.而IOT

Oracle中如何管理索引组织表

索引组织表(IOT)有一种类B树的存储组织方法.普通的堆组织表是以一种无序的集合存储.而IOT中的数据是按主键有序的存储在B树索引结构中.与一般B树索引不同的的是,在IOT中每个叶结点即有每行的主键列值,又有那些非主键列值. 在IOT所对应的B树结构中,每个索引项包括<主键列值,非主键列值>而不是ROWID,对于普通堆组织表,oracle会有对应的索引与之对应,且分开存储.换句话说,IOT即是索引,又是实际的数据. 索引组织表(IOT)不仅可以存储数据,还可以存储为表建立的索引.索引组织表的数

oracle 索引组织表的深入详解_oracle

索引组织表(IOT)不仅可以存储数据,还可以存储为表建立的索引.索引组织表的数据是根据主键排序后的顺序进行排列的,这样就提高了访问的速度.但是这是由牺牲插入和更新性能为代价的(每次写入和更新后都要重新进行重新排序).索引组织表的创建格式如下: 复制代码 代码如下: create table indexTable(   ID   varchar2 (10),   NAME varchar2 (20),   constraint pk_id primary key (ID)   )   organi

[20151008]索引组织表上创建BITMAP索引.txt

[20151008]索引组织表上创建BITMAP索引.txt --IOT 是一种特殊的索引结构,使用它能够解决特定场合的应用问题,但是在许多应用中很少使用,更多的是使用堆表. --我偏向使用静态以及"只读"的小表. --以前写过一个系列,一直没有很好理解在IOT表上建立位图索引,最近再研究看看; --我当时不理解为什么mapping table与iot表对应的块为什么不一致? 1.建立测试环境: SCOTT@test> @ver1 PORT_STRING             

ORACLE索引组织表讨论

本文只代表作者观点,如有错误请指正 关于索引组织表本文主要讨论以下几个方面 1.什么是索引组织表 2.索引组织表的关键特性 3.如果建立一个合适的索引组织表 4.什么事逻辑ROWID以及物理猜(Physical Guesses) 5.从内部结构进行分析和证明这些观点 一般的情况下索引是和表分离的SEGMENT,索引的行记录的是索引键值和ROWID,而在索引组织表中就整个表就是一个索引,索引的页节点记录的并非 键值和ROWID而记录是整个数据行,这里和MYSQL INNODB的表非常相像,MYSQ

[20151010]物化视图的物理表建成索引组织表

[20151010]物化视图的物理表建成索引组织表.txt --论坛有人问这个问题,自己测试看看是否可行? http://www.itpub.net/thread-1939707-1-1.html 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                        

关于索引组织表IOT

摘自oracle高效设计: create table t( a int, b int, c int, primary key (a,b))organization index; create index t_idx_c on t(c); set autotrace traceonly explain set autotrace traceonly explainselect a, b from t where c = 55; 执行计划-------------------------------

[20120228]IOT索引组织表相关信息的学习.txt

[20120228]IOT索引组织表相关信息的学习.txt IOT 是一种特殊的索引结构,使用它能够解决特定场合的应用问题,但是在许多应用中很少使用,更多的是使用堆表.我仅仅在生产系统中对3个表使用,我偏向使用静态以及"只读"的小表. 如果IOT表存在除主键外的第2索引,如果使用它存在物理猜"physical guess",我以前的理解一直是第2索引记录了主键信息,通过这个信息就可以定位IOT表中对应的数据,一直没有很好的测试与理解.我最近也看了两个链接,介绍了IO

MySQL覆盖索引(Covering Index)

MySQL覆盖索引(Covering Index) mysql高效索引之覆盖索引 概念 如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作 判断标准 使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询   注意 1.覆盖索引也并不适用于任意的索引类型,索引必须存储列的值 2.Hash 和full-text索引不存储值,因