Oracle（12）Managing Indexes

目录

目标：

一、基础知识

1、Classification ofindexes 索引的分类

2、B-Tree vs Bitmap 

3、Creating Indexes: Guidelines 创建索引:准则

4、Offline Index Rebuild 脱机索引重建

5、RebuildingIndexes 重建索引

6、Online Index Rebuild 在线索引重建

7、Index Coalescing 索引合并

8、Getting Index Information 获取索引信息

二、基础操作

1、创建普通索引

 2、Creating B-Tree Indexes 创建B树索引

3、创建Bit map索引

4、Storage Params for indexes 修改索引的存储参数

5、Alloc & Dealloc Index Space 分配和取消分配索引空间

6、Rebuilding Indexes 重建索引

7、Rebuilding indexes Online 在线重建索引

8、Coalescing Indexes 合并索引（解决碎片化问题）

9、Checking Index Validity 检查索引有效性

10、Dropping Indexes 删除索引

11、Identifying Unused indexes 识别未使用的索引

---

Managing Indexes 管理索引

目标：

• 列出不同类型的索引及其用法
• 创建各种类型的索引
• 重组索引
• 维护索引
• 监视索引的使用情况
• 获取索引信息

一、基础知识

1、Classification ofindexes 索引的分类

• Logical 逻辑分类：
  • Single column or concatenated 单列或级联
  • Unique or nonunique 唯一或非唯一
  • Function-based 基于功能的
  • Domain 域名
• Physical 物理分类：
  •  Partitioned or nonpartitioned  分区或非分区
  • B-tree: Normal or reverse key B树:正常键或反向键
    • 
    • 每一个绿色结点都是一个Block，真正的数据是放在Leaf结点上，而Root和Branch只是为了找到Leaf结点
  • Bitmap ：位图
    • 

2、B-Tree vs Bitmap 

• B树（大部分索引使用的都得B树索引）
  • 适用于高基数色谱柱
  • 更新的按键相对便宜
  • 使用OR谓词的查询效率低下
  • 对OLTP有用

• 位图
  • 适用于低基数列
  • 对关键列的更新非常昂贵
  • 对于使用OR谓词的查询是有效的
  • 对数据仓库很有用

3、Creating Indexes: Guidelines 创建索引:准则

• 平衡查询和DML需求
• 放置在单独的表空间中
• 使用统一的扩展大小:五个块的倍数或表空间的最小扩展大小
• 对于大型索引，请考虑NOLOGING
• INITRANS通常在索引上的块比在相应的表上更高

4、Offline Index Rebuild 脱机索引重建

• 1.Lock the table 创建锁表
• 2.Create a new, temporary index by reading againstthe contents of the existing index. 通过读取现有索引的内容来创建新的临时索引。
• 3.Drops the original index.删除原始索引。
• 4. Renames the temporary index to make it seem tobe the original index.重命名临时索引，使其看起来像是原始索引。
• 5.Remove the table lock..移除表锁。

5、RebuildingIndexes 重建索引

在以下情况下重建索引:

• 必须将现有索引移动到不同的表空间。如果索引与表在同一个表空间中，或者需要跨磁盘重分发对象，则可能需要这样做。
• 一个索引包含许多已删除的条目。这是滑动索引的典型问题，例如对订单表的订单号的索引，其中已完成的订单被删除，编号更高的新订单被添加到表中。如果有几个旧订单未完成，则可能有几个索引叶块，除了几个已删除的条目外，其余都是索引叶块。
• 必须将现有的普通索引转换为反向键索引。从早期版本的甲骨文服务器迁移应用程序时可能会出现这种情况。
• 使用ALTERTABLE将索引的表移到了另一个表空间..移动表空间命令。

6、Online Index Rebuild 在线索引重建

• 1.创建表所。
• 2.创建一个新的临时空索引和一个IOT来存储正在进行的DML。
• 3.松开表锁。
• 4.通过读取现有索引的内容填充临时索引。
• 5将lOT的内容与新索引合并
• 6.锁好表锁。
• 7.最后从IOT合并并删除原始索引。
• 8重命名临时索引，使其看起来像是原始索引。
• 9.移除表锁。

7、Index Coalescing 索引合并

• 沿着索引的底部扫描。
• 如果相邻的节点可以组合成一个节点，那么就这样做
• 合并索引比索引重建效率更快

8、Getting Index Information 获取索引信息

可以通过查询以下视图来获取有关索引的信息：

• DBA_INDEXES:DBA索引:提供关于索引的信息
• DBA_IND_COLUMNS:数据库管理系统IND列:提供有关索引的列的信息
• V$OBJECT_USAGE:对象用法:提供有关索引使用的信息

二、基础操作

1、创建普通索引

格式：CREATE INDEX 索引名字 ON 添加索引的表字段

样例：

CREATE INDEX t_idX1 ON t(id);

创建一个索引，名字为t_idX1在t表的id字段上

选择是否创建索引需要根据具体情况而论，如果你需要经常查询且数据量比较大的时候创建索引会大大增加查询的速度，但是对于经常插入、修改等操作的数据不适合创建索引，因为索引会使得这写操作速度降低

 2、Creating B-Tree Indexes 创建B树索引

CREATE INDEX hr.employees_last_name_idx
ON hr.employees (last_name)
PCTFREE 30 -- 如果块的剩余空间小于百分之30就无法插入
STORAGE (INITIAL 200K NEXT 200K
PCTINCREASE O MAXEXTENTS 50)
TABLESPACE indx;

简单的创建B树索引的话只需要写以上代码前两行即可（后边参数默认），高级的话需要全写进行修改参数。

3、创建Bit map索引

格式：CREATE BITMAP INDEX 索引名 ON 添加索引的表字段;

CREATE BITMAP INDEX my_bit_idx ON t(sex);

4、Storage Params for indexes 修改索引的存储参数

ALTER INDEX employees_last_name idx
STORAGE (NEXT 400K
MAXEXTENTS 100);

5、Alloc & Dealloc Index Space 分配和取消分配索引空间

分配

ALTER INDEX orders_region_id_idx
ALLOCATE EXTENT (SIZE 200K
DATAFILE '/DISK6/indx01.dbf');

取消分配

ALTER INDEX orders_id_idx
DEALLOCATE UNUSED:

6、Rebuilding Indexes 重建索引

• 使用ALTERINDEX命令可以:
  • 将索引移动到不同的表空间
  • 通过删除已删除的条目来提高空间利用率

ALTER INDEX orders_region_id_idx REBUILD
TABLESPACE indx02;

7、Rebuilding indexes Online 在线重建索引

• Indexes can be rebuilt with minimal table locking 可以用最少的表锁定重新生成索引

  • ALTER INDEX orders_id_idx REBUILD ONLINE;
    

• Some restrictions still apply 一些限制仍然适用

8、Coalescing Indexes 合并索引（解决碎片化问题）

ALTER INDEX orders_id_idx COALESCE;

9、Checking Index Validity 检查索引有效性

ANALYZE INDEX orders_region_id_idx
VALIDATE STRUCTURE:

10、Dropping Indexes 删除索引

• 在大容量加载之前删除并重新创建索引
• 删除不经常需要的索引，并在必要时生成索引
• 删除并重新创建无效索引

DROP INDEX hr.departments_name_idx;

11、Identifying Unused indexes 识别未使用的索引

• To start monitoring the usage of an index:要开始监视索引的使用情况

• ALTER INDEX hr.dept_id_idx
  MONITORING USAGE
  

• To stop monitoring the usage of an index:若要停止监视索引的使用情况:

• ALTER INDEX hr.dept_id_idx
  NOMONITORING USAGE