关于索引问题

Question

版本：Oracle 10g
工具：pl/sql
索引语句：create index IDX_USER_SAS on t_user_statistics(time,cityid,mnc,platid);
SQL语句：
(1) 走全表扫描
select to_char(time,'dd') day, count(distinct imei) cnt1,sum(isNew) cnt2,count(distinct imei)-sum(isNew) cnt3 from(
   select * from t_user_statistics t,T_STATIC_MOBILE_OPERATOR t2 
     where t.time>= to_date('2009-09-01','yyyy-MM-dd') and t.time <to_date('2009-09-30','yyyy-MM-dd')
     and t.mnc=t2.mnc and t2.name='中国联通'  and t.platid='1000' ) temp  
group by time 
order by time
(2) 走索引
select to_char(time,'dd') day, count(distinct imei) cnt1,sum(isNew) cnt2,count(distinct imei)-sum(isNew) cnt3 from(
   select * from t_user_statistics t,T_STATIC_MOBILE_OPERATOR t2 
     where t.time>= to_date('2009-09-01','yyyy-MM-dd') and t.time <to_date('2009-09-20','yyyy-MM-dd')
     and t.mnc=t2.mnc and t2.name='中国联通'  and t.platid='1000' ) temp  
group by time 
order by time2句SQL 就改变了一下时间
此时time为2009-09-01 ~2009-09-21 时间范围内 走索引，在2009-09-01~2009-09-30就走全表扫描？ 为什么
time 为2009-09-01 ~2009-10-10范围内 走全表扫描 ，为什么
还有 如果我输入错误的时间 比如 2009-000-20 走filter 然后都是索引 为什么？在线等

Answer 1

cbo判断全表扫描速度更快，所以不走索引
返回数据量小的话，用索引比较快
你看看2009-09-01 ~2009-09-21 和2009-09-01~2009-09-30  2009-09-01 ~2009-10-10
数据量有什么不同

Answer 2

不会啊，2009-09-01~2009-09-21走是的NESTED JOIN 执行过程如下：
SELECT STATEMENT, GOAL = ALL_ROWS 252 13913 248 1182605
 SORT GROUP BY 252 13913 248 1182605
  TABLE ACCESS BY INDEX ROWID DB_AGENT T_USER_STATISTICS 247 13913 245 876519
   NESTED LOOPS 250 13913 248 1182605
    TABLE ACCESS FULL DB_AGENT T_STATIC_MOBILE_OPERATOR 3 1 3 22
    INDEX RANGE SCAN DB_AGENT IDX_USER_SAS 224 56 222 而2009-09-01 ~ 2009-09-30 走HASH JOIN 执行过程如下：
SELECT STATEMENT, GOAL = ALL_ROWS 298 23126 286 1965710
 SORT GROUP BY 298 23126 286 1965710
  HASH JOIN 295 23126 286 1965710
   TABLE ACCESS FULL DB_AGENT T_STATIC_MOBILE_OPERATOR 3 1 3 22
   TABLE ACCESS FULL DB_AGENT T_USER_STATISTICS 290 69377 283 4370751
各时间的数据量 是每天用户的访问量

Answer 3

SQL语句的执行计划走不走索引除了与Statistic有关系，还有pfile的一个参数有关：optimizer_index_cost_adj. 该参数影响优化器选择索引还是全表扫描的倾向,建议将其设为40. 楼主看看这个参数是多少..

Answer 4

谢谢，optimizer_index_cost_adj 参数确实可以产生我的问题。。但是我改成40了依旧是上述结果没有解决··········alter session set optimizer_index_cost_adj =40 

Answer 5

oracle中索引是最复杂的一个概念,能影响索引的条件很多!
有一个条件是经常被别人忽略的,我简单说一下,也可能不是这个条件导致楼主的问题,但还是希望你能查看一下:
oracle在dba_indexes中提供一个名为 clustering_factor 的列,通知优化器关于表的行与索引的同步情况.
当集簇因子接近数据块的数量时，表的行与索引同步。
列值的选择性、db_block_size、avg_row_len 以及集合基数全都协同工作，帮助优化器决定是使用索引还是使用全表扫描.
如果数据列具有高度的选择性和低的 clustering_factor，则索引扫描通常是最快的执行方法.
相反，高 clustering_factor 的数值达到表中的行数 num_rows，表明这些行的顺序与索引中的顺序不同，
索引范围扫描将会需要额外的 I/O。由于 clustering_factor 达到表中的行数，这些行与索引不同步.
但是，即使列具有高度的选择性，高 clustering_factor 和小 avg_row_len 也会表示列值在表中随机分布，
而获取这些行需要额外的 I/O.在此情况下，索引范围扫描会导致大量不必要的 I/O,全表扫描则会高效得多.
总而言之，clustering_factor、db_block_size 和 avg_row_len 全都影响优化器有关执行全表扫描或是索引范围扫描的决策.
analyze table t_user_statistics compute statistics;select t.table_name,t.num_rows,t.blocks,t.avg_row_len,i.index_name,i.clustering_factor 
from user_tables t,user_indexes i 
where t.table_name=i.table_name and t.table_name='T_USER_STATISTICS';把结果发上来看下