1,第11章 索引,2,本章概述,数据库的索引类似于图书的索引。在图书中，索引允许用户不必翻阅整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。在图书中，索引就是内容和相应页号的清单。在数据库中，索引就是表中数据和相应存储位置的列表。对于包含了大量数据的表来说，如果没有索引，那么对表中的数据检索时速度可能慢得令人难以忍受。 本章将全面介绍Oracle系统的索引内容。,3,本章要点,理解索引的基本概念 了解索引的优点和缺点 理解修改数据时索引开销 掌握索引联接、压缩和跳跃的特点 了解反转键索引的特点 掌握基于函数索引的特点 理解位图索引的特点,4,教学过程,11.1 概述 11.2 索引什么时候有用 11.3 索引开销 11.4 索引的联接、压缩和跳跃 11.5 索引和约束 11.6 反转键索引 11.7 基于函数的索引 11.8 位图索引,5,11.1 概述,索引是一种树状结构，可以通过该结构迅速访问表中的数据。索引可以从逻辑设计和物理实现两个方面来分类。 (1) 从逻辑设计方面来看，主要考虑索引是如何组合的。这种情况下，可以把索引分成单列索引和复合索引、唯一性索引和非唯一性索引、基于函数的索引等类型。 (2) 从物理实现的角度来看，索引可以分为分区索引和非分区索引、B树索引、正向索引和反向索引、位图索引、位图联接索引。,6,High Water Mark,This is a term used with objects stored in the database. If you envision a table for example as a flat structure, as a series of blocks laid one after the other in a line from left to right, the high water mark would be the right most block that ever contained data. For example:,7,8,This shows that the high water mark starts at the first block of a newly created table. As data is placed into the table over time and more blocks get used, the high water mark rises. If we delete some (or even all) of the rows in the table, we might have many blocks that no longer contain data, but they are still under the high water mark and will remain under the high water mark until the object is rebuilt or truncated. The high water mark is relevant since Oracle will scan all blocks under the high water mark, even when they contain no data, during a full scan. This will impact the performance of a full scan - especially if most of the blocks under the high water mark are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows). Do a SELECT COUNT(*) from this table. Now, DELETE every row in it and you will find that the SELECT COUNT(*) takes just as long to count zero rows as it did to count 1,000,000. This is because Oracle is busy reading all of the blocks below the high water mark to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the high water mark of a table back to zero. If you plan on deleting every row in a table, TRUNCATE would be the method of my choice for this reason.,9,示例,connect system/manager grant dba to scott; connect scott/tiger create table indextest as select * from dba_objects where owner in (OUTLN, PUBLIC, SCOTT, SYS, SYSTEM); analyze table indextest compute statistics; set autotrace trace explain select owner, object_name from indextest where object_name = DBA_INDEXES; create index indextest_objname_idx on indextest(object_name); select owner, object_name from indextest where object_name = DBA_INDEXES;,10,11.2 索引什么时候有用,从上一节的分析中将会发现，利用索引好像远远胜于全表搜索。 然而，用户可能会提出一个相当合理的问题：如果索引这么好，为什么不在所有表的所有列上都使用索引并且利用他们进行操作？ 总之，好的索引是选择性索引，它只会引用全部数据量中很少比例的数据。,11,示例,set autotrace off select owner, count(*) from indextest group by owner; create index indextest_owner_idx on indextest(owner) set autotrace trace explain select owner, object_name from indextest where owner = SYS; select owner, object_name from indextest where owner = SCOTT; analyze table indextest compute statistics for columns owner; select owner, object_name from indextest where owner = SYS; select owner, object_name from indextest where owner = SCOTT;,12,11.3 索引开销,前面提到过，索引可以提高检索效率，具有高选择性的索引总是比全表搜索更有效地从表中获取数据。但是，索引的出现会对插入操作、更新操作和删除操作带来负面影响。 本节主要介绍索引的开销。,13,11.3.1 插入行如何影响索引,若再向表中插入15行数据，name列上的索引如下：,14,插入Bill后，name列上的索引可能如下：,15,示例,下面通过一个示例来介绍插入数据是如何影响索引的。为了确保可以在统一层次的领域中操作，重建前面已经存在的表和索引。在如图11-13所示的代码中，首先关闭跟踪开关，然后删除以前创建的indextest表。接下来，基于dba_objects数据字典视图重新创建indextest表，并在该表的object_name列上创建索引。注意，由于pctfree参数的值为0，表示所有的叶子节点都充满了数据。然后对该表进行统计分析。,16,set autotrace off drop table indextest; create table indextest as select * from dba_objects where owner in (OUTLN, PUBLIC, SCOTT, SYS, SYSTEM); create index indextest_objname_idx on indextest(object_name) pctfree 0; analyze table indextest compute statistics; analyze index indextest_objname_idx validate structure; select name, height, lf_blks, pct_used from index_stats; insert into indextest(owner, object_name) values(AAAAAAAAAA, AAAAAAAAAAAAAAAAAAAAA); commit; analyze index indextest_objname_idx validate structure; select name, height, lf_blks, pct_used from index_stats; insert into indextest(owner, object_name) values(ZZZZZZZZZZ, ZZZZZZZZZZZZZZZZZZZZZ); commit; analyze index indextest_objname_idx validate structure; select name, height, lf_blks, pct_used from index_stats;,17,alter index indextest_objname_idx rebuild pctfree 10; analyze index indextest_objname_idx validate structure; select name, height, lf_blks, pct_used from index_stats; insert into indextest(owner, object_name) values(AAAAAAAAAA, AAAAAAAAAAAAAAAAAAAAA); commit; analyze index indextest_objname_idx validate structure; select name, height, lf_blks, pct_used from index_stats; insert into indextest(owner, object_name) values(ZZZZZZZZZZ, ZZZ