【oracle调优】表扫描还是索引扫描

    在数据库优化的时候，最常用的手段就是建索引了。但实际情况是建了索引，数据库也不一定会走索引，我们首先来看看，优化器根据什么决定走索引还是走全表。

1、查询出来的记录数

    优化器显示的返回的记录数为优化器估计的数值，并不一定是实际查出来的记录数。在对列有直方图的情况下，这个预估值就会更接近真实值。在没有直方图的情况下，这个值有时候会跟实际值差很多，以致可能直接影响对索引的选择。

    比如公司员工100人，年龄为30-50，年龄唯一值有20个，但这个字段值分布很不均，其中实际35岁的就有50人，比如我要查询年龄为35岁的人，若没有直方图，则优化器预估的返回记录数应该为100/20=5个，而实际要返回50个。这就很有可能造成优化器倾向选择性能可能更差的索引。

    所以，对于数据分布很不均的列，要建立直方图。

    建立直方图的参数是统计分析中的

Method_opt  这个是确定在哪些列上建立柱状图的选项。（只列出了for all

columns相关的）

For all columns size repeat.只在原本有柱状图的列上进行柱状图分析重建。

For all columns size skewonly.分析所有列数据的分布情况，根据此情况决定在哪些列上建立柱状图。比较耗费系统资源。

For all columns size auto。根据列数据分布情况和负载情况决定如何建立柱状图。

 

2、记录的长度

    也就是每行的平均长度。记录的长度越长，计算出的块读取数越多，也就越倾向于走索引。

           可以从dba_tab_statistics视图中的AVG_ROW_LEN查看。

    该值也是在统计分析后才能得到最准确的值。

3、数据分布

    也就是聚簇因子的值。

    当表数据大致是按照索引的顺序存储的时候，则通过索引找到rowid之后，则可以通过读取更少的数据块就能查询出结果集。这种情况下，数据库走索引的花费也将趋于更少。

    可以从dba_ind_statistics视图中的CLUSTERING_FACTOR查看该值。

    该值最优为等于表的blocks数，最差等于表的row数。

    若该值的正确值仍对查询造成了不理想的效果，也可通过dbms_stats.set_index_stats函数进行修改。

 

4、优化器目标

           优化器目标参数OPTIMIZER_MODE默认是ALL_ROWS，其他可设的值还有FIRST_ROWS、FIRST_ROWS_n(n为1、10、100或1000)。

           全表扫描可以在目标为ALL_ROWS的时候获得更快的响应时间，若你更倾向于更快得到前部分数据，更倾向于让你的查询走索引，则设置为FIRST_ROWS_N可能更好。

5、两个关于索引的参数

         Optimizer_index_caching

         该参数默认为0，取值范围为0-100，优化器根据该值计算预估用于嵌套循环联结的索引块有多少比例已经存在于数据缓冲区。该值设置的大些，更有利于优化器选择索引，选择嵌套循环方式的联结。

         Optimizer_index_cost_adj

         该值默认是100，取值范围为0-10000，优化器根据该值计算索引扫描和全表扫描的花费，即默认索引扫描和全表扫描的花费是一样的。

         通过给该值一个更小的值，可以让优化器更倾向于走索引。

         原本db_file_multiblock_read_count的值也会影响优化器计算全表扫描的花费，但10gR2之后，该值已经可以自调节，不在需要手动调整了。

 

6、HINT

         当然，也可以通过oracle提供的方法显式的决定执行计划，存储提纲、固话基线以及我们要说的这个hint提示。

         提示全表扫描

         /*+FULL(表名)*/

         提示走索引

         /*+INDEX(表名  索引名)*/

 

7、表扫描还是索引扫描

           说了以上那么多，只是说明了优化器是根据什么决定表扫描还是索引扫描的，但是优化器有时候会根据错误的信息（比如过时的统计信息、不合适的参数设置）得到可能并不合适的执行计划。这是就需要我们去思考究竟是表扫描还是索引扫描这个问题了。

           最直接适用的一点就是目标数据的占比。

           当然这个占比无法给出一个确切的比例，主要因为row的长度不一定，所以，基本上。要得到的数据占整个表的大部分，则全表扫描更合适，然只是整个表的很小一部分，则索引扫描通常更好。