学习过程中对Oracle索引相关的知识做了一个整理,另外做了两个简单的实验测试B-树索引对执行计划的影响和位图索引的锁机制。
索引的作用:
如果一个堆组织表没有索引,数据库必须执行全表扫描(扫描HWM下所有的块)。索引只影响执行速度。如果表上存在过多的索引,由于数据库需要维护索引,将会降低DML性能。
何时使用索引:
1、要索引的列经常被查询,并只返回表中的行的总数的一小部分。
2、列或列集上存在引用完整性约束(强烈建议对该列或列集建立索引)。如果建立索引将可以避免更新父表主键、合并父表、从父表删除行时可能引起的全表锁定。
3、要在表上设置唯一键约束,并且您想手动指定索引和所有索引选项。
Oracle会在下面两种情况下自动创建索引:
1、对于表的主键。
2、对于表中具有唯一性约束的列。
可见索引与不可见索引:
可见索引是指会被oracle自动维护,并且影响SQL的执行计划。
不可见索引会被自动维护,但是执行SQL时,将不会考虑。不可见索引在测试索引效果时非常有用。
创建索引:
CREATE INDEX index_name ON table_name (column_name);
复合索引:
在多个列上创建的索引即复合索引。
复合索引的键应该按照SQL查询条件中的列的顺序来定义。只有查询包含索引的前导列(前面一部分列)或全部列时,才会使用复合索引。
非唯一索引:
一个索引键值对应多行称为非唯一索引,非唯一索引中,值相同的记录按照rowid升序进行排序。
索引的分类:
B-树索引是一个树结构,分支块用于查找,叶块用于存储值。分支块和叶块都包含指针,而所有叶块形成一个双向链表。
叶块包含键值和rowid,其中rowid用来定位行。
Oracle如何使用索引:
索引中通过N个I/O就能找到要查找的值(N为索引高度)的rowid。
另外如果SQL语句只要查找索引包含的列,则不需要再访问表,否则ORACLE将交替进行索引块读与数据块读。
快速完全索引扫描:
当索引包含了查询所需的所有列,且索引键中至少一列具有NOT NULL约束时(否则列都为空的行不在索引中,从而必须读数据块),快速完全索引扫描可以替代全表扫描。
SELECT name, department
FROM employees;
该查询不包含order by子句,并且name, department包含在索引中,此时会使用快速完全索引扫描。
索引范围扫描:
SELECT name
FROM employees
WHERE name LIKE ‘A%’;
该查询如果对name列进行了索引,数据库可以使用范围扫描。
索引范围扫描通过索引叶块的链表指针向前推进,获得范围内的所有数据(rowid)。
唯一索引扫描:
一个索引键对应0个或
索引的作用:
如果一个堆组织表没有索引,数据库必须执行全表扫描(扫描HWM下所有的块)。索引只影响执行速度。如果表上存在过多的索引,由于数据库需要维护索引,将会降低DML性能。
何时使用索引:
1、要索引的列经常被查询,并只返回表中的行的总数的一小部分。
2、列或列集上存在引用完整性约束(强烈建议对该列或列集建立索引)。如果建立索引将可以避免更新父表主键、合并父表、从父表删除行时可能引起的全表锁定。
3、要在表上设置唯一键约束,并且您想手动指定索引和所有索引选项。
Oracle会在下面两种情况下自动创建索引:
1、对于表的主键。
2、对于表中具有唯一性约束的列。
可见索引与不可见索引:
可见索引是指会被oracle自动维护,并且影响SQL的执行计划。
不可见索引会被自动维护,但是执行SQL时,将不会考虑。不可见索引在测试索引效果时非常有用。
创建索引:
CREATE INDEX index_name ON table_name (column_name);
复合索引:
在多个列上创建的索引即复合索引。
复合索引的键应该按照SQL查询条件中的列的顺序来定义。只有查询包含索引的前导列(前面一部分列)或全部列时,才会使用复合索引。
非唯一索引:
一个索引键值对应多行称为非唯一索引,非唯一索引中,值相同的记录按照rowid升序进行排序。
索引的分类:
B-树索引是一个树结构,分支块用于查找,叶块用于存储值。分支块和叶块都包含指针,而所有叶块形成一个双向链表。
叶块包含键值和rowid,其中rowid用来定位行。
Oracle如何使用索引:
索引中通过N个I/O就能找到要查找的值(N为索引高度)的rowid。
另外如果SQL语句只要查找索引包含的列,则不需要再访问表,否则ORACLE将交替进行索引块读与数据块读。
快速完全索引扫描:
当索引包含了查询所需的所有列,且索引键中至少一列具有NOT NULL约束时(否则列都为空的行不在索引中,从而必须读数据块),快速完全索引扫描可以替代全表扫描。
SELECT name, department
FROM employees;
该查询不包含order by子句,并且name, department包含在索引中,此时会使用快速完全索引扫描。
索引范围扫描:
SELECT name
FROM employees
WHERE name LIKE ‘A%’;
该查询如果对name列进行了索引,数据库可以使用范围扫描。
索引范围扫描通过索引叶块的链表指针向前推进,获得范围内的所有数据(rowid)。
唯一索引扫描:
一个索引键对应0个或