关于索引的迷惑！

当检索记录超过全部记录20%，oracle就不会使用索引了。

个人认为只有三列都在where子句中才会使用索引。

听各位这么一说，偶更糊涂了。
--------------------------------
drabit(square) 和ern(毕业生) 观点1.用户建立索引后，oracle优化器会自动选择使用索引还是全表检索（我的理解是oracle优化器知道用哪个快，自动选择）；观点2.对于多列索引，如果条件里只有第一个列，也是会用的。但是如果只有第二个或第三个，是不会的。
--------------------------------################################
welyngj(平平淡淡) 的观点和我看到的trace是一致的，没有使用索引。
################################********************************
qiyousyc(沈阳棋友) 说:当检索记录超过全部记录20%，oracle就不会使用索引了。意思应该和ern(毕业生)的说法差不多，也就是oracle优化器会知道用哪个快而自动选择。
********************************
综合上面的，我现在得出来的结论是：建立索引后，不论你是用索引列检索还是用非索引列检索，也不论你用了索引列中的几列，oracle优化器都会知道该查询是用索引快还是全表检索快而自动选择。我们要做的只是建立索引而已，而不用考虑sql文的写法，不知道我有没有想错，还希望高手指点...

刚才看oracle关于index的文档，看到这么一段:
Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important. Generally, the most commonly accessed or most selective columns go first.
好像阐述的就是ern(毕业生)的第二个观点:对于多列索引，如果条件里只有第一个列，也是会用的。但是如果只有第二个或第三个，是不会的。那现在oracle优化器会知道该查询是用索引快还是全表检索快而自动选择这个观点不知道对不对，呵呵，再去看看。

sorry !经过我亲自测试发现ern(毕业生) 的说法是正确的：如果条件里包含有第一列是会使用索引的，没有第一列是不会使用索引的。
不信请楼主自己测试。

有一句话看不懂，请教一下：A key value cannot exceed roughly one-half (minus some overhead) the available data space in a data block.

to welyngj(平平淡淡) ：
我也试了，但是还是告诉我TABLE ACCESS (FULL) OF 'T1'，总纪录3464，检出34条。纳闷阿....

analyze table t1 compute statistics ;

to welyngj(平平淡淡) ：
不好意思，请问analyze完了应该怎么看结果啊，呵呵！

：A key value cannot exceed roughly one-half (minus some overhead) the available data space in a data block.我的翻译（可能很不准确）：
在一个数据块中关键值大致不能超过可用的空间一半（减掉一些负荷）
我自己都看不懂是什么意思。

to welyngj(平平淡淡) ：
1.   analyze table t1 compute statistics；以后再select * from T1 where d='1'就 INDEX (RANGE SCAN) OF 'TCLAIMCTRL_IDX1' (NON-UNIQUE)是使用索引了，这是为什么？
2.   在"A key value cannot exceed roughly one-half (minus some overhead) the available data space in a data block."这句话的前面有句话说索引不能超过32列，然后就是这句话了，再结合你的翻译，应该是“索引不能超过32列，每列的值大致不能超过一个数据块中的可用空间的一半”，不过还是不懂，呵呵！！to 大伙儿：
   请问oracle优化器是否会知道某个查询是用索引快还是全表检索快而自动选择么？我们要做的只是建立索引，而不用考虑sql文的写法呢？？

1。Use the ANALYZE statement to: Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute. 2。我也说不清。

to welyngj(平平淡淡) ：
1. 为什么用了analyze table t1 compute statistics以后就使用索引，我刚才查到了。因为我的优化器模式是默认的choose，而它有这个特性：If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
所以是analyze table t1 compute statistics这句话把table t1的statistics加入了data dictionary ，然后choose模式就会使用cost-based optimizes（CBO）这种注重响应时间的模式，所以就使用了索引。
2. 不管了，没什么意义！to 大伙儿：
索引这个东西，从学校学的时候就说用好可以提高查询速度，但是到底应该怎么用，偶始终是没搞明白，希望对索引有一定理解的人指点一下啊！！

大家讨论得还是很激烈的嘛，我刚才也在琢磨一个索引的问题，才来，呵呵
刚才查资料，看到这么一句话
Index keys that have high selectivity. The selectivity of an index is the
percentage of rows in a table having the same value for the indexed key. An
index’s selectivity is optimal if few rows have the same value.
我想这也是一条重要原则吧。其他原则就比较简单了，比如用函数的时候不会用到缩影啊，什么的。这段话出自9i的官方文档中的performance tunning guide and reference 185页

现在首先我想搞明白一个问题：Optimizer会不会自动选择使用全表查询和还是索引查询？
其次我现在对于索引的理解是：
如果没有建立索引，除了唯一检索（主键检索），统统全表检索，这样对于数据量比较小的检索就不合适了；所以建立索引是为了那些小数据量的检索而用。举个例子，假设有100个苹果，我现在要往篮子里放99个，那我肯定是先把100个苹果全倒进去，然后再拿出一个来（全表检索）；但是如果我要往里放5个，那我一定是一个一个往篮子里放（索引检索）。但是如果没有建立索引，那我就只能还是先把100个苹果全倒进篮子，再往外拿95个。不知道这样理解对不对？

To ern(毕业生) :
我说的和你是一个道理拉，大海捞针，不就是小数据量检索？捞海水不就是大数据量检索？然后按照你说的，Optimizer会根据你是要捞针还是要捞海水（数据量的大小）而自动选择查询方式。不知道这个数据量的大小比例Optimizer是怎么计算的，有没有资料里提过阿？

To welyngj(平平淡淡) ：
首先对你的关注和帮助表示感谢。能不能具体说一下什么书在哪一页？关于oracle优化器，我又另开了一个帖子请教大家，希望大家帮我讨论一下。谢谢！！
http://expert.csdn.net/Expert/topic/2853/2853877.xml?temp=.5119593

我个人再总结一下这个帖子：
Ｑ：多列索引时，使用多列索引的第一列作为查询条件，会不会使用索引？
Ａ：如果oracle优化器是choose模式，且数据量不超过一定比例（这个比例我现在还不知道多少），oracle优化器会选择使用索引。但是如果用多列索引的非第一列作为查询条件，肯定不会使用索引。
在这里，再次感谢参与答贴者，他们是：welyngj(平平淡淡)、ern(毕业生)、qiyousyc(沈阳棋友) 、ouyangyamin() 。

To welyngj(平平淡淡) ：
在学校有这么深的功力，佩服佩服！！

索引的三個問題
　　索引( Index )是常见的数Database 的性能。虽然有许多，还是有不少的人对它存在误解Oracle 8.1.7 OPS on HP N se使用不同的方法后，数据的比较明白事情的关键。据库对象，它的设置好坏、使用是否资料讲索引的用法， DBA 和 Develo，因此针对使用中的常见问题，讲三ries ,示例全部是真实数据，读者不。本文所讲基本都是陈词滥调，但是得当，极大地影响数据库应用程序和per 们也经常与它打交道，但笔者发现个问题。此文所有示例所用的数据库是需要注意具体的数据大小，而应注意在笔者试图通过实际的例子，来真正让您
　　第一讲、索引并非总是最佳选择
　　如果发现Oracle 在有索引，Oracle 确实会选择全表扫描的情况下，没有使用索引，这并不是（Full Table Scan）,而非索引扫描 Oracle 的优化器出错。在有些情况下（Index Scan）。这些情况通常有：
　　1. 表未做statistics, 或者 statistics 陈旧，导致 Oracle 判断失误。
　　2. 根据该表拥有的记录数和数据块数，实际上全表扫描要比索引扫描更快。
　　对第1种情况，最常见的例子，是以下这句sql 语句：
　　select count(*) from mytable;
　　在未作statistics 之前，它使用全表扫描，statistics 之后，使用的是 INDEX (FAST FULL S得不好，也会导致Oracle 不使用索引。需要读取6000多个数据块（一个数据块是8k）, 做了CAN) ，只需要读取450个数据块。但是，statistics 做
　　第2种情况就要复杂得多。一般概念上都认为扫描快。为了讲清楚这个问题，这里先介绍一下Or：CF(Clustering factor) 和 FF(Filtering fact 索引比表快，比较难以理解什么情况下全表扫描要比索引acle 在评估使用索引的代价（cost）时两个重要的数据or).
　　CF: 所谓 CF, 通俗地讲，就是每读入一个索引块，要对应读入多少个数据块。
　　FF: 所谓 FF, 就是该sql 语句所选择的结果集，占总的数据量的百分比。
　　大约的计算公式是：FF * (要读入的数据块块数。需要读入全表扫描需要读入的数据块数等 CF + 索引块个数) ，由此估计出，的数据块越多，则 cost 越大，Orac于该表的实际数据块数）一个查询，如果使用某个索引，会需le 也就越可能不选择使用 index. （
　　其核心就是， CF 可能会比实际的数据块数量建立时，索引中的记录与表中的记录有良好的对应对应关系越来越乱，CF 也越来越大。此时需要 DB 大。CF 受到索引中数据的排列方式影响，通常在索引刚关系，CF 都很小；在表经过大量的插入、修改后，这种A 重新建立或者组织该索引。
　　如果某个sql 语句以前一直重新整理该索引了。使用某索引，较长时间后不再使用，一种可能就是 CF 已经变得太大，需要
　　FF 则是Oracle 根据 stati，最大值是409654，考虑以下sq stics 所做的估计。比如, mytablesl 语句：表有32万行，其主键myid的最小值是1
　　Select * from mytables where myid>=1; 和
　　Select * from mytables where myid>=400000
　　这两句看似差不多的 sql 者的 FF 可能只有 1%。如果它实际上，在我们的数据库上的测语句，对Oracle 而言，却有巨大的的CF 大于实际的数据块数，则Oracl试验证了我们的预测. 以下是在HP 差别。因为前者的 FF 是100%，而后e 可能会选择完全不同的优化方式。而第二讲、索引也有好坏                                                                      　　索引有 B tree 索引， Bit全称是Balanced , 其意义是，有一个字段（Single column）,Function-based index. 许多de map 索引， Reverse b tree 索引，从 tree 的 root 到任何一个leaf 也可以有多个字段（Composite）,veloper 都倾向于使用单列B 树索引等。最常用的是 B tree 索引。 B 的，要经过同样多的 level. 索引可以只最多32个字段，8I 还支持。　　所谓索引的好坏是指：                                                                      　　1，索引不是越多越好。特别是大量从来或者个索引即会降低性能，而且在一个sql 中， Oracl 几乎不用的索引，对系统只有损害。OLTP系统每表超过5e 从不能使用超过 5个索引。　　2，很多时候，单列索引不如复合索引有效率。                                                　　3，用于多表连结的字段，加上索引会很有作用。                                              　　那么，在什么情况下单列索所查询的列，全部都出现在复合使用多个单列索引要快得多。（引不如复合索引有效率呢？有一种情索引中时，此时由于 Oracle 只需要此时，这种优化方式被称为 Index o 况是显而易见的，那就是，当sql 语句查询索引块即可获得所有数据，当然比nly access path）
第三讲、索引再好，不用也是白搭
　　抛开前面所说的，假设你设不用，那么，需要做的第一件事置了一个非常好的索引，任何傻瓜都情，是审视你的 sql 语句。知道应该使用它，但是Oracle 却偏偏
　　Oracle 要使用一个索引，有一些最基本的条件：
　　1， where 子句中的这个字段，必须是复合索引的第一个字段；
　　2， where 子句中的这个字段，不应该参与任何形式的计算
　　具体来讲，假设一个索引是按 f1, f2, f3的= : var2, 则因为 f2 不是索引的第1个字段，无次序建立的，现在有一个 sql 语句, where 子句是 f2 法使用该索引。
　　第2个问题，则在我们之中非常严重。以下是从实际系统上面抓到的几个例子：
　　Select jobid from mytabs where isReq='0' and to_date (updatedate) >= to_Date ( '2001-7-18', 'YYYY-MM-DD')；
　　以上的例子能很容易地进行和内存资源。改进。请注意这样的语句每天都在我们的系统中运行，消耗我们有限的cpu
　　除了1，2这两个我们必须牢记于心的原则外，。这里我只讲哪些操作或者操作符会显式（explic 还应尽量熟悉各种操作符对 Oracle 是否使用索引的影响itly）地阻止 Oracle 使用索引。以下是一些基本规则：
　　1，如果 f1 和 f2 是同一个表的两个字段，则 f1>f2, f1>=f2, f1
　　2， f1 is null, f1 is no t null, f1 not in, f1 !=, f1 lik e ‘%pattern%’;
　　3， Not exist
　　4，某些情况下，f1 in 也会不用索引；
　　对于这些操作，别无办法，许可以将 in 操作改成比较操只有尽量避免。比如，如果发现你的作 + union all。笔者在实践中发现 sql 中的 in 操作没有使用索引，也很多时候这很有效。
　　但是，Oracle 是否真正使用索引，使用索引，对所写的复杂的 sql, 在将它写入应用程序之前Oracle 对该 sql 的解析（plan）,可以明确地看是否真正有效，还是必须进行实地的测验。合理的做法是，先在产品数据库上做一次explain . explain 会获得到 Oracle 是如何优化该 sql 的。
　　如果经常做 explain, 就会划往往不尽如人意。事实上，将然这已经是题外话了。发现，喜爱写复杂的 sql 并不是个复杂的 sql 拆开，有时候会极大地好习惯，因为过分复杂的sql 其解析计提高效率，因为能获得很好的优化。当

《数据库系统概念》杨冬青等译机械工业出版社
英文名：Database system concept 第14章查询优化

个人实践：
书本上的东西并不全是对的，我就碰到一个，写了一个视图，稍微复杂了一些，大概嵌套了4、5层，感觉oracle解析器失效了，没有按照预期的那样引用索引，结果，用pl/sql重新写了一边，为了引用索引列甚至用到了游标，给人的感觉好像是效率特别低下，结果，正好相反，效率提高了将近30倍

调试易

关于索引的迷惑！

解决方案 »