mysql伪列映射语句_oracle堆组织表(HOT)与索引组织的表(IOT)

weixin_39612228

于 2021-02-08 02:08:30 发布

阅读量120

点赞数

文章标签： mysql伪列映射语句

本文链接：https://blog.csdn.net/weixin_39612228/article/details/113945818

版权

一、堆组织表(HOT)

概述

执行CREATE TABLE语句时，默认得到的表类型就是堆组织表。其他类型的表结构需要在CREATE TABLE语句本身中指定它。

堆组织表中，数据以堆的方式管理。增加数据时，会使用段中找到的第一个能放下此数据的自由空间。从表中删除数据后，允许以后的INSERT和UPDATE重用这部分空间。堆(heap)是一组空间，以一种随机的方式使用。因此，无法保证按照放入表中的顺序取得数据

表定义的查询

使用DBMS_METADATA来查询这个表的定义。

select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

创建表的常用参数

可以根据需要，修改某些参数

对于ASSM(自动段空间管理)有3个重要选项，对于MSSM有5个重要选项。随着本地管理表空间的引入(推荐做法)，其余的参数已经没什么意义了。

FREELIST：仅适用于MSSM。

PCTFREE：ASSM和MSSM都适用。

PCTUSED：仅适用于MSSM。

INITRANS：ASSM 和MSSM 都适合。为块初始分配的事务槽数。如果会对同样的块完成多个并发更新，就应该考虑增大这个值。

COMPRESS/NOCOMPRESS：ASSM 和MSSM 都适合。

11g之前，选项是COMPRESS或者NOCOMPRESS，只有直接路径操作(例如CREATE TABLE AS SELECT, INSERT , ALTER TABLE T MOVE以及SQL*Loader直接路径加载)才能利用压缩。

11g之后，选项是COMPRESS FOR OLTP，COMPRESS BASIC或者NOCOMPRESS。COMPRESS FOR OLTP启用所有操作的压缩(包括直接路径和常规路径)，COMPRESS BASIC则只针对直接路径操作。

注意：单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。这些LOB块以不同的方式管理：它们总是会填入，直至达到最大容量，而且仅当完全为空时才返回FREELIST。

堆表具有的唯一优点是插入数据不需要采取任何措施，只需要顺其自然地安装插入的顺序存储，减少了插入大量数据的代价。

二、索引组织表(IOT)

基本概念：

索引组织表(IOT)不仅可以存储数据，还可以存储为表建立的索引。索引组织表的数据是根据主键排序后的顺序进行排列的，这样就提高了访问的速度。但是这是由牺牲插入和更新性能为代价的(每次写入和更新后都要重新进行重新排序)。

索引组织表(IOT)有一种类B树的存储组织方法。普通的堆组织表是以一种无序的集合存储。而IOT中的数据是按主键有序的存储在B树索引结构中。与一般B树索引不同的的是，在IOT中每个叶结点即有每行的主键列值，又有那些非主键列值。

在IOT所对应的B树结构中，每个索引项包括而不是ROWID，对于普通堆组织表，oracle会有对应的索引与之对应，且分开存储。换句话说，IOT即是索引，又是实际的数据。

IOT中，数据要根据主键有序地存储。

适合使用IOT的几种情况：

· 表完全由主键组成或者只通过主键来访问一个表。使用IOT，表就是索引，可以节约空间，提高效率。

· 通过外键访问子表，子表使用IOT。通过IOT将相同外键的子表数据物理的存储在同一个位置，查询所需要的物理I/O更少，因为数据都在同一个(几个)块上。

· 经常在主键或者或惟一键上使用BETWEEN查询。数据以某种特定的顺序物理存储，所以获取这些数据时所需的物理I/O更少。

索引组织表的创建格式

createtableindexTable(

IDvarchar2(10),

NAMEvarchar2(20),

constraintpk_idprimarykey(ID)

)

organizationindex;

注意两点：

● 创建IOT时，必须要设定主键，否则报错。

●

索引组织表实际上将所有数据都放入了索引中。

索引组织表属性

1、OVERFLOW子句(行溢出)

因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的设置有两种格式：

PCTTHRESHOLD

n：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段

INCLUDING

column_name：指定列之前的列都放入索引块，之后的列都放到溢出段

●

当行中某字段的数据量无法确定时使用PCTTHRESHOLD。

●

若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。

createtablet88(

IDvarchar2(10),

NAMEvarchar2(20),

constraintpk_idprimarykey(ID)

)

organizationindex

PCTTHRESHOLD20

overflowtablespaceusers

INCLUDINGname;

●

如上例所示，name及之后的列必然被放入溢出列，而其他列根据PCTTHRESHOLD规则。

2、COMPRESS子句(键压缩)

与普通的索引一样，索引组织表也可以使用COMPRESS子句进行键压缩以消除重复值。

具体的操作是，在organization

index之后加上COMPRESSn子句

●

n的意义在于：指定压缩的列数。默认为无穷大。

例如对于数据(1,2,3)、(1,2,4)、(1,2,5)、(1,3,4)、(1,3,5)时

若使用COMPRESS则会将重复出现的(1,2)、(1,3)进行压缩

若使用COMPRESS

1时，只对数据(1)进行压缩

选项NOCOMPRESS对索引一般都可用，它和表压缩不同，它告诉Oracle把构成主键的每个值分别存储在各个索引条目中(也就是不压缩)。

例如，主键在A、B和C列上，则A、B和C列中每一次出现的值都会物理地存储。

NOCOMPRESS反过来就是COMPRESS N，在此N是一个整数，表示要压缩的列数。这样可以避免重复值，并在块级提取“公因子”(factor out)。例如如果使用COMPRESS 2，那么A和B列上如果有重复的值，就只会存储1次。使用索引压缩会减少物理I/O，但是占用更多的CPU时间来处理索引，需要在两者之间权衡。

可以使用ANALYZE INDEX VALIDATE STRUCTURE命令来得到最优的压缩方案。这个命令会填写一个名为INDEX_STATS的动态性能视图，其中最多只包含一行，即这个ANALYZE命令最后一次执行的信息。例如：

create table iot (owner, object_type, object_name,

constraint iot_pk primary key(owner, object_type, object_name))

organization index nocompress

as select distinct owner, object_type, object_name from all_objects;

analyze index iot_pk validate structure;

select lf_blks, br_blks, used_space, opt_cmpr_count,

opt_cmpr_pctsave from index_stats;

得到结果：

上面命令的执行结果

LF_BLKS表示索引使用了348个叶子块(即数据所在的块)；

BR_BLKS表示索引使用了3个分支块(在索引结构中导航所用的块)来找到这些叶子块；

USED_SPACE表示使用的空间大约是2496647字节；

OPT_CMPR_COUNT(最优压缩数)表示最优的压缩设置为COMPRESS 2；

OPT_CMPR_PCTSAVE(最优的节省压缩百分比)表示如果设置为COMPRESS 2，就能节省大约33%的存储空间。

可以用alter table iot move compress 2;来重建IOT

溢出段(OVERFLOW子句)

索引一般在表的一个列子集上。通常索引块上的行数比表块上的行数会多很多，这对索引是有利的，否则Oracle需要花费大量的时间来维护索引，因为每个INSERT或UPDATE都可能导致索引块分解，以容纳新数据。

OVERFLOW子句允许你建立另一个段(这就使得IOT成为一个多段对象)，根据我们的设定，IOT的行数据的一部分可以溢出到这个段中，这样可以让索引叶子块(包含具体索引数据的块)能够高效地存储数据。需要注意，构成主键的列不能溢出，它们必须直接放在叶子块上。建立IOT时，最关键的是适当地分配数据，即哪些数据存储在索引块上，哪些数据存储在溢出段上。使用溢出段的条件可以采用两种方式来指定：

· PCTTHRESHOLD

行中的数据量超过块的这个百分比时，行中余下的列将存储在溢出段中。例如，如果PCTTHRESHOLD是10%，而块大小是8KB，长度大于800字节的行就会把其中一部分存储在溢出段中。 Oracle会从最后一列开始向前查找，直到不包括主键的最后一列，得出哪些列需要存储在溢出段中。例如：

create table iot1 (x int primary key, y date, z varchar2(2000)) organization index pctthreshold 10 overflow;

· INCLUDING

行中从第一列直到INCLUDING子句所指定列(也包括这一列)的所有列都存储在索引块上，余下的列存储在溢出段中。例如：

create table iot2 (x int primary key, y date, z varchar2(2000)) organization index including y overflow;

索引组织表的维护

索引组织表可以和普通堆表一样进行INSERT、UPDATE、DELETE、SELECT操作。

可使用ALTER TABLE ...

OVERFLOW语句来更改溢出段的属性。

altertablet88addoverflow;--新增一个overflow

●

要ALTER任何OVERVIEW的属性，都必须先定义overflow，若建表时没有可以新增

alter

tablet88pctthreshold

including

name;--调整overflow的参数

altertablet88initrans

overflow initrans

4;--修改数据块和溢出段的initrans特性

索引组织表的应用

Heap Table

就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。

IOT就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。适用于信息检索、空间和OLAP程序。

索引组织表的适用情况

1、完全由主键组成的表

这样的表如果采用堆组织表，则表本身完全是多余的开销，因为所有的数据全部同样也保存在索引里，此时，堆表是没用的

2、代码查找表

如果你只会通过一个主键来访问一个表，这个表就非常适合实现为IOT，经常用between…and…对主键或唯一键进行查询。

2、

经常通过主码访问的表。

3、

构建自己的索引结构。

4、

加强数据的共同定位，要数据按特定顺序物理存储。

5、

经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。

不适用情况：

经常更新的表当然不适合IOT，因为oracle需要不断维护索引，而且由于字段多索引成本就大。

如果不是经常使用主键访问表，就不要使用IOT

MAPPING TABLE

1、当在索引组织表上创建位图索引时需要创建关联的映射表；

2、堆表(普通表)位图索引的每个位(0/1)对应到表的一个ROWID，索引组织表的ROWID会随着索引的分裂而发生改变，

3、如果和堆表一样处理，位图索引很容易就失效或者维护成本很高

4、映射表就是为解决这个问题而引入的

A、映射表存放索引组织表的ROWID和逻辑行间的对应关系

B、索引位图中的位对应到逻辑行

C、一个索引组织表只有一个映射表。

D、dba_indexes.pct_direct_access可以用来指示映射表GUESS的效率，这个值大于

30时推荐重建位图索引，即：

随着时间的推移，bitmapindex可能会和IOT不一致，查找不一致范围：selectowner,index_name,pct_direct_accessfromdba_indexeswherepct_direct_accessisnotnullandowner='SCOTT';

Oracle建议，超过30%的bitmapindex应该rebuild。

IOT上建立索引

IOT本身可以再建索引，就像在索引之上再加索引，这称为二次索引(secondary index)。

IOT上的索引可以使用非主键列提供IOT数据的快速、高效访问。

正常情况下，索引包含了所指向的行的物理地址，即rowid。而IOT二次索引无法做到这一点。因为IOT中的行可以大量移动，而且它不像堆组织表中的行那样“迁移”。IOT中的行肯定在索引结构中的每个位置上，这取决于它的主键值；索引本身的大小和形状发生改变时行就会移动。因此，Oracle引入了一个逻辑rowid(logical rowid)，这些逻辑rowid根据IOT主键建立。因此，与常规表相比，IOT上的索引效率稍低。

使用IOT的好处

1、由于索引项和数据存储在一起，所以无论是基于主键的等值查询还是范围查询都能大大节省磁盘访问时间。

2、为了能够更快地访问那些频繁访问的列，可以使用溢出存储选项将那些访问不频繁的列放在B树叶结点数据块之外的溢出堆空间中。这样一来便可以得到更小的B树，以及包含更多行的叶结点

3、和堆组织表和索引不同，主键不需要被存储两次。

4、ROWID伪列是基于主键值的逻辑rowid，而不是物理rowid，即使表被重新组织过，造成了基表行的迁移，二级索引仍然可用，不需要重建。

注：

1、Oracle使用rowid数据类型存储行地址,rowid可以分成两种，分别适于不同的对象，Physical

rowids：存储ordinary table,clustered table,table partition and

subpartition,indexe,index partition and subpartition；Logical rowids

:存储IOT的行地址

2、每个表在oracle内部都有一个ROWID伪列,它在所有sql中无法显示，不占存储空间;它用于从表中查询行的地址或者在where中进行参照,一个例子如下：

SELECT ROWID, last_name FROM