ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Using Index Only Tables (IOTs)[akadia]

Using Index Only Tables (IOTs)[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-07-18 20:57:02 0 删除 编辑

Index-organized tables are tables with data rows grouped according to the primary key. The attributes of index-organized tables are stored entirely within the physical data structures for the index. Index-organized tables provide fast key-based access to table data for queries involving exact match and range searches. Changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure (because there is no separate table storage area). Also, storage requirements are reduced because key columns are not duplicated in the table and index. The remaining non-key columns are stored in the index structure.

Index-organized tables are particularly useful when you are using applications that must retrieve data based on a primary key such as intersection tables.

Note the following important constraints

  • The data in an index-organized table is not duplicated.

  • Index-organized tables can be reorganized with the ALTER TABLE
    statement MOVE clause

  • Index-organized table must have a Primary Key

  • Index-organized tables may not have unique constraints.

  • Cannot contain LONGs or LOBs.

  • The DBA_TABLES data dictionary view contains two new columns, IOT_NAME and IOT_TYPE, to provide information on index-organized tables.

Here is an example

CREATE TABLE my_intersection (
id1 NUMBER(15) NOT NULL,
id2 NUMBER(15) NOT NULL,
job VARCHAR2(500) NULL,
CONSTRAINT pk_my_intersection
PRIMARY KEY (id1,id2))
ORGANIZATION INDEX
STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0)
TABLESPACE idx
INCLUDING job
OVERFLOW TABLESPACE tab;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60592/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    268
  • 访问量
    172247