求助，分区表之上建立视图，查询时不走索引的问题

Question

描述：
相关表t8_psn_customer、t8_customer均建立了38个分区，分区均通过db_id字段区分。
相关视图v8_customer建立在t8_psn_customer、t8_customer之上问题：
当在视图上执行语句
select * from v8_customer where db_id='518' and cust_no='1000310404500015816'会出现全表扫描t8_psn_customer的情况，查询速度很慢，但指定其他db_id不会出现。
去掉db_id参数后select * from v8_customer where cust_no='1000310404500015816'时，则均会通过索引查询，速度很快，而该记录确实存在于518分区。之前分区一直正常，当有大量数据进入某一个或几个分区后会出现这种问题。以前出现过很多次，解决办法是将数据导入一个新的分区表，然后把之前的表干掉，表名再改回来。但增量数据经常有，总这么处理不是办法。想探究一下原因和改进方法，请各位协助，谢谢！补充描述：
在具体表上执行相关查询则均走索引，且速度很快。
select * from t8_customer where db_id='518' and cust_no='1000310404500015816'主键及索引建立情况
alter table T8_CUSTOMER  add constraint PK_T8_CUSTOMER1 primary key (DB_ID, CUST_ID)
create index T8IDX_DB_ID_CUST_NO on T8_CUSTOMER (DB_ID, CUST_NO);alter table T8_PSN_CUSTOMER  add constraint PK_T8_PSN_CUSTOMER primary key (DB_ID, CUST_ID)
create index T8IDX_NAME_ID_TYPE_ID_CARD on T8_PSN_CUSTOMER (NAME, ID_TYPE, ID_CARD);视图定义如下：
create or replace view v8_customer as
select c.db_id, pc.cust_id,c.cust_no,pc.name,pc.gender,pc.birth_date,pc.id_type,pc.id_card,pc.occ_code
from t8_customer c,t8_psn_customer pc
where c.db_id=pc.db_id  and c.cust_id=pc.cust_id

Answer 1

-- 这几天碰到了类似的问题，解决方案：分区表和数据、索引都已经弄好的基础上，对2个分获表收集统计信息，参考下面语句：
exec dbms_stats.gather_table_stats(ownname => 'owner_name',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);
-- 相应地改成你的SCHEMA_NAME和TABLE_NAME完成后再看看视图的性能，如果还不行把执行计划填上来。

Answer 2

楼主把表statistics分析一下看看..analyze table tablename compute statistics for all indexes;
analyze table tablename delete statistics SQL语句的执行计划走不走索引除了与Statistic有关系，还有pfile的一个参数有关：optimizer_index_cost_adj. 该参数影响优化器选择索引还是全表扫描的倾向,建议将其设为40. 
Oracle 10g statistic数据统计,Oracle会根据这些统计信息来决定是走RBO(Rule-BasedOptimization)，还是走CBO(Cost-BasedOptimization)，会去选择哪种执行计划更划算,影响是否走相关的索引等.如果是CBO的话，它依靠准确的（或者说比较准确的）统计信息来产生优化的执行路径,如果没有做过统计，CBO也就没有做cost评估的依据，所以虽然是CBO，但是实际上还是用RBO了，而且如果不常做统计的话，由于CBO是以统计为依据的，所以这时CBO的依据信息有问题，CBO也会不准。 所以 DBA 需要确保定期收集统计信息，创建另一个执行核对清单。举个例子：
一次测试，一个sql语句执行要20分钟，有时候还出不了结果，发现查看执行计划，发现居然走了全表扫描（表中大约300w条记录），为啥不用索引呢，查看索引状态，一切正常。分析了相关的表，然后重新执行3分钟搞定！世事无绝对，analyze表会增加CBO执行的性能？不一定的。
我就碰到一个语句分析后要执行30多分钟，删除分析后，只要30秒。
很多情况下不一定的，最好是自己从执行计划判断。analyze table tablename compute statistics for all indexes;
analyze table tablename delete statistics 顺便补充一点，表只有分析了之后，num_rows才会有值。
select * from user_all_tables a where a.num_rows <10;analyze table tablename compute statistics;SELECT 'ANALYZE  TABLE  ' || TABLE_NAME || '  COMPUTE  STATISTICS;' 
  FROM (SELECT DISTINCT TABLE_NAME FROM ALL_COL_COMMENTS); SQL语句的执行计划走不走索引除了与Statistic有关系，还有pfile的一个参数有关：optimizer_index_cost_adj. 该参数影响优化器选择索引还是全表扫描的倾向,建议将其设为40. Oracle 10g Statistic数据统计
http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx

Answer 3

db_id='518' 这个518分区数据量太大了。当加上条件的时候是否走了全表扫描？或者518分区的索引已经失效了，重建下索引，看效果如何？

Answer 4

感谢mantisXF，昨天下班之前跑了你的那条语句。
早上看结果，一个表3.7G跑了2个小时；令一个表6.2G跑了3个多小时。目前SQL运行全部正常，能够走索引，速度很快。看来就是在有大增量数据之后，需要重做统计信息的原因。

Answer 5

之前也做过了analyze，但是没起作用。用了mantisXF的dbms_stats.gather_table_stats就可以了，不知有何区别？
回头研究一下您说的pfile参数，今后或许会对我们的优化有帮助，谢谢！

Answer 6

还有一个比较奇怪的现象想请教大家，还是针对描述的这个问题：
select * from v8_customer where db_id='518' and cust_no='1000310404500015816'
db_id是varchar2类型，在没有做统计信息之前，如果把518的引号去掉，即db_id=518就可以走索引了。但是这么写明显是不对的，这是为什么呢？

Answer 7

1. analyze table tablename compute statistics
2. dbms_stats.gather_table_stats-- 方式1只会对各分区进行统计信息收集，方式2适合收集分区大表及各分区信息，而且可以生成关于分区表的柱状图信息: all_histograms，这样便于ORACLE对列进行COST准确分析
-- 可以试试方式1跑完后ALL_TABLES中关于分区表的信息比如NUM_ROWS等字段是没有值的。
-- 关于db_id=518去引号走索引的问题：ORACLE 9i主要是根据COST来决定执行最佳计划，可以查看一下db_id='518'这种方式如果选择走索引的执行计划会比不走索引的COST会高一些。http://blog.csdn.net/mantisXF/archive/2009/11/11/4799793.aspx