Oracle索引整理
16lz
2021-03-28
简介
索引包含了rowid+存储列值rowid包含了存储列值的表行在磁盘上的物理位置，有了rowid，Oracle可以通过最少量的磁盘读取有效的检索表种的数据。为了提高性能，Oracle还使用了索引来强制执行已经启动的主键和唯一约束。当外键列建立索引时，Oracle可以更好的管理表锁定的情况。索引创建是需要付出代价的，需要消耗磁盘空间和系统资源。B树索引时Oracle默认的索引类型。再表中值最独特的列上创建B树索引，效率最高。创建合适的索引可以提高性能。再某些情况下，查询优化器可以选择不使用索引，也就是查询优化器计算的全表扫描的成本低于使用索引时的成本。再某些情况下，Oracle只需要访问索引就可以检索出要查询的数据，无需对表进行访问。确定使用哪种索引
索引类型
B树索引
默认的索引类型，平衡树索引，适用于高基数（不同值的程度高）的列，除非特殊原因要使用不同的索引类型，否则正常的B树索引。索引组织表
当主键包含大多数的列时很有效率，这种索引就像访问表一样。“数据”存储再一个类似B数的结构中。唯一索引
B树索引的一种形式，用于强制执行列值的唯一性。经常与主键和唯一性约束一起使用。但是也可以独立于约束而创建。反向键索引
B树索引的一种形式，再索引有许多顺序插入的情况下，用于平衡I/O.键压缩索引
适用于签导列经常重复的组合索引，压缩叶条目，此功能适用于B树索引或者IOT（索引组织表）索引降序索引
B树索引的一种,在索引对应的列值按照降序（默认是升序）排序时使用，反向键索引不能指定降序，如果是位图索引，那么oracle忽略降序。位图索引
对于包含低基数列，以及再sql语句的where子句中使用许多and或者or运算符的数仓环境，非常适合使用这种索引，位图索引不适合经常更新行的在线事务处理（oltp）数据库。无法创建唯一的位图索引。位图连接索引
再数据仓库环境中，对于利用连接事实表和维表的星型模式结构的查询非常有用。基于函数的索引
适用于应用sql函数的列，可以与b树索引类型或者位图索引类型结合使用。虚拟列索引
在表的虚拟列上定义的索引，适用于应用了sql函数的列，可以用来替代基于函数的索引。虚拟索引
允许通过create index 的nosegment子句创建没有物理段或者区的自居，再调优sql时有用，因为无需建立物理索引而避免消耗资源。任何类型的索引都可以创建为虚拟索引。不可见索引
该索引对查询优化器时不可见的。然而，再表中的数据被修改的同时也维护索引结构。用于在使索引对应的程序可见之前测试它。任何类型的索引都可以创建为不可见。全局分区索引
跨分区表的所有分区或者常规表的全局索引，它的类型可以是B树索引，而不能是位图索引。本地分区索引
本地索引基于分区表的单个分区，它的类型可以是B树索引或者位图索引。域索引
用于具体的应用程序或者程序模块。B树聚簇索引
用于聚簇表。散列聚簇索引
用于散列聚簇。索引指南
| 索引指南 | 论证 |
|---|---|
| 创建所需数量的索引，但是应尽量少创建索引，明智的添加索引，首先测试来确定可量化的性能收益。 | 索引提高性能，但是消耗磁盘空间和处理资源。不要添加不必要的索引。 |
| 对表执行的查询所需的性能，应成为指定的索引策略的基础。 | |
| 考虑使用sql调优顾问或者sql访问顾问获取索引的建议。 | |
| 为所有表创建主键约束 | |
| 在合适的地方创建创建唯一性约束 | |
| 为包含外键的列创建索引 | 连接表时，外键列通常包含在where子句中，从而能提高sql 的select语句的性能，在外键盘列上创建一个B树索引，还可以减少在更新和插入子表时的锁定问题。 |
| 小心地选择和测试小表索引（小表少于几千行） | |
| 数据仓库环境中，可以考虑使用位图索引。 | 对不经常更新的低基数数值来说，使用这些索引是理想的选择，位图索引适用于星型模式事实表的外键列，前提是经常会对事实表运行使用AND和OR连接条件的查询。 |
| 考虑为索引使用单独的表空间。与表进行分离。 | 便于管理，因为表和索引可能有有不同的存储、备份和恢复的要求 |
| 让索引从表空间即成它的存储属性 更易于管理和维护 | |
| 使用一致的命名标准 使得维护和排障更加方便。 | |
| 不要重建索引，除非有充足的理由这样做。 | 重建索引通常是不必要的，除非索引损坏或者需要将其移动到不同的表空间。 |
| 检测索引，并删除不被使用的索引。 | 这样做可以释放物理空间，并提高DML语句的性能。 |
| 删除索引之前，考虑把它裱机为不可用或者不可见 | 这使得在删除索引之前，可以更好的确定是否有任何性能问题，这些选项可以重建或者重新启用索引而无需用DDL来创建语句。 |
好知识，才能预见未来
赞赏
0人进行了赞赏支持
更多相关文章
- python起步学习（三）
- 组合索引，下他哪些说法符合《阿里巴巴Java开发手册》
- 终于有人把搜索引擎讲明白了
- mysql数据库CURD中常用操作
- Kibana 之 使用指引
- MySQL InnoDB存储引擎select和update查询阻塞insert语句,形成表
- 10+年数据库开发工程师带你深入理解MySQL索引
- MongoDB实战篇(七)：使用索引
- OCP 071中文考试题库(cuug内部资料)第13题