学习笔记——索引

B-Tree索引：ENTRY HEADER + 索引列长度和值(n) + ROWID

位图索引：ENTRY HEADER + KEY VALUES + 起始rowid + 终止rowid + BITMAP SEGMENT

 

-- 创建索引

create index ix_t1 on t1(id)

pctfree 20

storage(initial 100K next 100K pctincrease 0 maxextents 100)

TABLESPACE TEST;

create BITMAP index ix_t1 on t1(TYPE)

pctfree 20

storage(initial 100K next 100K pctincrease 0 maxextents 100)

TABLESPACE TEST;

-- 删除索引

DROP INDEX ix_t1;

-- 数据字典

SELECT * FROM dba_indexes;

-- 索引重建

ALTER INDEX ix_t1 REBUILD PCTFREE 20;

ALTER INDEX ix_t1 REBUILD ALLOCATE EXTENT;

-- 回收未使用的磁盘空间

ALTER INDEX ix_t1 DEALLOCATE UNUSED;

-- 碎片整理

ALTER INDEX ix_t1 COALESCE;

-- 监测索引是否使用

ALTER INDEX ix_t1 MONITORING USAGE; -- Begin monitoring

SELECT * FROM t1 WHERE ID=3; -- Issuing this SQL statement

SELECT * FROM v$object_usage -- 监测结果保存在v$object_usage中

ALTER INDEX ix_t1 NOMONITORING USAGE; -- End monitoring

 

-- The End --