[bbk3153] 第62集 Chapter 15Application Tuning(02)

Index-Organized Tables

IOT表要求必须有主键.heap表可以没有主键.没有主键的表，不能称之为IOT表.

IOT and Heap Tables

• Compared to heap tables,IOTs have:
  • Faster key-based access to table data
  • Reduced storage requirements
  • Secondary indexes and logical rowids
• IOTs have the following restrictions:
  • -Must have a primary key
  • -Cannot be clustered

Creating IOT

CREATE TABLE country
(
    country_id             CHAR(2) CONSTRAINT contry_id_nn NOT NULL,
    country_name        VARCHAR2(40),
    currency_nbame     VARCHAR2(25),
    currency_symbol    VARCAHR2(3),
    map BLOB,
    flag BLOB,
    CONSTRAINT country_c_id_pk PRIMARY KEY(country_id))
    ORGANIZATION INDEX
    TABLESPACE indx
    PCTTHRESHOLD 20
    OVERFLOW TABLESPACE users;

Keyword ORGANIZATION INDEX指明IOT表.

PCTTRHESHOLD 20,OVERFLOW TABLESPACE users，两个参数结合使用：当索引内容占到一个索引块大小的20%的时候，就会overflow到user tablespace里面.节省空间，使一个索引块里面存放的索引entry更多；一个索引块里面存放的索引entry更多，那么hight就会越低.height越低，I/O的次数就会越少，性能就会提高.

IOT Row Overflow

pctthreshold和including这两个关键字，是用来控制那些列放在叶子block，那些列存放在overflow指定的segment.当二者发生冲突的时候， 以关键字pctthreshold优先.

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

上图图解:通过指定关键字pctthreshold 10，说明每行所有字段累加超过叶子节点块大小的10%时，就会将多余出的数据，存放于overflow指定的表空间中.

CREATE TABLE iot2
(
        x       int,
        y       date,
        z       varchar2(2000),
        constraint iot_pk2 primary key(x)
)
organization index
including y
overflow
/

图解：因为在脚本中指定了including y，也就是说列y(包含y)左边的所有列都将包含在叶子节点所在的块中.列y右边的字段将直接放在overflow默认的表空间里面.上图清晰可见.