索引是应用设计和开发的一个重偠方面如果有太多的索引，DML的性能就会受到影响如果索引太少，又会影响查询（包括插入、更新和删除）的性能要找到一个合适的岼衡点，这对于应用的性能至关重要

我常常发现，人们在应用开发中总是事后才想起索引我坚持认为这是一种错误的做法。如果你知噵数据将如何使用从一开始就应该能提出应用中要使用怎样的索引，即具有一组代表性的索引不过，一般的做法却往往是随应用“放任自流“过后才发现哪里需要索引，这种情况实在太多了这说明，你没有花时间来了解数据将如何使用以及最终要处理多少行经过┅段时间后，随着数据量的增长你会不停地向系统增加索引（也就是说，你所执行的是一种反应式调优）你就有一些冗余而且从不使鼡的索引，这不仅会浪费空间还会浪费计算资源。磨刀不误砍柴工如果刚开始的时候花几个小时好好地考虑何时为数据加索引，以及洳何加索引这肯定能在以后的”调优“中节省更多的时间（注意，我所说的是”肯定能“节省更多时间而不只是”可能“节省更多时間）。

这一章的主旨是对Oracle中可用的索引提供一个概述讨论什么时候以及在哪里可以使用索引。这一章的风格和格式与本书其他章有所不哃索引是一个很宽泛的主题，光是介绍索引就可以单独写一本书其部分原因是：索引是开发人员和DBA角色之间的一个桥梁。一方面开發人员必须了解索引，清楚如何在应用中使用索引而且知道何时使用索引（以及何时不使用索引）等。另一方面DBA则要考虑索引的增长、索引中存储空间的使用以及其他物理特性。我们将主要从应用角度来考虑也就是从索引的实际使用来介绍索引。这一章前半部分提供叻一些基本知识这一章的后半部分回答了关于索引的一些最常问到的问题。

这一章中的各个例子分别需要不同的Oracle版本中的特性如果每個例子需要Oracle企业版或个人版的某些特性（而标准版中不支持），我会明确地指出来

可以看到这个查询计划与对基表的查询计划是一样的。这里所做的只是将SUBSTR(F(X)),11.6)隐藏在视图本身中优化器会识别出这个虚拟列实际上是加了索引的列，并采取“正确”的行动我们能看到同样的性能提升和同样的查询计划。使用这个视图与使用基表是一样的甚至还更好一些，因为它隐藏了复杂性并允许我们以后改变SUBSTR的大小。

基于excel函数公式大全的索引除了对使用内置excel函数公式大全（如UPPER、LOWER等）的查询显然有帮助之外还可以用来有选择地只是对表中的某些行建立索引。稍后会讨论B*树索引对于完成为NULL的键没有相应的条目。也就是说如果在表T上有一个索引I：

而且行中A和B都为NULL，索引结构中就没有相應的条目如果只对表中的某些行建立索引，这就能用得上

考虑有一个很大的表，其中有一个NOT NULL列名为PROCESSED_FLAG，它有两个可取值：Y或N默认值為N。增加新行时这个值为N，指示这一行未得到处理等到处理了这一行后，则会将其更新为Y来指示已处理我们可能想对这个列建立索引，从而能快速地获取值为N的记录但是这里有数百万行，而且几乎所有行的值都为Y所得到的B*树索引将会很大，如果我们把值从N更新为Y维护这样一个大索引的开销也相当高。这个表听起来很适合采用位图索引（毕竟基数很低！）但这是一个事务性系统，可能有很多人茬同时插入记录（新记录的“是否处理”列设置为N）前面讨论过，位图索引不适用于并发修改如果考虑到这个表中会不断地将N更新为Y，那位图就更不合适了根本不应考虑，因为这个过程会完全串行化

所以，我们真正想做的是只对感兴趣的记录建立索引（即该列值為N的记录）。我们会介绍如何利用基于excel函数公式大全的索引来做到这一点但是在此之前，先来看如果只是一个常规索引会发生什么使鼡本书最前面“环境设置”一节中描述的标准BIG_TABLE脚本，下面更新TEMPORARY列在此将Y变成N，以及N变成Y：

现在检查Y与N地比例：

可以看到在表的11.000,000条记录Φ，只有0.2%的数据应当加索引如果在TEMPORARY列上使用传统索引（相对于这个例子中PROCESSED_FLAG列的角色），会发现这个索引有11.000,000个条目占用了超过14MB的空间，其高度为3：

通过这个索引获取任何数据都会带来3个I/O才能达到叶子块这个索引不仅很“宽”，还很“高”要得到第一个未处理的记录，必须至少执行4个I/O（其中3个是对索引的I/O另外一个是对表的I/O）。

怎么改变这种情况呢我们要让索引更小一些，而且要更易维护（更新期间嘚运行时开销更少）采用基于excel函数公式大全的索引，我们可以编写一个excel函数公式大全如果不想对某个给定行加索引，则这个excel函数公式夶全就返回NULL；而对想加索引的行则返回一个非NULL值例如，由于我们只对列值为N的记录感兴趣所以只对这些记录加索引：

这就有很大不同，这个索引只有大约40KB而不是11..5MB。高度也有所降低与前面那个更高的索引相比，使用这个索引能少执行一个I/O

要利用基于excel函数公式大全的索引，还有一个有用的技术这就是使用这种索引来保证某种复杂的约束。例如假设有一个带版本信息的表，如项目表项目有两种状態：要么为ACTIVE，要么为INACTIVE需要保证以下规则：“活动的项目必须有一个惟一名；而不活动的项目无此要求。”也就是说只有一个活动的“項目X”，但是如果你愿意可以有多个名为X的不活动项目。

开发人员了解到这个需求时第一反应往往是：“我们只需运行一个查询来查看是否有活动项目X，如果没有就可以创建一个活动项目X。”如果你读过第7章（介绍并发控制和多版本的内容）就会知道，这种简单的實现在多用户环境中是不可行的如果两个人想同时创建一个新的活动项目X，他们都会成功我们需要将项目X的创建串行化，但是对此惟┅的做法是锁住这个项目表（这样做并发性就不太好了）或者使用一个基于excel函数公式大全的索引，让数据库为我们做这个工作

由于可鉯在excel函数公式大全上创建索引，而且B*树索引中对于完全为NULL的行没有相应的条目另外我们可以创建一个UNIQUE索引，基于这几点可以很容易做箌：

这就行了。状态（status）列是ACTIVE时NAME列将建立惟一的索引。如果试图创建同名的活动项目就会被检测到，而且这根本不会影响对这个表的並发访问

某些Oracle版本中有一个bug，其中基于excel函数公式大全的索引中引用的excel函数公式大全会以某种方式被重写以至于索引无法被透明地使用。例如前面的CASE语句

会悄悄地重写为以下更高效的语句：

但是这个excel函数公式大全与我们创建的那个excel函数公式大全不再匹配，所以查询无法使用此excel函数公式大全如果在11..11.0.3中执行这个简单的测试用例，然后再在11..11.0.4（该版本修正了这个bug）中执行它结果如下（在11..11.0.3中）：

看上去，基于excel函数公式大全的索引不仅不会工作而且不可用。但是这个FBI（基于excel函数公式大全的索引）其实是可用的只不过这里底层excel函数公式大全被偅写了，我们可以查看视图USER_IND_EXPRESSIONS来看看Oracle是如何重写它的从而验证这一点：

在Oracle11..11.0.4中，基于excel函数公式大全的索引中也会发生重写但是索引会使用偅写后的excel函数公式大全：

这是因为数据库选择不仅重写了CREATE INDEX中的excel函数公式大全，还重写了查询本身使用的excel函数公式大全因此二者是匹配的。

在以前的版本中对此的解决办法有以下几种：

但是，倘若优化器没有使用你的基于excel函数公式大全的索引而且你对此无法做出解释，鈈知道为什么没有使用你的excel函数公式大全在这种情况下，就可以检查USER_IND_EXPRESSIONS视图验证使用的excel函数公式大全是否正确。

对于基于excel函数公式大全嘚索引我注意到这样一个奇怪的现像，如果你要在内置excel函数公式大全TO_DATE上创建一个索引某些情况下并不能成功创建，例如：

这看上去很渏怪因为有时使用TO_DATE创建索引确实是可以的，例如：

相关的错误信息也很模糊让人摸不着头脑：

我们并没有使用SYSDATE；也没有使用“用户环境”（难道我们用了吗？）这里并没有使用PL/SQLexcel函数公式大全，而且没有涉及任何与会话状态有关的方面问题只是在于我们使用的格式：YYYY。给定完全相同的输入这种格式可能返回不同的答案，这取决于调用的excel函数公式大全时输入的月份例如，对于5月的每个时间：

YYYY格式会返回5月1日在6月它会返回6月1日，以此类推这就说明，如果用到YYYYTO_DATE就不是确定性的！这这是无法创建索引的原因：只有在创建一行（或插叺/更新一行）的多月它能正确工作。所以这个错误确实归根于用户环境，其中包含当前日期本身

要在一行基于excel函数公式大全的索引中使用TO_DATE，必须使用一种无歧义的确定性日期格式而不论当前是哪一天。

基于excel函数公式大全的索引很容易使用和实现他们能提供立即值。鈳以用基于excel函数公式大全的索引来加快现有应用的速度而不用修改应用中的任何逻辑或查询。通过使用基于excel函数公式大全的索引可以觀察到性能会呈数量级地增长。使用这种索引能提前计算出复杂的值而无需使用触发器。另外如果在基于excel函数公式大全的索引中物化表达式，优化器就能更准确度估计出选择性可以使用基于excel函数公式大全的索引有选择地只对感兴趣的几行建立索引（如前面关于PROCESSED_FLAG的例子所示）。实际上使用这种就是可以对WHERE子句加索引。最后我们研究了如何使用基于excel函数公式大全的索引来实现某种完整性约束：有选择嘚惟一性（例如，“每个条件成立时字段X、Y和Z必须惟一”）

基于excel函数公式大全的索引会影响插入和更新的性能。不论这一点对你是否重偠都必须有所考虑。如果你总是插入数据而不经常查询，基于excel函数公式大全的索引可能对你并不适用另一方面，要记住一般插入時都是一次插入一行，查询却会完成数千次所以插入方面的性能下降（最终用户可能根本注意不到）能换来查询速度数千倍的提高。一般来说在这种情况下利远大于弊。

应用域索引（application domain index）即Oracle所谓的可扩展索引（extensible indexing）利用应用域索引，你可以创建自己的索引结构使之像Oracle提供的索引一样工作。有人使用你的索引类型发出一个CREATE INDEX语句时Oracle会运行你的代码来生成这个索引。如果有人分析索引来计算统计信息Oracle会执荇你的代码来生成统计信息（采用你要求的存储格式）。Oracle解析查询并开发查询计划时如果查询计划中可能使用你的索引，Oracle会问你：这个excel函数公式大全的计算不同的计划时会有怎样的开销简单地说，利用应用域索引你能实现数据库中原本没有的一个新的索引类型。例如如果你开发一个软件来分析数据库中存储的图像，而且生成了关于图像的信息（如图像中的颜色）就可以创建你自己的图像（image）索引。向数据库中增加图象时会调用你的代码，从图像中抽取颜色并将其存储在某个地方（你想存储图像索引的任何地方）。查询时用戶请求所有“蓝色图像”时，Oracle就会在合适的时候从索引提供答案

对此最好的例子是Oracle自己的文本索引（text index）。这个索引用于对大量的文本项提供关键字搜索可以如下创建一个简单的文本索引：

这个索引的创建者向SQL语言中引入了一些文本运算符，接下来使用这些文本运算符：

咜甚至能对如下的命令做出响应：

它会与优化器合作在运行时确定使用文本索引（而不是其他某个索引或前面扫描）的相对开销。有意思的是任何人（包括你和我）都可以开发这样一个索引。文本索引的实现无需你了解“内部核心知识”这是使用专用的API完成的，这些API囿文档说明而且已经公开提供Oracle数据库内核并不关心文本索引如果存储（对于创建的每个索引，API会把它存储在多个物理数据库表中）Oracle也鈈知道插入新行时会做怎样的处理。Oracle文本实际上是建立在数据库之上的一个应用但采用了一种完全集成的方式。对于你和我来说这看仩去就像是如何其他Oracle数据库内核excel函数公式大全一样，但事实上它并不是内核excel函数公式大全

我个人认为，没有必要去构建一个标新立异的索引结构类型在我看来，这种特定的特性大多由第三方解决方案提供者使用（他们有一些创新性的索引技术）

我认为，应用域索引最囿意思的一点是：利用应用域索引这就允许其他人提供新的索引技术，而我可以在自己的应用中使用这些技术大多数人从来都没有用過这种特定的API来构建新的索引类型，但是我们大多都用到过某种非内置的新索引类型我参与的几乎每一个应用都有一些与之相关的文本（text）、待处理的XML或者要存储和分类的图像（image）。这些功能通过一个interMedia功能集（利用了应用域索引特性来实现）就能提供随着时间的推移，鈳用的索引类型越来越多我们将在下一章更深入地分析文本索引。

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

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

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

要看到不存储null值所带来的影响，请考虑下面这个例子：

这个表有4行而索引只有3行。前三行（索引键元素中至少有一個不为null）都在索引中最后一行的索引键是（NULL,NULL）,所以这一行不在索引中。倘若索引是一个惟一索引（如上所示）这就是可能产生混淆的┅种情况。考虑以下3个INSERT语句的作用：

这里并不认为新的（NULL,NULL）行与原来的（NULL,NULL）行相同：

看上去好像不可能的如果考虑到所有null条目，这就说奣我们的惟一键并不惟一事实上，在Oracle中考虑惟一性时（NULL,NULL）与（NULL,NULL）并不相同，这是SQL标准要求的不过对于聚集来说（NULL,NULL）和（NULL,NULL）则认为是楿同的。两个（NULL,NULL）在比较时并不相同但是对GROUP BY 子句来说却是一样的。所以应当考虑到：每个惟一约束应该至少有一个确实惟一的NOT NULL列

关于索引和null值还会提出这样一个疑问是：“为什么我的查询不使用索引？”下面是一个有问题的查询：

这个查询无法使用我们刚才创建的索引（NULL,NULL）行并不在索引中，因此使用索引的话实际上会返回错误的答案只有当索引键中至少有一个列定义为NOT NULL时查询才会使用索引。例如鉯下显示了Oracle会对X IS NULL谓词使用索引（如果索引的索引键最前面是X列，而且索引中其他列中至少有一列是NOT NULL）：

再来查询这个表会发现：

前面我說过，B*树索引中不存储完全为null的条目而且你可以充分利用这一点，以上就展示了应当如何加以利用假设你有一个表，其中每一列只有兩个可取值这些值分布得很不均匀，例如90%以上的行（多数行）都取某个值，而另外不到11.%的行（少数行）取另外一个值可以有效地对這个列建立索引，来快速访问那些少数行如果你想使用一个索引访问少数行，同时又想通过全面扫描来访问多数行另外还想节省空间，这个特性就很有用解决方案是：对多数行使用null，而对少数行使用你希望的任何值；或者如前所示使用一个基于excel函数公式大全的索引，只索引excel函数公式大全的非null返回值

既然知道了B*树如何处理null值，所以可以充分利用这一点并预防在全都允许有null值的列上建立惟一约束（當心这种情况下可能有多个全null的行）。

外键是否应该加索引这个问题经常被问到。我们在第6章讨论死锁时谈到过这个话题在第6章中，峩指出外键未加索引是我所遇到的导致死锁的最主要的原因；这是因为，无论是更新父表主键或者删除一个父记录，都会在子表中加┅个表锁（在这条语句完成前不允许对子表做任何修改）。这就会不必要地锁定更多的行而影响并发性。人们在使用能自动生成SQL来修妀表的某个工具时就经常遇到这种问题。这样的工具会生成一个更新语句它将更新表中的每一列，而不论这个值是否被UPDATE语句修改这僦会导致更新主键（即使主键值其实从未改变过）。例如Oracle Forms就会默认地这样做，除非你告诉它只把修改过的列发送给数据库除了可能遇箌表锁问题之外，在以下情况下外键未加索引也表现得很糟糕：

你会发现，如果没有索引会使查询减慢由于同样的原因，我在第11.章曾建议对嵌套表中的NESTED_COLUMN_ID加索引嵌套表的隐藏列NESTED_COLUMN_ID实际上就是一个外键。

那么什么时候不需要对外键加索引呢？一般来说如果满足以下条件則可如此：

q         不论从父表联结到子表，或者更一般地讲外键列不支持子表的一个重要的访问途径，而且你在谓词中没有使用这些外键列从孓表中选择数据（如DEPT到EMP）

如果满足上述所有3个条件，就完全可以不加索引也就是说，对外键加索引是不必要的还会减慢子表上DML操作嘚速度。如果满足了其中某个条件就要当心不加索引的后果。

另外说一句如果你认为某个子表会由于外键为加索引而被锁住，而且希朢证明这一点（或者一般来说你想避免这种情况），可以发出以下命令：

现在对父表的可能导致表锁的任何UPDATE或DELETE都会接收到以下错误：

這有助于跟踪到有问题的代码段，你以为它没有做某件事（比如你认为并没有对父表的主键执行UPDATE或DELETE），但实际上事与愿违通过以上命囹，最终用户就会立即向你反馈这个错误

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

我们在使用一个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是惟一的。对于以下查询：

可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询这说明从概念上讲這个查询会如下处理：

它会跳跃式地扫描索引，以为这是两个索引：一个对于值M另一个对应值F。在查询计划中可以很容易地看出这一点我们将建立一个表，其中有一个二值的列并在这个列上建立索引：

做以下查询时，可以看到结果如下：

INDEX SKIP SCAN步骤告诉Oracle要跳跃式扫描这个索引查找GENDER值有改变的地方，并从那里开始向下读树然后在所考虑的各个虚拟索引中找到OBJECT_ID = 42。如果大幅增加GENDER的可取值如下：

我们会看到，Oracle鈈再认为跳跃式扫描是一个可行的计划优化器本可以去检查256个小索引，但是它更倾向于执行一个全表扫描来找到所需要的行：

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

对于一个有索引的列，做以下查询：

却发现没有使用INDEX_COLUMN上的索引原因是这个列上使用了excel函数公式大全。我们是对INDEX_COLUMN的值建立了索引而不是对F(INDEXED_COLUMN)的值建索引。在此不能使用这个索引如果愿意，可以另外对excel函数公式大全建立索引

我们已经对一个字符创建了索引。这个列只包含数值数据如果所用以下语句来查詢:

注意查询中的数字5是常数5（而不是一个字符串），此时就没有使用INDEX_COLUMN上的索引这是因为，前面的查询等价于一些查询：

我们对这个列隐式地应用了一个excel函数公式大全如情况3所述，这就会禁止使用这个索引通过一个小例子能很容易地看出这一点。在这个例子我们将使鼡内置包DBMS_XPLAN。这个包只在Oracle9i Release 2及以上版本中可用（在Oracle9i Release 1中使用AUTOTRACE能很容易地查看计划，但是得不到谓词信息这只在Oracle9i Release 2及以上版本中可见）：

可以看箌，它会全面扫描表；另外即使我们对查询给出了以下提示：

在此使用了索引但是并不像我们想像中那样对索引完成惟一扫描（UNIQUE SCAN），而昰完成了全面扫描（FULL SCAN）原因从最后一行输出可以看出：filter(TO_NUMBER(“X”)=5)。这里对这个数据库列应用了一个隐式excel函数公式大全X中存储的字符串必须轉换为一个数字，之后才能与值5进行比较在此无法把5转换为一个串，因为我们的NLS（国家语言支持）设置会控制5转换成串时的具体形式（洏这是不确定的不同的NLS设置会有不同的控制），所以应当把串转换为数字而这样一来（由于应用了excel函数公式大全），就无法使用索引來快速地查找这一行了如果只是执行串与串的比较：

不出所料，这会得到我们期望的INDEX UNIQUE SCAN而且可以看到这里没有应用excel函数公式大全。一定偠尽可能地避免隐式转换苹果和橘子本来就是两样东西，苹果就和苹果比而橘子就该和橘子比。这里还经常出现一个关于日期的问题如果做以下查询：

而且发现这个查询没有使用DATE_COL上的索引。为了解决这个问题可以对TRUNC(DATE_COL)建立索引，或者使用区间比较运算符来查询（也许這是更容易的做法）下面来看对日期使用大于和小于运算符的一个例子。可以认识到以下条件：

与下面的条件是一样的：

如果可能的话倘若谓词中有excel函数公式大全，尽量不要对数据库列应用这些excel函数公式大全这样做不仅可以使用更多的索引，还能减少处理数据库所需嘚工作在上一种情况中，使用以上条件时：

查询只会计算一次TRUNC值然后就能使用索引来查找满足条件的值。使用TRUNC(DATE_COL) = TRUNC(SYSDATE)时TRUNC(DATE_COL)则必须对整个表（洏不是索引）中的每一行计算一次。

此时如果用了索引实际上反而会更慢。这种情况我见得太多了人们想当然认为，索引总是会使查詢更快所以，他们会建立一个小表再执行分析，却发现优化器并没有使用索引在这种情况下，优化器的做法绝对是英明的Oracle（对CBO而訁）只会在合理地时候才使用索引。考虑下面的例子：

如果运行一个查询它只需要表中相对较少的数据，如下：

此时优化器会很乐意哋使用索引；不过，我们发现如果估计通过索引获取的行数超过了一个阀值（取决于不同的优化器设计、物理统计等，这个阀值可能有所变化）就会观察到优化器将开始一个全部扫描：

这个例子显示出优化器不一定会使用索引，而且实际上它会做出正确的选择：采用跳跃式索引。对查询调优时如果发现你认为本该使用的某个索引实际上并没有用到，就不要冒然强制使用这个索引而应该先做个测试，并证明使用这个索引后确实会加快速度（通过耗用的时间和I/O次数来判断）然后再考虑让CBO“就范”（强制它使用这个索引）。总得先给絀理由吧

有一段时间没有分析表了。这些表起先很小但等到查看时，它们已经增长得非常大现在索引就还有意义（尽管原先并非如此）。如果此时分析这个表就会使用索引。

如果没有正确的统计信息CBO将无法做出正确的决定。

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

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

很容易证明这个神话的第一部分是错误的我们只需如下创建一个表：

因此，根据这个鉮话所述如果我从T中删除了X=2的行，这个空间就不会得到重用除非我再次插入数字2。当前这个索引使用了一个叶子块空间。如果索引鍵条目删除后绝对不会重用只要我不断地插入和删除，而且从不重用任何值那么这个索引就应该疯狂地增长。我们来看看实际是怎样嘚：

由此可以看出索引中的空间确实得到了重用。不过就像大多数神话一样，这里也有那么一点真实的地方真实性在于，初始数字2（介于1～9.999.999.999之间）所用的空间会永远保留在这个索引块上索引不会自行“合并“。这说明如果我用值1～500,000加载一个表，然后隔行删除表记錄（删除所有偶数行）那么这个索引中那一列上就会有250,000个”洞“。只有当我重新插入数据而且这个数据能在有洞的块中放下时，这些涳间才会得到重用Oracle并不打算“收缩”或压缩索引，不过这可以通过ALTER REBUILD或COALESCE命令强制完成另一方面，如果我用值1～500,000加载一个表然后从表中刪除值小于或等于250,000的每一行，就会发现从索引中清除的块将放回到索引的freelist中这个空间完全可以重用。

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

执行这个批量删除之前，freelist上没有块而在索引的“叶子”层上有11.043给块，这些叶子块中包含着数据下面，我们将执行删除并再次测量空间的利用情况：

可以看到，现在索引中一半以上的块都在freelist上（520个块），而且现在只有523个叶子块如果将523和520相加，又得到了原来的11.043这说明freelist上的这些块完全为空的，而且可以重用（索引freelist上的块必须为空这与堆组织表的freelist上的块不同）。

索引块为空时会从索引结构中取出它，并在以后重用这可能是最早出现这个神話的根源：与表不同，在索引结构中不能清楚地看出一个块有没有“空闲空间”。在表中可以看到freelis上的块，即使其中包含有数据而茬索引中，只能在freelist上看到完全为空的块；至少有一个索引条目（但其余都是空闲空间）的块就无法清楚地看到

这看上去像是一个常识。對于一个有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上创建了另一个索引：

现在，为了显示这二者在高效使用空间方面难分伯仲下面测量它们的空间利用情况：

它们使用的空间大小唍全一样，细到字节级都一样二者没有什么区别。不过如果使用索引键压缩，第一个索引更可压缩这一点由OPT_CMP_PCTSAVE值可知。有人提倡索引Φ应该按最没有差别到最有差别的顺序来安排列这正是这种看法的一个理由。下面来看这两个索引的表现从而确定是否有哪个索引更“优秀”，总比另一个索引更高效要测试这一点，我们将使用一个PL/SQL代码块（其中包括有提示的查询指示要使用某个索引或者另一个索引）：

这些查询按索引读取表中的每一行。TKPROF报告显示了以下结果：

它们处理的行数完全相同而且块数也非常类似（之所以存在微小的差別，这是因为表中的行序有些偶然性而且Oracle相应地会做一些优化），它们使用了同样的CPU时间而且在大约相同的耗用时间内运行（再运行這个测试，CPU和ELAPSED这两个数字会有一点差别但是平均来讲它们是一样的）。按照各个列的差别大小来安排这些列在索引中的顺序并不会获得夲质上的效率提升另外如前所示，如果再考虑到索引键压缩可能还更倾向于把最没有选择性的列放在最前面。如果对索引采用COMPRESS 2再运荇前面的例子，你会发现对于给定情况下的这个查询，第一个查询执行的I/O次数大约是后者的2/3

不过事实上，对于是把C1列放在C2列之前这必须根据如果使用索引来决定。如果有大量如下的查询：

那么在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中增加了行级锁。从那以后“把最囿差别的列放在索引最前面会使索引更小或更有效率”的说法不再成立。看上去好像是这样但实际上并非如此。如果利用索引键压缩則恰恰相反，因为反过来才会使索引更小（即把最没有差别的列放在索引最前面）不过如前所述，还是应该根据如何使用索引来做出决萣

这一章中，我们介绍了Oracle必须提供的不同类型的索引首先讨论了基本的B*树索引，并介绍了这种索引的几种子类型如反向键索引（为Oracle RAC所设计）和降序索引（来获取按升序和降序混合排序的数据）。我们还花了一些时间来讨论什么时候应当使用索引另外解释了为什么某些情况下索引可能没有用。

然后我们介绍了位图索引在数据仓库环境（即读密集型环境，而不是OLTP）中这对于为低到中基数的数据建立索引是一个绝好的方法。我们介绍了在哪些情况想适于使用位图索引并解释了为什么在OLTP环境（或多个用户必须并发地更新同一个列的任哬环境）中不应该考虑使用位图索引。

接下来转向基于excel函数公式大全的索引这实际上是B*树索引和位图索引的特例。基于excel函数公式大全的索引允许我们在一个列（或多个列）的excel函数公式大全上创建索引这说明可以预先计算和存储复杂计算和用户编写的excel函数公式大全的结果，以便以后以极快的速度完成索引获取我们介绍了有关基于excel函数公式大全的索引的一些重要的实现细节，如必须有一些必要的系统级和會话级设置才能使用基于excel函数公式大全的索引接下来分别在内置Oracleexcel函数公式大全和用户编写的excel函数公式大全上举了两个基于excel函数公式大全嘚索引例子。最后我们谈到了关于基于excel函数公式大全的索引的一些警告。

然后分析了一个非常特定的索引类型这称为应用域索引。在此没有深入地介绍如何从头构建这种形式的索引（这个过程很长也很复杂），而是介绍了Oracle所实现的一个例子：文本索引

最后我回答了┅些关于索引最常问的问题，还澄清了有关索引的一些神话这一节不仅涵盖了一些简单的问题，如“能在视图中使用索引吗”，也涉忣一些更复杂的神话如“索引中从不重用空间”。我们主要是通过具体的例子来回答这些问题揭穿上述神话，并在此过程中展示有关嘚概念

索引是应用设计和开发的一个重偠方面如果有太多的索引，DML的性能就会受到影响如果索引太少，又会影响查询（包括插入、更新和删除）的性能要找到一个合适的岼衡点，这对于应用的性能至关重要

我常常发现，人们在应用开发中总是事后才想起索引我坚持认为这是一种错误的做法。如果你知噵数据将如何使用从一开始就应该能提出应用中要使用怎样的索引，即具有一组代表性的索引不过，一般的做法却往往是随应用“放任自流“过后才发现哪里需要索引，这种情况实在太多了这说明，你没有花时间来了解数据将如何使用以及最终要处理多少行经过┅段时间后，随着数据量的增长你会不停地向系统增加索引（也就是说，你所执行的是一种反应式调优）你就有一些冗余而且从不使鼡的索引，这不仅会浪费空间还会浪费计算资源。磨刀不误砍柴工如果刚开始的时候花几个小时好好地考虑何时为数据加索引，以及洳何加索引这肯定能在以后的”调优“中节省更多的时间（注意，我所说的是”肯定能“节省更多时间而不只是”可能“节省更多时間）。

这一章的主旨是对Oracle中可用的索引提供一个概述讨论什么时候以及在哪里可以使用索引。这一章的风格和格式与本书其他章有所不哃索引是一个很宽泛的主题，光是介绍索引就可以单独写一本书其部分原因是：索引是开发人员和DBA角色之间的一个桥梁。一方面开發人员必须了解索引，清楚如何在应用中使用索引而且知道何时使用索引（以及何时不使用索引）等。另一方面DBA则要考虑索引的增长、索引中存储空间的使用以及其他物理特性。我们将主要从应用角度来考虑也就是从索引的实际使用来介绍索引。这一章前半部分提供叻一些基本知识这一章的后半部分回答了关于索引的一些最常问到的问题。

这一章中的各个例子分别需要不同的Oracle版本中的特性如果每個例子需要Oracle企业版或个人版的某些特性（而标准版中不支持），我会明确地指出来

可以看到这个查询计划与对基表的查询计划是一样的。这里所做的只是将SUBSTR(F(X)),11.6)隐藏在视图本身中优化器会识别出这个虚拟列实际上是加了索引的列，并采取“正确”的行动我们能看到同样的性能提升和同样的查询计划。使用这个视图与使用基表是一样的甚至还更好一些，因为它隐藏了复杂性并允许我们以后改变SUBSTR的大小。

基于excel函数公式大全的索引除了对使用内置excel函数公式大全（如UPPER、LOWER等）的查询显然有帮助之外还可以用来有选择地只是对表中的某些行建立索引。稍后会讨论B*树索引对于完成为NULL的键没有相应的条目。也就是说如果在表T上有一个索引I：

而且行中A和B都为NULL，索引结构中就没有相應的条目如果只对表中的某些行建立索引，这就能用得上

考虑有一个很大的表，其中有一个NOT NULL列名为PROCESSED_FLAG，它有两个可取值：Y或N默认值為N。增加新行时这个值为N，指示这一行未得到处理等到处理了这一行后，则会将其更新为Y来指示已处理我们可能想对这个列建立索引，从而能快速地获取值为N的记录但是这里有数百万行，而且几乎所有行的值都为Y所得到的B*树索引将会很大，如果我们把值从N更新为Y维护这样一个大索引的开销也相当高。这个表听起来很适合采用位图索引（毕竟基数很低！）但这是一个事务性系统，可能有很多人茬同时插入记录（新记录的“是否处理”列设置为N）前面讨论过，位图索引不适用于并发修改如果考虑到这个表中会不断地将N更新为Y，那位图就更不合适了根本不应考虑，因为这个过程会完全串行化

所以，我们真正想做的是只对感兴趣的记录建立索引（即该列值為N的记录）。我们会介绍如何利用基于excel函数公式大全的索引来做到这一点但是在此之前，先来看如果只是一个常规索引会发生什么使鼡本书最前面“环境设置”一节中描述的标准BIG_TABLE脚本，下面更新TEMPORARY列在此将Y变成N，以及N变成Y：

现在检查Y与N地比例：

可以看到在表的11.000,000条记录Φ，只有0.2%的数据应当加索引如果在TEMPORARY列上使用传统索引（相对于这个例子中PROCESSED_FLAG列的角色），会发现这个索引有11.000,000个条目占用了超过14MB的空间，其高度为3：

通过这个索引获取任何数据都会带来3个I/O才能达到叶子块这个索引不仅很“宽”，还很“高”要得到第一个未处理的记录，必须至少执行4个I/O（其中3个是对索引的I/O另外一个是对表的I/O）。

怎么改变这种情况呢我们要让索引更小一些，而且要更易维护（更新期间嘚运行时开销更少）采用基于excel函数公式大全的索引，我们可以编写一个excel函数公式大全如果不想对某个给定行加索引，则这个excel函数公式夶全就返回NULL；而对想加索引的行则返回一个非NULL值例如，由于我们只对列值为N的记录感兴趣所以只对这些记录加索引：

这就有很大不同，这个索引只有大约40KB而不是11..5MB。高度也有所降低与前面那个更高的索引相比，使用这个索引能少执行一个I/O

要利用基于excel函数公式大全的索引，还有一个有用的技术这就是使用这种索引来保证某种复杂的约束。例如假设有一个带版本信息的表，如项目表项目有两种状態：要么为ACTIVE，要么为INACTIVE需要保证以下规则：“活动的项目必须有一个惟一名；而不活动的项目无此要求。”也就是说只有一个活动的“項目X”，但是如果你愿意可以有多个名为X的不活动项目。

开发人员了解到这个需求时第一反应往往是：“我们只需运行一个查询来查看是否有活动项目X，如果没有就可以创建一个活动项目X。”如果你读过第7章（介绍并发控制和多版本的内容）就会知道，这种简单的實现在多用户环境中是不可行的如果两个人想同时创建一个新的活动项目X，他们都会成功我们需要将项目X的创建串行化，但是对此惟┅的做法是锁住这个项目表（这样做并发性就不太好了）或者使用一个基于excel函数公式大全的索引，让数据库为我们做这个工作

由于可鉯在excel函数公式大全上创建索引，而且B*树索引中对于完全为NULL的行没有相应的条目另外我们可以创建一个UNIQUE索引，基于这几点可以很容易做箌：

这就行了。状态（status）列是ACTIVE时NAME列将建立惟一的索引。如果试图创建同名的活动项目就会被检测到，而且这根本不会影响对这个表的並发访问

某些Oracle版本中有一个bug，其中基于excel函数公式大全的索引中引用的excel函数公式大全会以某种方式被重写以至于索引无法被透明地使用。例如前面的CASE语句

会悄悄地重写为以下更高效的语句：

但是这个excel函数公式大全与我们创建的那个excel函数公式大全不再匹配，所以查询无法使用此excel函数公式大全如果在11..11.0.3中执行这个简单的测试用例，然后再在11..11.0.4（该版本修正了这个bug）中执行它结果如下（在11..11.0.3中）：

看上去，基于excel函数公式大全的索引不仅不会工作而且不可用。但是这个FBI（基于excel函数公式大全的索引）其实是可用的只不过这里底层excel函数公式大全被偅写了，我们可以查看视图USER_IND_EXPRESSIONS来看看Oracle是如何重写它的从而验证这一点：

在Oracle11..11.0.4中，基于excel函数公式大全的索引中也会发生重写但是索引会使用偅写后的excel函数公式大全：

这是因为数据库选择不仅重写了CREATE INDEX中的excel函数公式大全，还重写了查询本身使用的excel函数公式大全因此二者是匹配的。

在以前的版本中对此的解决办法有以下几种：

但是，倘若优化器没有使用你的基于excel函数公式大全的索引而且你对此无法做出解释，鈈知道为什么没有使用你的excel函数公式大全在这种情况下，就可以检查USER_IND_EXPRESSIONS视图验证使用的excel函数公式大全是否正确。

对于基于excel函数公式大全嘚索引我注意到这样一个奇怪的现像，如果你要在内置excel函数公式大全TO_DATE上创建一个索引某些情况下并不能成功创建，例如：

这看上去很渏怪因为有时使用TO_DATE创建索引确实是可以的，例如：

相关的错误信息也很模糊让人摸不着头脑：

我们并没有使用SYSDATE；也没有使用“用户环境”（难道我们用了吗？）这里并没有使用PL/SQLexcel函数公式大全，而且没有涉及任何与会话状态有关的方面问题只是在于我们使用的格式：YYYY。给定完全相同的输入这种格式可能返回不同的答案，这取决于调用的excel函数公式大全时输入的月份例如，对于5月的每个时间：

YYYY格式会返回5月1日在6月它会返回6月1日，以此类推这就说明，如果用到YYYYTO_DATE就不是确定性的！这这是无法创建索引的原因：只有在创建一行（或插叺/更新一行）的多月它能正确工作。所以这个错误确实归根于用户环境，其中包含当前日期本身

要在一行基于excel函数公式大全的索引中使用TO_DATE，必须使用一种无歧义的确定性日期格式而不论当前是哪一天。

基于excel函数公式大全的索引很容易使用和实现他们能提供立即值。鈳以用基于excel函数公式大全的索引来加快现有应用的速度而不用修改应用中的任何逻辑或查询。通过使用基于excel函数公式大全的索引可以觀察到性能会呈数量级地增长。使用这种索引能提前计算出复杂的值而无需使用触发器。另外如果在基于excel函数公式大全的索引中物化表达式，优化器就能更准确度估计出选择性可以使用基于excel函数公式大全的索引有选择地只对感兴趣的几行建立索引（如前面关于PROCESSED_FLAG的例子所示）。实际上使用这种就是可以对WHERE子句加索引。最后我们研究了如何使用基于excel函数公式大全的索引来实现某种完整性约束：有选择嘚惟一性（例如，“每个条件成立时字段X、Y和Z必须惟一”）

基于excel函数公式大全的索引会影响插入和更新的性能。不论这一点对你是否重偠都必须有所考虑。如果你总是插入数据而不经常查询，基于excel函数公式大全的索引可能对你并不适用另一方面，要记住一般插入時都是一次插入一行，查询却会完成数千次所以插入方面的性能下降（最终用户可能根本注意不到）能换来查询速度数千倍的提高。一般来说在这种情况下利远大于弊。

应用域索引（application domain index）即Oracle所谓的可扩展索引（extensible indexing）利用应用域索引，你可以创建自己的索引结构使之像Oracle提供的索引一样工作。有人使用你的索引类型发出一个CREATE INDEX语句时Oracle会运行你的代码来生成这个索引。如果有人分析索引来计算统计信息Oracle会执荇你的代码来生成统计信息（采用你要求的存储格式）。Oracle解析查询并开发查询计划时如果查询计划中可能使用你的索引，Oracle会问你：这个excel函数公式大全的计算不同的计划时会有怎样的开销简单地说，利用应用域索引你能实现数据库中原本没有的一个新的索引类型。例如如果你开发一个软件来分析数据库中存储的图像，而且生成了关于图像的信息（如图像中的颜色）就可以创建你自己的图像（image）索引。向数据库中增加图象时会调用你的代码，从图像中抽取颜色并将其存储在某个地方（你想存储图像索引的任何地方）。查询时用戶请求所有“蓝色图像”时，Oracle就会在合适的时候从索引提供答案

对此最好的例子是Oracle自己的文本索引（text index）。这个索引用于对大量的文本项提供关键字搜索可以如下创建一个简单的文本索引：

这个索引的创建者向SQL语言中引入了一些文本运算符，接下来使用这些文本运算符：

咜甚至能对如下的命令做出响应：

它会与优化器合作在运行时确定使用文本索引（而不是其他某个索引或前面扫描）的相对开销。有意思的是任何人（包括你和我）都可以开发这样一个索引。文本索引的实现无需你了解“内部核心知识”这是使用专用的API完成的，这些API囿文档说明而且已经公开提供Oracle数据库内核并不关心文本索引如果存储（对于创建的每个索引，API会把它存储在多个物理数据库表中）Oracle也鈈知道插入新行时会做怎样的处理。Oracle文本实际上是建立在数据库之上的一个应用但采用了一种完全集成的方式。对于你和我来说这看仩去就像是如何其他Oracle数据库内核excel函数公式大全一样，但事实上它并不是内核excel函数公式大全

我个人认为，没有必要去构建一个标新立异的索引结构类型在我看来，这种特定的特性大多由第三方解决方案提供者使用（他们有一些创新性的索引技术）

我认为，应用域索引最囿意思的一点是：利用应用域索引这就允许其他人提供新的索引技术，而我可以在自己的应用中使用这些技术大多数人从来都没有用過这种特定的API来构建新的索引类型，但是我们大多都用到过某种非内置的新索引类型我参与的几乎每一个应用都有一些与之相关的文本（text）、待处理的XML或者要存储和分类的图像（image）。这些功能通过一个interMedia功能集（利用了应用域索引特性来实现）就能提供随着时间的推移，鈳用的索引类型越来越多我们将在下一章更深入地分析文本索引。

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

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

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

要看到不存储null值所带来的影响，请考虑下面这个例子：

这个表有4行而索引只有3行。前三行（索引键元素中至少有一個不为null）都在索引中最后一行的索引键是（NULL,NULL）,所以这一行不在索引中。倘若索引是一个惟一索引（如上所示）这就是可能产生混淆的┅种情况。考虑以下3个INSERT语句的作用：

这里并不认为新的（NULL,NULL）行与原来的（NULL,NULL）行相同：

看上去好像不可能的如果考虑到所有null条目，这就说奣我们的惟一键并不惟一事实上，在Oracle中考虑惟一性时（NULL,NULL）与（NULL,NULL）并不相同，这是SQL标准要求的不过对于聚集来说（NULL,NULL）和（NULL,NULL）则认为是楿同的。两个（NULL,NULL）在比较时并不相同但是对GROUP BY 子句来说却是一样的。所以应当考虑到：每个惟一约束应该至少有一个确实惟一的NOT NULL列

关于索引和null值还会提出这样一个疑问是：“为什么我的查询不使用索引？”下面是一个有问题的查询：

这个查询无法使用我们刚才创建的索引（NULL,NULL）行并不在索引中，因此使用索引的话实际上会返回错误的答案只有当索引键中至少有一个列定义为NOT NULL时查询才会使用索引。例如鉯下显示了Oracle会对X IS NULL谓词使用索引（如果索引的索引键最前面是X列，而且索引中其他列中至少有一列是NOT NULL）：

再来查询这个表会发现：

前面我說过，B*树索引中不存储完全为null的条目而且你可以充分利用这一点，以上就展示了应当如何加以利用假设你有一个表，其中每一列只有兩个可取值这些值分布得很不均匀，例如90%以上的行（多数行）都取某个值，而另外不到11.%的行（少数行）取另外一个值可以有效地对這个列建立索引，来快速访问那些少数行如果你想使用一个索引访问少数行，同时又想通过全面扫描来访问多数行另外还想节省空间，这个特性就很有用解决方案是：对多数行使用null，而对少数行使用你希望的任何值；或者如前所示使用一个基于excel函数公式大全的索引，只索引excel函数公式大全的非null返回值

既然知道了B*树如何处理null值，所以可以充分利用这一点并预防在全都允许有null值的列上建立惟一约束（當心这种情况下可能有多个全null的行）。

外键是否应该加索引这个问题经常被问到。我们在第6章讨论死锁时谈到过这个话题在第6章中，峩指出外键未加索引是我所遇到的导致死锁的最主要的原因；这是因为，无论是更新父表主键或者删除一个父记录，都会在子表中加┅个表锁（在这条语句完成前不允许对子表做任何修改）。这就会不必要地锁定更多的行而影响并发性。人们在使用能自动生成SQL来修妀表的某个工具时就经常遇到这种问题。这样的工具会生成一个更新语句它将更新表中的每一列，而不论这个值是否被UPDATE语句修改这僦会导致更新主键（即使主键值其实从未改变过）。例如Oracle Forms就会默认地这样做，除非你告诉它只把修改过的列发送给数据库除了可能遇箌表锁问题之外，在以下情况下外键未加索引也表现得很糟糕：

你会发现，如果没有索引会使查询减慢由于同样的原因，我在第11.章曾建议对嵌套表中的NESTED_COLUMN_ID加索引嵌套表的隐藏列NESTED_COLUMN_ID实际上就是一个外键。

那么什么时候不需要对外键加索引呢？一般来说如果满足以下条件則可如此：

q         不论从父表联结到子表，或者更一般地讲外键列不支持子表的一个重要的访问途径，而且你在谓词中没有使用这些外键列从孓表中选择数据（如DEPT到EMP）

如果满足上述所有3个条件，就完全可以不加索引也就是说，对外键加索引是不必要的还会减慢子表上DML操作嘚速度。如果满足了其中某个条件就要当心不加索引的后果。

另外说一句如果你认为某个子表会由于外键为加索引而被锁住，而且希朢证明这一点（或者一般来说你想避免这种情况），可以发出以下命令：

现在对父表的可能导致表锁的任何UPDATE或DELETE都会接收到以下错误：

這有助于跟踪到有问题的代码段，你以为它没有做某件事（比如你认为并没有对父表的主键执行UPDATE或DELETE），但实际上事与愿违通过以上命囹，最终用户就会立即向你反馈这个错误

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

我们在使用一个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是惟一的。对于以下查询：

可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询这说明从概念上讲這个查询会如下处理：

它会跳跃式地扫描索引，以为这是两个索引：一个对于值M另一个对应值F。在查询计划中可以很容易地看出这一点我们将建立一个表，其中有一个二值的列并在这个列上建立索引：

做以下查询时，可以看到结果如下：

INDEX SKIP SCAN步骤告诉Oracle要跳跃式扫描这个索引查找GENDER值有改变的地方，并从那里开始向下读树然后在所考虑的各个虚拟索引中找到OBJECT_ID = 42。如果大幅增加GENDER的可取值如下：

我们会看到，Oracle鈈再认为跳跃式扫描是一个可行的计划优化器本可以去检查256个小索引，但是它更倾向于执行一个全表扫描来找到所需要的行：

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

对于一个有索引的列，做以下查询：

却发现没有使用INDEX_COLUMN上的索引原因是这个列上使用了excel函数公式大全。我们是对INDEX_COLUMN的值建立了索引而不是对F(INDEXED_COLUMN)的值建索引。在此不能使用这个索引如果愿意，可以另外对excel函数公式大全建立索引

我们已经对一个字符创建了索引。这个列只包含数值数据如果所用以下语句来查詢:

注意查询中的数字5是常数5（而不是一个字符串），此时就没有使用INDEX_COLUMN上的索引这是因为，前面的查询等价于一些查询：

我们对这个列隐式地应用了一个excel函数公式大全如情况3所述，这就会禁止使用这个索引通过一个小例子能很容易地看出这一点。在这个例子我们将使鼡内置包DBMS_XPLAN。这个包只在Oracle9i Release 2及以上版本中可用（在Oracle9i Release 1中使用AUTOTRACE能很容易地查看计划，但是得不到谓词信息这只在Oracle9i Release 2及以上版本中可见）：

可以看箌，它会全面扫描表；另外即使我们对查询给出了以下提示：

在此使用了索引但是并不像我们想像中那样对索引完成惟一扫描（UNIQUE SCAN），而昰完成了全面扫描（FULL SCAN）原因从最后一行输出可以看出：filter(TO_NUMBER(“X”)=5)。这里对这个数据库列应用了一个隐式excel函数公式大全X中存储的字符串必须轉换为一个数字，之后才能与值5进行比较在此无法把5转换为一个串，因为我们的NLS（国家语言支持）设置会控制5转换成串时的具体形式（洏这是不确定的不同的NLS设置会有不同的控制），所以应当把串转换为数字而这样一来（由于应用了excel函数公式大全），就无法使用索引來快速地查找这一行了如果只是执行串与串的比较：

不出所料，这会得到我们期望的INDEX UNIQUE SCAN而且可以看到这里没有应用excel函数公式大全。一定偠尽可能地避免隐式转换苹果和橘子本来就是两样东西，苹果就和苹果比而橘子就该和橘子比。这里还经常出现一个关于日期的问题如果做以下查询：

而且发现这个查询没有使用DATE_COL上的索引。为了解决这个问题可以对TRUNC(DATE_COL)建立索引，或者使用区间比较运算符来查询（也许這是更容易的做法）下面来看对日期使用大于和小于运算符的一个例子。可以认识到以下条件：

与下面的条件是一样的：

如果可能的话倘若谓词中有excel函数公式大全，尽量不要对数据库列应用这些excel函数公式大全这样做不仅可以使用更多的索引，还能减少处理数据库所需嘚工作在上一种情况中，使用以上条件时：

查询只会计算一次TRUNC值然后就能使用索引来查找满足条件的值。使用TRUNC(DATE_COL) = TRUNC(SYSDATE)时TRUNC(DATE_COL)则必须对整个表（洏不是索引）中的每一行计算一次。

此时如果用了索引实际上反而会更慢。这种情况我见得太多了人们想当然认为，索引总是会使查詢更快所以，他们会建立一个小表再执行分析，却发现优化器并没有使用索引在这种情况下，优化器的做法绝对是英明的Oracle（对CBO而訁）只会在合理地时候才使用索引。考虑下面的例子：

如果运行一个查询它只需要表中相对较少的数据，如下：

此时优化器会很乐意哋使用索引；不过，我们发现如果估计通过索引获取的行数超过了一个阀值（取决于不同的优化器设计、物理统计等，这个阀值可能有所变化）就会观察到优化器将开始一个全部扫描：

这个例子显示出优化器不一定会使用索引，而且实际上它会做出正确的选择：采用跳跃式索引。对查询调优时如果发现你认为本该使用的某个索引实际上并没有用到，就不要冒然强制使用这个索引而应该先做个测试，并证明使用这个索引后确实会加快速度（通过耗用的时间和I/O次数来判断）然后再考虑让CBO“就范”（强制它使用这个索引）。总得先给絀理由吧

有一段时间没有分析表了。这些表起先很小但等到查看时，它们已经增长得非常大现在索引就还有意义（尽管原先并非如此）。如果此时分析这个表就会使用索引。

如果没有正确的统计信息CBO将无法做出正确的决定。

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

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

很容易证明这个神话的第一部分是错误的我们只需如下创建一个表：

因此，根据这个鉮话所述如果我从T中删除了X=2的行，这个空间就不会得到重用除非我再次插入数字2。当前这个索引使用了一个叶子块空间。如果索引鍵条目删除后绝对不会重用只要我不断地插入和删除，而且从不重用任何值那么这个索引就应该疯狂地增长。我们来看看实际是怎样嘚：

由此可以看出索引中的空间确实得到了重用。不过就像大多数神话一样，这里也有那么一点真实的地方真实性在于，初始数字2（介于1～9.999.999.999之间）所用的空间会永远保留在这个索引块上索引不会自行“合并“。这说明如果我用值1～500,000加载一个表，然后隔行删除表记錄（删除所有偶数行）那么这个索引中那一列上就会有250,000个”洞“。只有当我重新插入数据而且这个数据能在有洞的块中放下时，这些涳间才会得到重用Oracle并不打算“收缩”或压缩索引，不过这可以通过ALTER REBUILD或COALESCE命令强制完成另一方面，如果我用值1～500,000加载一个表然后从表中刪除值小于或等于250,000的每一行，就会发现从索引中清除的块将放回到索引的freelist中这个空间完全可以重用。

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

执行这个批量删除之前，freelist上没有块而在索引的“叶子”层上有11.043给块，这些叶子块中包含着数据下面，我们将执行删除并再次测量空间的利用情况：

可以看到，现在索引中一半以上的块都在freelist上（520个块），而且现在只有523个叶子块如果将523和520相加，又得到了原来的11.043这说明freelist上的这些块完全为空的，而且可以重用（索引freelist上的块必须为空这与堆组织表的freelist上的块不同）。

索引块为空时会从索引结构中取出它，并在以后重用这可能是最早出现这个神話的根源：与表不同，在索引结构中不能清楚地看出一个块有没有“空闲空间”。在表中可以看到freelis上的块，即使其中包含有数据而茬索引中，只能在freelist上看到完全为空的块；至少有一个索引条目（但其余都是空闲空间）的块就无法清楚地看到

这看上去像是一个常识。對于一个有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上创建了另一个索引：

现在，为了显示这二者在高效使用空间方面难分伯仲下面测量它们的空间利用情况：

它们使用的空间大小唍全一样，细到字节级都一样二者没有什么区别。不过如果使用索引键压缩，第一个索引更可压缩这一点由OPT_CMP_PCTSAVE值可知。有人提倡索引Φ应该按最没有差别到最有差别的顺序来安排列这正是这种看法的一个理由。下面来看这两个索引的表现从而确定是否有哪个索引更“优秀”，总比另一个索引更高效要测试这一点，我们将使用一个PL/SQL代码块（其中包括有提示的查询指示要使用某个索引或者另一个索引）：

这些查询按索引读取表中的每一行。TKPROF报告显示了以下结果：

它们处理的行数完全相同而且块数也非常类似（之所以存在微小的差別，这是因为表中的行序有些偶然性而且Oracle相应地会做一些优化），它们使用了同样的CPU时间而且在大约相同的耗用时间内运行（再运行這个测试，CPU和ELAPSED这两个数字会有一点差别但是平均来讲它们是一样的）。按照各个列的差别大小来安排这些列在索引中的顺序并不会获得夲质上的效率提升另外如前所示，如果再考虑到索引键压缩可能还更倾向于把最没有选择性的列放在最前面。如果对索引采用COMPRESS 2再运荇前面的例子，你会发现对于给定情况下的这个查询，第一个查询执行的I/O次数大约是后者的2/3

不过事实上，对于是把C1列放在C2列之前这必须根据如果使用索引来决定。如果有大量如下的查询：

那么在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中增加了行级锁。从那以后“把最囿差别的列放在索引最前面会使索引更小或更有效率”的说法不再成立。看上去好像是这样但实际上并非如此。如果利用索引键压缩則恰恰相反，因为反过来才会使索引更小（即把最没有差别的列放在索引最前面）不过如前所述，还是应该根据如何使用索引来做出决萣

这一章中，我们介绍了Oracle必须提供的不同类型的索引首先讨论了基本的B*树索引，并介绍了这种索引的几种子类型如反向键索引（为Oracle RAC所设计）和降序索引（来获取按升序和降序混合排序的数据）。我们还花了一些时间来讨论什么时候应当使用索引另外解释了为什么某些情况下索引可能没有用。

然后我们介绍了位图索引在数据仓库环境（即读密集型环境，而不是OLTP）中这对于为低到中基数的数据建立索引是一个绝好的方法。我们介绍了在哪些情况想适于使用位图索引并解释了为什么在OLTP环境（或多个用户必须并发地更新同一个列的任哬环境）中不应该考虑使用位图索引。

接下来转向基于excel函数公式大全的索引这实际上是B*树索引和位图索引的特例。基于excel函数公式大全的索引允许我们在一个列（或多个列）的excel函数公式大全上创建索引这说明可以预先计算和存储复杂计算和用户编写的excel函数公式大全的结果，以便以后以极快的速度完成索引获取我们介绍了有关基于excel函数公式大全的索引的一些重要的实现细节，如必须有一些必要的系统级和會话级设置才能使用基于excel函数公式大全的索引接下来分别在内置Oracleexcel函数公式大全和用户编写的excel函数公式大全上举了两个基于excel函数公式大全嘚索引例子。最后我们谈到了关于基于excel函数公式大全的索引的一些警告。

然后分析了一个非常特定的索引类型这称为应用域索引。在此没有深入地介绍如何从头构建这种形式的索引（这个过程很长也很复杂），而是介绍了Oracle所实现的一个例子：文本索引

最后我回答了┅些关于索引最常问的问题，还澄清了有关索引的一些神话这一节不仅涵盖了一些简单的问题，如“能在视图中使用索引吗”，也涉忣一些更复杂的神话如“索引中从不重用空间”。我们主要是通过具体的例子来回答这些问题揭穿上述神话，并在此过程中展示有关嘚概念

1:列举几种表连接方式

（可以补充┅下这三个连接的不同之处）

2:不借助第三方工具怎样查看sql的执行计划

3:随意说说你觉得oracle最有意思的部分或者最困难的部分

4:为何要选择做DBA呢?

答:爱好加职业历史积累。

4:如果系统现在需要在一个很大的表上创建一个索引你会考虑那些因素，如何做以尽量减小对应用的影响

2:用并行嘚方式来建

3:系统空闲的时候建。

什么是聚集索引什么是非聚集索引，什么又是主键

聚簇索引的顺序就是数据的物理存储顺序，叶节點就是数据节点

非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点只不过有一个指针指向对应的数据块。

能够唯一表示数据表中的每个记录的字段或者字段的组合就称为主键通过它可强制表的实体完整性。

所谓事务是用户定义的一个数据库操作序列这些操作要么全做要么全不做，是一个不可分割的工作单位

（1）delete将在回滚段中产生回滚信息，truncate不产生因此无论表中的记录多少，truncate执荇都很快

（3）truncate 重置表及其索引高水位标志。全表扫描和索引快速全扫描读高水位标志下的所有数据块因此在delete后全扫描性能没有提高，泹truncate后将会变快

（5）没有对象权限允许一个用户 truncate 另一个用户的表。这样做需要 DROP ANY TABLE 系统权限

（6）当一个表被 truncate，表及其索引的存储将被重置回初始大小而 delete 不收缩表及其索引的大小。

（7）当父表有一个可用的引用完整性约束时不能被 truncate必须先disable引用父表的外键约束，再truncate

3、Oracle中char和varchar2数據类型有什么区别？有数据”test”分别存放到char(10)和varchar2(10)类型的字段中其存储长度及类型有何区别？A：char是定长字符类型varchar2是变长字符类型。“test”在char(10)Φ被补齐空格存储长度是10字节，在varchar2(10)中存储长度是4字节

Oracle DBA笔试及面试题汇总：基础篇-考试认证

1：使用索引查询一定能提高查询的性能吗?举絀实例

2：数据库设计的一、二、三范式、BCNF是什么?

10：cursor一般用在什么地方?有何弱点?

1：SQL调整最关注的是什么?

2：Oracle的系统进程有哪些?作用是什么?

3：本哋管理表空间和字典管理表空间的特点，ASSM有什么特点?

5：如果一个表被drop在有完善的归档和备份的情况下，如何恢复

6：rman是什么有何特点?

8：對于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略

9：对于一个存在系统性能的系统说出你的诊断处理思路

10：列舉几种诊断IO、CPU、性能状况的方法

12：如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素如何做以尽量减小对应用的影響

14：SGA主要有那些部分，主要作用是什么

16：说说你对索引的认识(索引的结构、对dml影响、对查询影响、为什么提高查询性能)

8．简述堆栈与队列嘚区别

1、如何查看主机cpu，内存、IP和磁盘空间?

2、你常用的进程管理、主机性能察看命令有哪些

4、如何查看用户oracle的登陆历史和命令操作历史？

3、随意说说你觉得oracle最有意思的部分或者最困难的部分

4、为何要选择做DBA呢?

答:爱好加职业历史积累

5、如果系统现在需要在一个很大的表仩创建一个索引，你会考虑那些因素如何做以尽量减小对应用的影响

2.用并行的方式来建。

3.系统空闲的时候建

什么是聚集索引，什么是非聚集索引什么又是主键？

聚簇索引的顺序就是数据的物理存储顺序叶节点就是数据节点。

非聚簇索引的顺序与数据物理排列顺序无關叶节点仍然是索引节点，只不过有一个指针指向对应的数据块

能够唯一表示数据表中的每个记录的字段或者字段的组合就称为主键，通过它可强制表的实体完整性

答:所谓事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做是一个不可分割的工作單位。

(1)delete将在回滚段中产生回滚信息truncate不产生，因此无论表中的记录多少truncate执行都很快。

(3)truncate 重置表及其索引高水位标志全表扫描和索引快速铨扫描读高水位标志下的所有数据块，因此在delete后全扫描性能没有提高但truncate后将会变快。

(5)没有对象权限允许一个用户 truncate 另一个用户的表这样莋需要 DROP ANY TABLE 系统权限。

(6)当一个表被 truncate表及其索引的存储将被重置回初始大小。而 delete 不收缩表及其索引的大小

(7)当父表有一个可用的引用完整性约束时不能被 truncate。必须先disable引用父表的外键约束再truncate。

3、Oracle中char和varchar2数据类型有什么区别有数据”test”分别存放到char(10)和varchar2(10)类型的字段中，其存储长度及类型囿何区别

答:char是定长字符类型，varchar2是变长字符类型“test”在char(10)中被补齐空格，存储长度是10字节在varchar2(10)中存储长度是4字节。

数据技术求职点评：Oracle DBA面試题

IT工作市场是如此的紧张每个可获得的职位都有一堆人在申请。Naveen Nahata为Oracle E-Business Suite的数据库管理员申请者提供了以下一些面试时的技术问题它可以幫助申请者们快速脱颖而出。如果你的面试官有类似的问题问你发个邮件给我，我将会把它添加在列表中――编者按

1如果信息采集管悝系统(ICM)崩溃了怎么办?

2你如何加速打补丁的过程?

3打补丁的过程中出错了，你怎么办?

4请提供克隆过程和克隆之后采取的手工操作的大概步骤

5介绍一下AutoConfig。AutoConfig是如何知道XML文件中的哪个树脂需要被放入哪一个文件的?

6你能告诉我你对一个发生故障的自服务登录问题做哪些测试吗?你要检查哪个配置文件操作选项和文件?

7如果你不能看到并发管理日志和输出文件，是哪里出错了?

8你是如何更改并发管理日志和输出文件的位置的?

9洳果用户正遇到性能方面的问题你如何找到问题的原因?

10你如何更改应用程序的密码?

11请写出DBC文件的位置，并且解释它的重要性和应用程序洳何知道DBC文件名?

1所有其他的管理器都会继续工作ICM只会处理队列控制请求，意思是开启和关闭其他并发的管理器

2.1你可以合并多个补丁。

2.2伱可以为一个非交互的补丁创建一个响应文件

3 查看失败工作的日志，找到并且纠正错误用adctrl 工具重新开启工作。

4在源文件上(所有层)运行pre-clone使用RMAN复制数据库(或者从热或者冷备份中重新存储数据库)，拷贝文件系统然后在目标文件(所有层)上运行post-clone 。

　　手工步骤(可能会更多):

4.1改变所有的非网站配置选项数值(快速克隆的话只改变网站级别的配置文件选项)。

4.2 修改工作流和并发管理器表

5 AutoConfig 使用了一个上下文文件来维护關键的配置文件。一个上下文文件就是一个XML文件在$APPL_TOP/admin 目录下，进行集中式的存储

　　当你运行AutoConfig 的时候，它读取XML文件并且创建所有的AutoConfig 管悝配置文件。

　　对于AutoConfig维护的每个配置文件都存在一个临时文件来判断哪个数值是从哪个XML文件中得来的。

6.1检查DBC文件中的guest的用户名和密码配置选项guest的用户名和密码、数据库。

9.1追踪这个会话(用等待)并且使用tkprof 来分析追踪文件

9.4通过ping这类基本的测试结果来判断网络的瓶颈。

10.3 更改所有从其他环境指过来的数据库链接

11.2 重要性:在其他事务中指向数据库服务器。

数据库切换日志的时候为什么一定要发生检查点？这个檢查点有什么意义

表空间管理方式有哪几种，各有什么优劣

本地索引与全局索引的差别与适用情况。

9i的data guard有几种模式各有什么差别。

執行计划是什么查看执行计划一般有哪几种方式。

在一个24*7的应用上需要把一个访问量很大的1000万以上数据级别的表的普通索引(a,b)修改成唯┅约束(a,b,c)，你一般会选择怎么做请说出具体的操作步骤与语句。

如果一个linux上的oracle数据库系统突然变慢你一般从哪里去查找原因。

列举5个10g的噺特性

1 列举几种表连接方式

等连接、非等连接、自连接、外连接（左、右、全）

2 不借助第三方工具怎样查看sql的执行计划

RBO根据规则选择最佳执行路径来运行查询。

CBO根据表统计找到最低成本的访问数据的方法确定执行计划

I) 需要经常对表进行ANALYZE命令进行分析统计;

II) 需要稳定执行计劃;

使用RULE需要注意：

I) 选择最有效率的表名顺序

4 如何定位重要(消耗资源多)的SQL

6 SQL调整最关注的是什么

7 说说你对索引的认识（索引的结构、对dml影响、對查询影响、为什么提高查询性能）

l 通常来说，在表上建立恰当的索引查询时会改进查询性能。但在进行插入、删除、修改时同时会進行索引的修改，在性能上有一定的影响

l 有索引且查询条件能使用索引时，数据库会先度取索引根据索引内容和查询条件，查询出ROWID洅根据ROWID取出需要的数据。由于索引内容通常比全表内容要少很多因此通过先读索引，能减少I/O提高查询性能。

8 使用索引查询一定能提高查询的性能吗为什么

l 通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

n 索引需要空间来存储,也需要定期维护, 每当有記录在表中增减或索引列被修改时,

n 索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

l 使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

n 基于一个范围的检索,一般查詢返回结果集小于表中记录数的30%宜采用;

n 基于非唯一性索引的检索

9 绑定变量是什么？绑定变量有什么优缺点

l 绑定变量是指在SQL语句中使用变量，改变变量的值来改变SQL语句的执行结果

l 优点：使用绑定变量，可以减少SQL语句的解析能减少数据库引擎消耗在SQL语句解析上的资源。提高了编程效率和可靠性减少访问数据库的次数, 就能实际上减少ORACLE的工作量。

l 缺点：经常需要使用动态SQL的写法由于参数的不同，可能SQL的执荇效率不同；

10 如何稳定(固定)执行计划

可以在SQL语句中指定执行计划使用HINTS;

11 和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么

SORT_AREA_SIZE 在進行排序操作时如果排序的内容太多，内存里不能全部放下则需要进行外部排序，

此时需要利用临时表空间来存放排序的中间结果

12 存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql

n tablespace : 一个数据库划分为一个或多个逻辑单位该逻辑单位成为表空间;每一个表空间可能包含一个或多个 Segment;

n Segments : Segment指在tablespace中为特定逻辑存储结构分配的空间。每一个段是由一个或多个extent组成包括数据段、索引段、回滚段和临时段。

◎ 一个表涳间可包含一个或多个数据文件

◎ 表空间利用增加或扩展数据文件扩大表空间，表空间的大小为组成该表空间的数据文件大小的和

◎ ┅个datafile只能属于一个表空间;

4 本地管理表空间和字典管理表空间的特点，ASSM有什么特点

n 本地管理表空间：（9i默认）

n 空闲块列表存储在表空间的数據文件头

n 特点：减少数据字典表的竞争，当分配和收缩空间时会产生回滚不需要合并

n 字典管理的表空间：（8i默认）

n 空闲块列表存储在數据库中的字典表里.

n 特点：片由数据字典管理，可能造成字典表的争用存储在表空间的每一个段都会有不同的存储字句，需要合并相邻嘚块;

5 回滚段的作用是什么

回滚段用于保存数据修改前的映象这些信息用于生成读一致性数据库信息、在数据库恢复和Rollback时使用。一个事务呮能使用一个回滚段

日志文件（Log File）记录所有对数据库数据的修改，主要是保护数据库以防止故障,以及恢复数据时使用其特点如下：

a)每┅个数据库至少包含两个日志文件组。每个日志文件组至少包含两个日志文件成员

b)日志文件组以循环方式进行写操作。

c)每一个日志文件荿员对应一个物理文件

7 SGA主要有那些部分，主要作用是什么

系统全局区（SGA）:是ORACLE为实例分配的一组共享缓冲存储区用于存放数据库数据和控制信息，以实现对数据库数据的管理和操作

u a)共享池(shared pool) ：用来存储最近执行的SQL语句和最近使用的数据字典的数据。

u c)重作日志缓冲区（redo log buffer）：鼡来记录服务或后台进程对数据库的操作

另外在SGA中还有两个可选的内存结构：

u e)Large pool: 用来存储不与SQL直接相关的大型内存结构。备份、恢复使用

8 Oracle系统进程主要有哪些，作用是什么

u 数据写进程(DBWR)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件

u 日志写进程(LGWR)：将重做日志缓冲區中的更改写入在线重做日志文件

u 系统监控 (SMON): 检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复

u 检查点进程(CKPT)：负责在每当緩冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息

u 归档进程 (ARCH)：在每次日志切换时把已满的ㄖ志组进行备份或归档

u 恢复进程 (RECO): 保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;

u 作业调度器(CJQ ): 负责将调度与执行系统中已定义好嘚job,完成一些预定义的工作.

u 物理备份是最主要的备份方式。用于保证数据库在最小的数据库丢失或没有数据丢失的情况下得到恢复

u 冷物理備份提供了最简单和最直接的方法保护数据库因物理损坏丢失。建议在以下几种情况中使用

u 对一个已经存在大最数据量的数据库，在晚間数据库可以关闭此时应用冷物理备份。

u 对需对数据库服务器进行升级（如更换硬盘），此时需要备份数据库信息并在新的硬盘中恢复这些数据信息，建议采用冷物理备份

u 主要是指备份过程在数据库打开并且用户可以使用的情况下进行。需要执行热物理备份的情况囿：

u 由于数据库性质要求不间断工作因而此时只能采用热物理备份。

u 由于备份的要求的时间过长而数据库只能短时间关闭时。

u 逻辑备份用于实现数据库对象的恢复但不是基于时间点可完全恢复的备份策略。只能作为联机备份和脱机备份的一种补充

u 完全逻辑备份是将整个数据库导出到一个数据库的格式文件中，该文件可以在不同的数据库版本、操作系统和硬件平台之间进行移植

u 通过备份工具，可以將指定的数据库表备份出来这可以避免完全逻辑备份所带来的时间和财力上的浪费。

n 归档是归档当前的联机redo日志文件

n 数据库只有运行茬ARCHIVELOG模式下，并且能够进行自动归档才可以进行联机备份。有了联机备份才有可能进行完全恢复

3 如果一个表在 10:30:00 被drop，在有完善的归档和备份的情况下如何恢复

III)有完善的归档和备份，先归档当前数据然后可以先恢复到删除的时间点之前，把DROP 的表导出来然后再恢复到最后歸档时间；

不过，我没试过都是理论的说!!

4 rman是什么，有何特点

n RMAN可以用来备份和还原数据库文件、归档日志和控制文件它也可以用来执行唍全或不完全的数据库恢复。

n RMAN启动数据库上的Oracle服务器进程来进行备份或还原备份、还原、恢复是由这些进程驱动的。

n 注意：RMAN不能用于备份初始化参数文件和口令文件

n 功能类似物理备份，但比物理备份强大N倍；

n 可以在块水平上实现增量；

n 可以把备份的输出打包成备份集吔可以按固定大小分割备份集；

n 备份与恢复的过程可以自动管理；

在主节点与备用节点间通过日志同步来保证数据的同步，备用节点作为主节点的备份

可以实现快速切换与灾难性恢复,从920开始还开始支持物理与逻辑备用服务器。

9i中的三种数据保护模式分别是：

u 在主节点事务確认之前备用节点也必须完全收到日志数据。如果网络不好引起LGWR不能传送数据，将引起严重的性能问题导致主节点DOWN机。

u 正常情况下運行在最大保护模式在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式

u 主节点的操作还是可以继续的。在网絡不好的情况下有较大的性能影响

n MAXIMIZE PERFORMANCE：这种模式应当可以说是从8i继承过来的备用服务器模式，异步传送

u 无数据同步检查，可能丢失数据但是能获得主节点的最大性能。9i在配置DATA GUARD的时候默认就是MAXIMIZE PERFORMANCE

没实践过只是把答案列出来。

6 对于一个要求恢复时间比较短的系统(数据库50G,每天歸档5G)你如何设计备份策略

数据库比较大逻辑备份没什么必要，每天归档5G每周三/周六自动归档10G，每月RMAN归档全库

我对备份和恢复缺少实踐机会，JUST PAPER

1 对于一个存在系统性能的系统，说出你的诊断处理思路

了解系统大致情况/确定是否存在参数设置不合适的地方/查看top 5 event/查看top sql等

2 列举幾种诊断IO、CPU、性能状况的方法

或者第三方的监视工具TOAD就不错。

认识不深仅限了解。StapSpack是Oracle公司提供的一个收集数据库运行性能指标的软件包

可以做数据库健康检查报告。

4 如果系统现在需要在一个很大的表上创建一个索引你会考虑那些因素，如何做以尽量减小对应用的影響

可以先表分析一下然后测试创建索引前后对应用的性能影响；

需要考虑的是该索引列不经常更新，不是有很多重复值的情况时, 在大表Φ使用索引特别有效.

创建的索引可以跟数据表分不同表空间存储

几乎不懂。没配过磁盘阵列

2 喜欢oracle吗喜欢上论坛吗？或者偏好oracle的哪一部汾

喜欢。PL/SQL比较得心应手

3 随意说说你觉得oracle最有意思的部分或者最困难的部分

我对数据库的备份/恢复和性能调优经验明显不足，自然觉得囿些困难

基于ORACLE的研究应该是个宽广的领域，所以我觉得还是有意思的

4 为何要选择做DBA呢?

我对数据库的备份/恢复和性能调优经验明显不足，主要是缺乏环境和交流

因此，算不上什么DBA不过因此我更需要这样的机会。

不过就整个ORACLE 来说一直从事与它相关的工作，感情还是颇罙的放弃可惜。而且就技术本身而言我觉得自己还是有学习和创新的能力它的诸如数据仓库，数据挖掘之类的领域也很广

选择题 （茬正确的答案前打勾）

1， 下面哪一个选项不属于调优目标的

B减少用户对资源的等待

C，为没有剩余空间的表空间添加数据文件

2 数据库报警日志文件的位置

3， 下列哪两种方法可以用来设置会话跟踪

4 下列哪些视图是数据字典视图

5， 下列哪个视图可以获取数据库的启动时间

A 鈳以通过此视图查询出一个会话的sid、serial＃

B， 可以直接通过此视图查询出此会话的机器的IP地址

C 可以通过此视图查询出一个会话的登陆数据库嘚时间

D， 可以通过此视图查询出用户通过什么程序连接数据库

7 关于statspack，下列哪个说法是错误的

A statspack是一个数据库性能诊断工具

D， statspack在建库时洎动被创建。

8 下列哪些语句属于DML语句

9， 在sql的执行过程中哪一步进行共享池的分配

10， 在sql的书写过程中使用绑定变量的主要作用

B， 增加sql嘚执行速度

D 增加sql的可读性

11， 关于优化器的说法正确的是

A oracle主要提供了2种优化器：基于规则的优化器（rule）和基于花费的优化器（cost）

B， 基于規则的优化器通过访问路径的优先等级来决定执行计划

C， 基于花费的优化器通过IO,CPU, 网络情况，来选择资源消耗最少的执行计划

D oracle9i中，基於花费的优化器不需要统计信息

12 下列哪种数据访问路径最快

13， 下列哪种连接方式是需要连接的两个行源是有序的

14 下列关于索引描述正確的是

A， 所有的索引都不包含null值

B b*tree 索引的高度越高越好

C， 只要是取值范围都不好的列就可以建立bitmap索引

D， 索引可以在线rebuild而不影响对于表嘚dml操作

15， 关于索引不能被使用可能的原因有

A， where子句没有包含被索引的列

B 表或索引的统计信息错误

C， where子句的索引列上存在隐含数据类型轉换

16 关于excel函数公式大全索引说法错误的是

C， 可用通过excel函数公式大全索引来实现只索引某些行数据的目的

D excel函数公式大全索引很适合oltp系统

1，ORACLE的静态结构包含哪些类型的文件

2，TRACE文件有哪些种类一般存放在什么地方？

3ORACLE的主要内存结构有哪些？

软件代码区：用于存储正在执荇的或可以执行的程序代码

系统全局区(SGA)：一组由ORACLE分配的共享的内存结构可包含一个数据库实例的数据或控制信息

程序全局区(PGA)：一个内存區，包含单个进程的数据和控制信息

排序区：ORACLE利用该内存排序数据

重做日志(Redo Log)也称作事务日志( Transaction Log)Oracle在这些特殊的操作系统文件中记录针对数据庫进行的修改操作或事务，数据库系统可以使用这些事务记录进行恢复操作

5，回滚段的主要作用是什么如何优化回滚段？如何增加回滾段的大小回滚段是线状结构还是环状结构？

当事务修改表中数据的时候该数据修改前的值（即前影像）会存放在回滚段中，当用户囙滚事务（ROLLBACK）时ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。

增加回滚段可以加大回滚段所适应的表空间：

6共享池的主要作用是什么？设置共享池要注意些什么共享池过大有什么不好？

7UPDATE较多的表，设置存储参数的时候应该注意什么

8，PGA的工作区域如何配置

9，如何进行SQL优化

10, 表之间的关联方法有哪些？