再上一篇:11.4基于函数的索引
上一篇:11.5应用域索引
主页
下一篇:11.7小结
再下一篇:12.1Oracle数据类型概述
文章列表

11.6关于索引的常见问题和神话

Oracle 9i 10g编程艺术:深入数据库体系结构

在本书的引言中曾经说过,我回答过大量关于Oracle的问题。我就是Oracle Magazine上“Ask Tom” 专栏和 http://asktom.oracle.com上的 Tom,在这个专栏和网站上我一直在回答大家提出的关于Oracle 数据库和工具的问题。根据我的经验,其中关于索引的问题最多。这一节中,我将回答问得最多的一些问 题。有些答案就像是常识一样,很直接;但是有些答案可能会让你很诧异。可以这么说,关于索引存在的 许多神话和误解。

11.6.1视图能使用索引吗?

与这个问题相关的另一个问题是:“能对视图加索引吗?”视图实际上就是一个存储查询(stored query)。Oracle会把查询中访问视图的有关文本代之以视图定义本身。视图只是为了方便最终用户或程序 员,优化器还是会对基表使用查询。使用视图时,完全可以考虑使用为基表编写的查询中所能使用的所有 索引。“对视图建立索引”实际上就是对基表建立索引。

11.6.2 Null和索引能协作吗?

B*树索引(除了聚簇B*树索引这个特例之外)不会存储完全为null的条目,而位图好聚簇索引则不 同。这个副作用可能会带来一些混淆,但是如果你理解了不存储完全为null的键是什么含义,就能很好地 利用这一点。

要看到不存储null值所带来的影响,请考虑下面这个例子:
ops$tkyte@ORA10GR1> create table t ( x int, y int );
Table created.
ops$tkyte@ORA10GR1> create unique index t_idx on t(x,y);

Index created.
ops$tkyte@ORA10GR1> insert into t values ( 1, 1 );
11.row created.
ops$tkyte@ORA10GR1> insert into t values ( 1, NULL );
11.row created.
ops$tkyte@ORA10GR1> insert into t values ( NULL, 1 );
11.row created.
ops$tkyte@ORA10GR1> insert into t values ( NULL, NULL );
11.row created.
ops$tkyte@ORA10GR1> analyze index t_idx validate structure; Index analyzed.
ops$tkyte@ORA10GR1> select name, lf_rows from index_stats; NAME LF_ROWS
------------------------------ ----------
T_IDX 3
这个表有 4行,而索引只有3 行。前三行(索引键元素中至少有一个不为null)都在索引中。最后

一行的索引键是(NULL,NULL),所以这一行不在索引中。倘若索引是一个惟一索引(如上所示),这就是可 能产生混淆的一种情况。考虑以下3个INSERT语句的作用:
ops$tkyte@ORA10GR1> insert into t values ( NULL, NULL );

11.row created.
ops$tkyte@ORA10GR1> insert into t values ( NULL, 1 );
insert into t values ( NULL, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
ops$tkyte@ORA10GR1> insert into t values ( 1, NULL );
insert into t values ( 1, NULL )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated

这里并不认为新的(NULL,NULL)行与原来的(NULL,NULL)行相同:
ops$tkyte@ORA10GR1> select x, y, count(*)
2 from t
3 group by x,y
4 having count(*) > 1;
X Y COUNT(*)
---------- ---------- ----------
2
看上去好像不可能的,如果考虑到所有null条目,这就说明我们的惟一键并不惟一。事实上,在 Oracle
中,考虑惟一性时(NULL,NULL)与(NULL,NULL)并不相同,这是 SQL标准要求的。不过对于聚集来说
(NULL,NULL)和(NULL,NULL)则认为是相同的。两个(NULL,NULL)在比较时并不相同,但是对GROUP BY 子句来说却是一样的。所以应当考虑到:每个惟一约束应该至少有一个确实惟一的NOT NULL列。
关于索引和null值还会提出这样一个疑问是:“为什么我的查询不使用索引?”下面是一个有问题的 查询:

select * from T where x is null;
这个查询无法使用我们刚才创建的索引,(NULL,NULL)行并不在索引中,因此使用索引的话实际上会

返回错误的答案。只有当索引键中至少有一个列定义为NOT NULL时查询才会使用索引。例如,以下显示了 Oracle会对X IS NULL谓词使用索引(如果索引的索引键最前面是X列,而且索引中其他列中至少有一列 是NOT NULL):
ops$tkyte@ORA10GR1> create table t ( x int, y int NOT NULL );
Table created.
ops$tkyte@ORA10GR1> create unique index t_idx on t(x,y); Index created.
ops$tkyte@ORA10GR1> insert into t values ( 1, 1 );
11.row created.
ops$tkyte@ORA10GR1> insert into t values ( NULL, 1 );
11.row created.
ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats(user,'T');
3 end;
4 /
PL/SQL procedure successfully completed.
再来查询这个表,会发现:
ops$tkyte@ORA10GR1> set autotrace on

ops$tkyte@ORA10GR1> select * from t where x is null; X Y

---------- ----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11.Card=11.Bytes=5)
11.0 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX (UNIQUE)) (Cost=11.Card=11.Bytes=5)
前面我说过,B*树索引中不存储完全为null的条目,而且你可以充分利用这一点,以上就展示了应
当如何加以利用。假设你有一个表,其中每一列只有两个可取值。这些值分布得很不均匀,例如,90%以上 的行(多数行)都取某个值,而另外不到11.%的行(少数行)取另外一个值。可以有效地对这个列建立索 引,来快速访问那些少数行。如果你想使用一个索引访问少数行,同时又想通过全面扫描来访问多数行, 另外还想节省空间,这个特性就很有用。解决方案是:对多数行使用null,而对少数行使用你希望的任何 值;或者如前所示,使用一个基于函数的索引,只索引函数的非null返回值。
既然知道了B*树如何处理null值,所以可以充分利用这一点,并预防在全都允许有null值的列上 建立惟一约束(当心这种情况下可能有多个全null的行)。

11.6.3外键是否应该加索引?

外键是否应该加索引,这个问题经常被问到。我们在第 6章讨论死锁时谈到过这个话题。在第6章中 , 我指出,外键未加索引是我所遇到的导致死锁的最主要的原因;这是因为,无论是更新父表主键,或者删 除一个父记录,都会在子表中加一个表锁(在这条语句完成前,不允许对子表做任何修改)。这就会不必要 地锁定更多的行,而影响并发性。人们在使用能自动生成SQL来修改表的某个工具时,就经常遇到这种问 题。这样的工具会生成一个更新语句,它将更新表中的每一列,而不论这个值是否被UPDATE语句修改。这 就会导致更新主键(即使主键值其实从未改变过)。例如,Oracle Forms就会默认地这样做,除非你告诉 它只把修改过的列发送给数据库。除了可能遇到表锁问题之外,在以下情况下,外键未加索引也表现得很 糟糕:
如果有一个ON DELETE CASCADE,而且没有对子表建索引。例如,EMP是DEPT的子表。DELETE FROM DEPT WHERE DEPTNO = 11.会级联至EMP。如果EMP中的DEPTNO没有加索引,就会导致对 EMP执行一个全表扫描。这种完全扫描可能是不必要的,而且如果从父表删除了多行,对于删除 的每一个父行,都会把子表扫描一次。
从父表查询子表时。还是考虑 EMP/DEPT的例子。在 DEPTNO 上下文查询 EMP表相当常见。 如果频繁地执行以下查询来生成一个报告或某个结果:
select *
from dept, emp

where emp.deptno = dept.deptno and dept.dname = :X;
你会发现,如果没有索引会使查询减慢。由于同样的原因,我在第 11.章曾建议对嵌套表中的 NESTED_COLUMN_ID加索引。嵌套表的隐藏列NESTED_COLUMN_ID实际上就是一个外键。
那么,什么时候不需要对外键加索引呢?一般来说,如果满足以下条件则可如此: 未删除父表中的行。 不论是有意还是无意(如通过一个工具),总之未更新父表的惟一/主键值。 不论从父表联结到子表,或者更一般地讲,外键列不支持子表的一个重要的访问途径,而
且你在谓词中没有使用这些外键列从子表中选择数据(如DEPT到EMP)。
如果满足上述所有3个条件,就完全可以不加索引,也就是说,对外键加索引是不必要的,还会减慢 子表上DML操作的速度。如果满足了其中某个条件,就要当心不加索引的后果。

另外说一句,如果你认为某个子表会由于外键为加索引而被锁住,而且希望证明这一点(或者一般来 说,你想避免这种情况),可以发出以下命令:
ALTER TABLE <child table name> DISABLE TABLE LOCK;

现在,对父表的可能导致表锁的任何UPDATE或DELETE都会接收到以下错误:
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for <child table name>
这有助于跟踪到有问题的代码段,你以为它没有做某件事(比如,你认为并没有对父表的主键执行
UPDATE或DELETE),但实际上事与愿违,通过以上命令,最终用户就会立即向你反馈这个错误。

11.6.4为什么没有使用我的索引?

对此有很多可能的原因。在这一节中,我们会查看其中一些最常见的原因。

1. 情况1

我们在使用一个B*树索引,而且谓词中没有使用索引的最前列。如果是这种情况,可以假设有一个 表T,在T(X,Y)上有一个索引。我们要做以下查询:SELECT * FROM T WHERE Y = 5。此时,优化器就不打 算使用T(x,y)上的索引,因为谓词中不涉及X列。在这种情况下,倘若使用索引,可能就必须查看每一个 索引条目(稍后我们会讨论一种索引跳跃式扫描,这是一种例外情况),而优化器通常更倾向于T对做一个 全表扫描。但这并不完全排除使用索引。如果查询是SELECT X, Y FROM T WHERE Y = 5,优化器就会注意 到,它不必全面扫描表来得到X 或Y(X 和Y 都在索引中),对索引本身做一个快速的全面扫描会更合适, 因为这个索引一般比底层表小得多。还要注意,仅CBO能使用这个访问路径。

另一种情况下CBO也会使用T(x,y)上的索引,这就是索引跳跃式扫描。当且仅当索引的最前列(在 上一个例子中,最前列就是Y)只有很少的几个不同值,而且优化器了解这一点,跳跃式扫描(skip scan) 就能很好地发挥作用。例如,考虑(GENDER, EMPNO)上的一个索引,其中GENDER可取值有M 和F,而且 EMPNO是惟一的。对于以下查询:
select * from t where empno = 5;
可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询,这说明从概念上讲这个查询会如

下处理:
select * from t where GENDER='M' and empno = 5

UNION ALL
select * from t where GENDER='F' and empno = 5;
它会跳跃式地扫描索引,以为这是两个索引:一个对于值M,另一个对应值F。在查询计划中可以很

容易地看出这一点。我们将建立一个表,其中有一个二值的列,并在这个列上建立索引:
ops$tkyte@ORA10GR1> create table t
2 as
3 select decode(mod(rownum,2), 0, 'M', 'F' ) gender, all_objects.*
4 from all_objects
5 /
Table created.
ops$tkyte@ORA10GR1> create index t_idx on t(gender,object_id)
2 /
Index created.
ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

做以下查询时,可以看到结果如下:
ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t t1 where object_id = 42;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11.Bytes=95)
11.0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=4 Card=11.Bytes=95)
2 1 INDEX (SKIP SCAN) OF 'T_IDX' (INDEX) (Cost=3 Card=1)
INDEX SKIP SCAN步骤告诉Oracle要跳跃式扫描这个索引,查找GENDER值有改变的地方,并从那里

开始向下读树,然后在所考虑的各个虚拟索引中找到OBJECT_ID = 42。如果大幅增加 GENDER的可取值, 如下:
ops$tkyte@ORA10GR1> update t
2 set gender = chr(mod(rownum,256));
48215 rows updated.
ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.
我们会看到,Oracle 不再认为跳跃式扫描是一个可行的计划。优化器本可以去检查256 个小索引,

但是它更倾向于执行一个全表扫描来找到所需要的行:
ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t t1 where object_id = 42;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=158 Card=11.Bytes=95)

11.0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=158 Card=11.Bytes=95)

2. 情况2

我们在使用一个 SELECT COUNT(*) FROM T 查询(或类似的查询),而且在表 T 上有一个 B*树索引。 不过,优化器并不是统计索引条目,而是在全面扫描这个表(尽管索引比表要小)。在这种情况下,索引可 能建立在一些允许有null值的列上。由于对于索引键完全为null的行不会建立相应的索引条目,所以索 引中的行数可能并不是表中的行数。这里优化器的选择是对的,如若不然,倘若它使用索引来统计行数, 则可能会得到错误的答案。

3. 情况3


对于一个有索引的列,做以下查询:
select * from t where f(indexed_column) = value
却发现没有使用INDEX_COLUMN上的索引。原因是这个列上使用了函数。我们是对INDEX_COLUMN的值
建立了索引,而不是对F(INDEXED_COLUMN)的值建索引。在此不能使用这个索引。如果愿意,可以另外对 函数建立索引。

4. 情况4


我们已经对一个字符创建了索引。这个列只包含数值数据。如果所用以下语句来查询:
select * from t where indexed_column = 5
注意查询中的数字5是常数5(而不是一个字符串),此时就没有使用 INDEX_COLUMN上的索引。这是

因为,前面的查询等价于一些查询:
select * from t where to_number(indexed_column) = 5
我们对这个列隐式地应用了一个函数,如情况3 所述,这就会禁止使用这个索引。通过一个小例子能

很容易地看出这一点。在这个例子,我们将使用内置包DBMS_XPLAN。这个包只在Oracle9i Release 2及 以上版本中可用(在Oracle9i Release 1中,使用AUTOTRACE能很容易地查看计划,但是得不到谓词信息 , 这只在Oracle9i Release 2及以上版本中可见):
ops$tkyte@ORA10GR1> create table t ( x char(1) constraint t_pk primary key,
2 y date ); Table created.
ops$tkyte@ORA10GR1> insert into t values ( '5', sysdate );
11.row created.
ops$tkyte@ORA10GR1> delete from plan_table;
3 rows deleted.
ops$tkyte@ORA10GR1> explain plan for select * from t where x = 5;
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------ Plan hash value: 749696591
---------------------------------------------------------------------------------------
-------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
---------------------------------------------------------------------------------------
-------------------------------
| 0 | SELECT STATEMENT | | 1 | 11. | 2 (0)
| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T | 1 | 11. | 2 (0)
| 00:00:01 |
---------------------------------------------------------------------------------------
-------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=5)


可以看到,它会全面扫描表;另外即使我们对查询给出了以下提示:
ops$tkyte@ORA10GR1> explain plan for select /*+ INDEX(t t_pk) */ * from t
2 where x = 5;

Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT
------------------------------------ Plan hash value: 3473040572

--------------------------------------------------------------------------------------------------------

-----------------------------------

| Id | Operation | Name | Rows | Bytes

| Cost (%CPU) | Time |

--------------------------------------------------------------------------------------------------------

------------------------------------

| 0 | SELECT STATEMENT | | 1 | 11. | 34 (0)

| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 11. | 34 (0) |

00:00:01 |

|* 2 | INDEX FULL SCAN | T_PK | 1 | | 26 (0)

| 00:00:01 |

--------------------------------------------------------------------------------------------------------

------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("X")=5)
在此使用了索引,但是并不像我们想像中那样对索引完成惟一扫描(UNIQUE SCAN),而是完成了全面

扫描(FULL SCAN)。原因从最后一行输出可以看出:filter(TO_NUMBER(“X”)=5)。这里对这个数据库列 应用了一个隐式函数。X 中存储的字符串必须转换为一个数字,之后才能与值5 进行比较。在此无法把5 转换为一个串,因为我们的NLS(国家语言支持)设置会控制5 转换成串时的具体形式(而这是不确定的, 不同的NLS设置会有不同的控制),所以应当把串转换为数字。而这样一来(由于应用了函数),就无法使 用索引来快速地查找这一行了。如果只是执行串与串的比较:
ops$tkyte@ORA10GR1> delete from plan_table;
2 rows deleted.
ops$tkyte@ORA10GR1> explain plan for select * from t where x = '5';
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------- Plan hash value: 1301177541
-------------------------------------------------------------------------------------------------------

-----------------------------------

| Id | Operation | Name | Rows | Bytes

| Cost (%CPU) | Time |

--------------------------------------------------------------------------------------------------------

-----------------------------------

| 0 | SELECT STATEMENT | | 1 | 11. | 1 (0)

| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 11. | 1 (0) |

00:00:01 |

|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)

| 00:00:01 |

--------------------------------------------------------------------------------------------------------

-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='5')

不出所料,这会得到我们期望的 INDEX UNIQUE SCAN,而且可以看到这里没有应用函数。一定要尽可
能地避免隐式转换。苹果和橘子本来就是两样东西,苹果就和苹果比,而橘子就该和橘子比。这里还经常

出现一个关于日期的问题。如果做以下查询:
-- find all records for today
select * from t where trunc(date_col) = trunc(sysdate);
而且发现这个查询没有使用DATE_COL上的索引。为了解决这个问题。可以对TRUNC(DATE_COL)建立

索引,或者使用区间比较运算符来查询(也许这是更容易的做法)。下面来看对日期使用大于和小于运算符 的一个例子。可以认识到以下条件:
TRUNC(DATE_COL) = TRUNC(SYSDATE)

与下面的条件是一样的:
select *
from t
where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)
这就把所有函数都移动等式的右边,这样我们就能使用 DATE_COL 上的索引了(而且与 WHERE
TRUNC(DATE_COL)=TRUNC(SYSDATE)的效果完全一样)。 如果可能的话,倘若谓词中有函数,尽量不要对数据库列应用这些函数。这样做不仅可以使用更多的

索引,还能减少处理数据库所需的工作。在上一种情况中,使用以上条件时:
where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)
查询只会计算一次 TRUNC 值,然后就能使用索引来查找满足条件的值。使用 TRUNC(DATE_COL) =
TRUNC(SYSDATE)时,TRUNC(DATE_COL)则必须对整个表(而不是索引)中的每一行计算一次。

5. 情况5


此时如果用了索引,实际上反而会更慢。这种情况我见得太多了,人们想当然认为,索引总是会使查 询更快。所以,他们会建立一个小表,再执行分析,却发现优化器并没有使用索引。在这种情况下,优化 器的做法绝对是英明的。Oracle(对CBO而言)只会在合理地时候才使用索引。考虑下面的例子:
ops$tkyte@ORA10GR1> create table t
2 ( x, y , primary key (x) )
3 as
4 select rownum x, object_name
5 from all_objects
6 /

Table created.
ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

如果运行一个查询,它只需要表中相对较少的数据,如下:
ops$tkyte@ORA10GR1> set autotrace on explain
ops$tkyte@ORA10GR1> select count(y) from t where x < 50; COUNT(Y)
----------
49
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=11.Bytes=28)
11.0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3 Card=41 Bytes=1148)
3 2 INDEX (RANGE SCAN) OF 'SYS_C009167' (INDEX (UNIQUE)) (Cost=2 Card=41)
此时,优化器会很乐意地使用索引;不过,我们发现,如果估计通过索引获取的行数超过了一个阀值

(取决于不同的优化器设计、物理统计等,这个阀值可能有所变化),就会观察到优化器将开始一个全部扫 描:
ops$tkyte@ORA10GR1> select count(y) from t where x < 15000;
COUNT(Y)
----------

14999
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=57 Card=11.Bytes=28)
11.0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=57 Card=14994 Bytes=419832)
这个例子显示出优化器不一定会使用索引,而且实际上,它会做出正确的选择:采用跳跃式索引。对
查询调优时,如果发现你认为本该使用的某个索引实际上并没有用到,就不要冒然强制使用这个索引,而 应该先做个测试,并证明使用这个索引后确实会加快速度(通过耗用的时间和I/O次数来判断),然后再考 虑让CBO“就范”(强制它使用这个索引)。总得先给出理由吧。

6. 情况6

有一段时间没有分析表了。这些表起先很小,但等到查看时,它们已经增长得非常大。现在索引就还 有意义(尽管原先并非如此)。如果此时分析这个表,就会使用索引。
如果没有正确的统计信息,CBO将无法做出正确的决定。

7. 索引情况小结

根据我的经验,这6种情况就是不使用索引的主要原因。归根结底,原因通常就是“不能使用索引, 使用索引会返回不正确的结果“,或者”不应该使用,如果使用了索引,性能会变得很糟糕“。

11.6.5神话:索引中从不重用空间

这是我要彻底揭穿的一个神话:在索引中确实会重用空间。这个神话是这样说的:假设有一个表T, 其中有一个列X。在某个时间点上,你在表中放了一个值X=5。后来把它删除了。据这个神话称:X=5所用 的空间不会被重用,除非以后你再把X=5放回索引中。按这个神话的说法,一旦使用了某个索引槽,它就 永远只能被同一个值重用。从这个神话出发还有一个推论,认为空闲空间绝对不会返回给索引结构,而且 块永远不会被重用。同样,事实并非如此。

很容易证明这个神话的第一部分是错误的。我们只需如下创建一个表:
ops$tkyte@ORA10GR1> create table t ( x int, constraint t_pk primary key(x) );
Table created.
ops$tkyte@ORA10GR1> insert into t values (1);
11.row created.
ops$tkyte@ORA10GR1> insert into t values (2);
11.row created.
ops$tkyte@ORA10GR1> insert into t values (9999999999);
11.row created.
ops$tkyte@ORA10GR1> analyze index t_pk validate structure; Index analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, btree_space
2 from index_stats;
LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- -----------
1 0 7996

因此,根据这个神话所述,如果我从 T中删除了X=2的行,这个空间就不会得到重用,除非我再次插

入数字2。当前,这个索引使用了一个叶子块空间。如果索引键条目删除后绝对不会重用,只要我不断地 插入和删除,而且从不重用任何值,那么这个索引就应该疯狂地增长。我们来看看实际是怎样的:
ops$tkyte@ORA10GR1> begin
2 for i in 2 .. 999999
3 loop
4 delete from t where x = i;
5 commit;
6 insert into t values (i+1);

7 commit;
8 end loop;
9 end;
11. /
ops$tkyte@ORA10GR1> analyze index t_pk validate structure; Index analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, btree_space
2 from index_stats;
LF_BLKS BR_BLKS BTREE_SPACE
---------- ---------- -----------
1 0 7996
由此可以看出,索引中的空间确实得到了重用。不过,就像大多数神话一样,这里也有那么一点真实
的地方。真实性在于,初始数字2(介于1~9.999.999.999之间)所用的空间会永远保留在这个索引块上 。 索引不会自行“合并“。这说明,如果我用值1~500,000加载一个表,然后隔行删除表记录(删除所有偶 数行),那么这个索引中那一列上就会有250,000个”洞“。只有当我重新插入数据,而且这个数据能在有 洞的块中放下时,这些空间才会得到重用。Oracle并不打算“收缩”或压缩索引,不过这可以通过ALTER INDEX REBUILD或COALESCE命令强制完成。另一方面,如果我用值1~500,000加载一个表,然后从表中 删除值小于或等于250,000的每一行,就会发现从索引中清除的块将放回到索引的freelist中,这个空间 完全可以重用。

如果你还记得,第二个神话:索引空间从不“回收”。据这个神话称:一旦使用了一个索引块,它就 会一直呆在索引结构的那个位置上,而且只有当你插入数据,并放回到原来那个位置上时,这个块才会被 重用。同样可以证明这是错误的。首先,需要建立一个表,其中大约有500,000 行。为此,我们将使用 big_table脚本。有了这个表,而且有了相应的主键索引后,我们将测量索引中有多少个叶子块,另外索 引的freelist上有多少个块。要记住,对于一个索引,只有当块完全为空时才会放在freelist上,这一 点与表不同。所以我们在freelist上看到的块都完全为空,可以重用。
ops$tkyte@ORA10GR1> select count(*) from big_table;
COUNT(*)
----------

500000
ops$tkyte@ORA10GR1> declare
2 l_freelist_blocks number;
3 begin
4 dbms_space.free_blocks
5 ( segment_owner => user,
6 segment_name => 'BIG_TABLE_PK',
7 segment_type => 'INDEX',
8 freelist_group_id => 0,
9 free_blks => l_freelist_blocks );
11. dbms_output.put_line( 'blocks on freelist = ' || l_freelist_blocks );
11. end;
11. /
blocks on freelist = 0
PL/SQL procedure successfully completed. ops$tkyte@ORA10GR1> select leaf_blocks from user_indexes
2 where index_name = 'BIG_TABLE_PK';
LEAF_BLOCKS
-----------
1043
执行这个批量删除之前,freelist上没有块,而在索引的“叶子”层上有11.043给块,这些叶子块

中包含着数据。下面,我们将执行删除,并再次测量空间的利用情况:
ops$tkyte@ORA10GR1> delete from big_table where id <= 250000;

250000 rows deleted.
ops$tkyte@ORA10GR1> commit; Commit complete.
ops$tkyte@ORA10GR1> declare
2 l_freelist_blocks number;
3 begin
4 dbms_space.free_blocks
5 ( segment_owner => user,
6 segment_name => 'BIG_TABLE_PK',
7 segment_type => 'INDEX',
8 freelist_group_id => 0,
9 free_blks => l_freelist_blocks );
11. dbms_output.put_line( 'blocks on freelist = ' || l_freelist_blocks );
11. dbms_stats.gather_index_stats
11. ( user, 'BIG_TABLE_PK' );
11. end;
11. /
blocks on freelist = 520
PL/SQL procedure successfully completed. ops$tkyte@ORA10GR1> select leaf_blocks from user_indexes
2 where index_name = 'BIG_TABLE_PK';

LEAF_BLOCKS
-----------
523
可以看到,现在,索引中一半以上的块都在freelist上(520个块),而且现在只有523个叶子块。
如果将523和520相加,又得到了原来的11.043。这说明 freelist上的这些块完全为空的,而且可以重 用(索引freelist上的块必须为空,这与堆组织表的freelist上的块不同)。
以上例子强调了两点: 一旦插入了可以重用空间的行,索引块上的空间就会立即重用。 索引块为空时,会从索引结构中取出它,并在以后重用。这可能是最早出现这个神话的根
源:与表不同,在索引结构中,不能清楚地看出一个块有没有“空闲空间”。在表中,可以看到
freelis上的块,即使其中包含有数据。而在索引中,只能在 freelist上看到完全为空的块; 至少有一个索引条目(但其余都是空闲空间)的块就无法清楚地看到。

11.6.6神话:最有差别的元素应该在最前面

这看上去像是一个常识。对于一个有100,000行的表,如果要在C1和C2列上创建一个索引,你发现 C1有100,000个不同的值,而C2有25,000个不同的值,你可能想在T(C1, C2)上创建索引。这说明,C1 应该在前面,这是“常识性”的方法。事实上,在比较数据向量时(假设 C1和C2是向量),把哪一个放在 前面都关系不大。考虑以下例子。我们将基于ALL_OBJECTS创建一个表,并基于OWNER、OBJECT_TYPE 和 OBJECT_NAME 列创建一个索引(这些列按从最没有差别到最有差别的顺序排列,即 OWNER 列差别最小, OBJECT_TYPE次之,OBJECT_NAME列差别最大),另外还在 OBJECT_NAME、OBJECT_TYPE和OWNER上创建了另 一个索引:
ops$tkyte@ORA10GR1> create table t
2 as
3 select * from all_objects; Table created.
ops$tkyte@ORA10GR1> create index t_idx_1 on t(owner,object_type,object_name); Index created.

ops$tkyte@ORA10GR1> create index t_idx_2 on t(object_name,object_type,owner); Index created.
ops$tkyte@ORA10GR1> select count(distinct owner), count(distinct object_type),
2 count(distinct object_name ), count(*)
3 from t;
DISTINCTOWNER DISTINCTOBJECT_TYPE DISTINCTOBJECT_NAME COUNT(*)
------------- -------------------
------------------- --------
28 36
28537 48243


现在,为了显示这二者在高效使用空间方面难分伯仲,下面测量它们的空间利用情况:
ops$tkyte@ORA10GR1> analyze index t_idx_1 validate structure;
Index analyzed.
ops$tkyte@ORA10GR1> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
2 from index_stats;
BTREE_SPACE PCT OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ------ --------------
----------------
2702744 89.0 2
28 ops$tkyte@ORA10GR1> analyze index t_idx_2 validate structure; Index analyzed.
ops$tkyte@ORA10GR1> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave

2 from index_stats;
BTREE_SPACE PCT OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ------ --------------
----------------
2702744 89.0 1
11.
它们使用的空间大小完全一样,细到字节级都一样,二者没有什么区别。不过,如果使用索引键压缩 ,

第一个索引更可压缩,这一点由OPT_CMP_PCTSAVE值可知。有人提倡索引中应该按最没有差别到最有差别 的顺序来安排列,这正是这种看法的一个理由。下面来看这两个索引的表现,从而确定是否有哪个索引更 “优秀”,总比另一个索引更高效。要测试这一点,我们将使用一个PL/SQL代码块(其中包括有提示的查 询,指示要使用某个索引或者另一个索引):
ops$tkyte@ORA10GR1> alter session set sql_trace=true;
Session altered. ops$tkyte@ORA10GR1> declare
2 cnt int;
3 begin
4 for x in ( select /*+FULL(t)*/ owner, object_type, object_name from t )
5 loop
6 select /*+ INDEX( t t_idx_1 ) */ count(*) into cnt
7 from t
8 where object_name = x.object_name
9 and object_type = x.object_type
11. and owner = x.owner;
11.
11. select /*+ INDEX( t t_idx_2 ) */ count(*) into cnt
11. from t

11. where object_name = x.object_name
11. and object_type = x.object_type
11. and owner = x.owner;
11. end loop;
11. end;
11. /
PL/SQL procedure successfully completed.

这些查询按索引读取表中的每一行。TKPROF报告显示了以下结果:
SELECT /*+ INDEX( t t_idx_1 ) */ COUNT(*) FROM T
WHERE OBJECT_NAME = :B3 AND OBJECT_TYPE = :B2 AND OWNER = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.00 0 0
0 0
Execute 48243 11..63 11..78 0 0
0 0
Fetch 48243 11.90 11.77 0 145133
0 48243
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 96487 11..53 11..55 0 145133
0 48243
Rows Row Source Operation
------- ---------------------------------------------------

48243 SORT AGGREGATE (cr=145133 pr=0 pw=0 time=2334197 us)
57879 INDEX RANGE SCAN T_IDX_1 (cr=145133 pr=0 pw=0 time=1440672 us)(object...
******************************************************************************** SELECT /*+ INDEX( t t_idx_2 ) */ COUNT(*) FROM T
WHERE OBJECT_NAME = :B3 AND OBJECT_TYPE = :B2 AND OWNER = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.00 0 0
0 0
Execute 48243 11..00 11..78 0 0
0 0
Fetch 48243 11.87 2.11. 0 145168 0 48243
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 96487 11..87 11..88 0 145168
0 48243
Rows Row Source Operation
------- ---------------------------------------------------
48243 SORT AGGREGATE (cr=145168 pr=0 pw=0 time=2251857 us)
57879 INDEX RANGE SCAN T_IDX_2 (cr=145168 pr=0 pw=0 time=1382547 us)(object...
它们处理的行数完全相同,而且块数也非常类似(之所以存在微小的差别,这是因为表中的行序有些
偶然性,而且Oracle相应地会做一些优化),它们使用了同样的CPU时间,而且在大约相同的耗用时间内 运行(再运行这个测试,CPU和ELAPSED这两个数字会有一点差别,但是平均来讲它们是一样的)。按照各 个列的差别大小来安排这些列在索引中的顺序并不会获得本质上的效率提升,另外如前所示,如果再考虑 到索引键压缩,可能还更倾向于把最没有选择性的列放在最前面。如果对索引采用COMPRESS 2,再运行前
面的例子,你会发现,对于给定情况下的这个查询,第一个查询执行的I/O次数大约是后者的2/3。
不过事实上,对于是把C1列放在C2列之前,这必须根据如果使用索引来决定。如果有大量如下的查

询:
select * from t where c1 = :x and c2 = :y;
select * from t where c2 = :y;
那么在T(C2,C1)上建立索引就更合理。以上这两个查询都可以使用这个索引。另外,通过使用索引
键压缩(我们在介绍IOT时讨论过,后面还将进一步分析),如果C2在前,就能建立一个更小的索引。这 是因为,C2的各个值会在索引中平均重复4次。如果C1和C2的平均长度都是11.字节,那么按道理这个 索引的条目就是2,000,000字节(100,000×20)。倘若在(C2,C1)上使用索引键压缩,可以把这个索引收 缩为11.250,000(100,000×11..5)字节,因为C2的4次重复中有3次都可以避免。
在Oracle 5中(不错,确实是“古老的”Oracle 5!),曾经认为应该把最有选择性的列放在索引的 最前面。其理由缘于Oracle 5实现索引压缩的方式(不同于索引键压缩)。这个特性在Oracle 6中就已经 去掉了,因为Oracle 6中 增加了行级锁。从那以后,“把最有差别的列放在索引最前面会使索引更小或更 有效率”的说法不再成立。看上去好像是这样,但实际上并非如此。如果利用索引键压缩,则恰恰相反, 因为反过来才会使索引更小(即把最没有差别的列放在索引最前面)。不过如前所述,还是应该根据如何使 用索引来做出决定。