ORACLE 查询不走索引的原因分析,解决办法通过强制索引或动态执行SQL语句提高查询速度

(一)索引失效的原因分析:

  • <>或者单独的>,<,(有时会用到,有时不会)
  • 有时间范围查询;oracle 时间条件值范围越大就不走索引
  • like "%_" 百分号在前.
  • 表没分析(统计信息最好定期收集,以业务的不同确定不同的收集周期,在新增的索引有可能没有收集 ,那么就会导致索引失效)
  • NVARCHAR查询不会走索引。

  解决方法:

    分析方法有一下几种:
    analyze table t1 compute statistics fortable; --针对表收集信息

    analyze table t2 compute statistics for allcolumns; --针对表字段收集信息

    analyze table t3 compute statistics for all indexescolumns; --收集索引字段信息

    analyze table t4 computestatistics;       --收集表,表字段,索引信息

    analyze table t5 compute statistics for allindexes;         --收集索引信息

    analyze table t6 compute statistics for table for all indexesfor allcolumns;   --

    --收集表,索引,表字段信息

  • 单独引用复合索引里非第一位置的索引列.
  • 字符型字段为数字时在where条件里不添加引号.
  • 对索引列进行运算.需要建立函数索引.
  • not in ,not exist.
  • 当变量采用的是times变量,而表的字段采用的是date变量时.或相反情况。
  • 索引失效。
  • 基于cost成本分析(Oracle因为走全表成本会更小):查询小表,或者返回值大概在10%以上;

  解决办法:

    在这种条件下 oracle会认为索引更占资源,就默认不走索引了。这种情况如果觉得索引快的,通过强制索引提高查询速度 

     select   /*+INDEX(t IDEX_HZYB_JSJL_BDJSRQ)*/   from hzyb_jsjl t
     where BDJSRQ >TO_DATE('','');
    其中T是 表名   IDEX_HZYB_JSJL_BDJSRQ 是表的索引

  • 有时都考虑到了 但就是不走索引,drop了从建试试在
  • B-tree索引 is null不会走,is not null会走,(B树索引示例:create index random_empno_idx on test_random(empno);)
  • 位图索引 is null,is not null   都会走(位图索引示例:create bitmap index normal_empno_bmx on test_normal(empno);)
  • 联合索引 is not null 只要在建立的索引列(不分先后)都会走,(组合索引示例:SQL> create index indx_t on t(object_type,object_name);) 

in null时   必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null(但必须在所有列都满足is null的时候),或者=一个值;
当建立索引的第一位置是=一个值时,其他索引列可以是任何情况(包括is null =一个值),以上两种情况索引都会走。其他情况不会走。

(二)通过动态执行SQL提高查询COUNT()执行速度

 1 var_strquery1 := 'SELECT COUNT(1)';
 2       var_strquery := 'FROM r_sn_link k
 3                       LEFT JOIN mfworkstatus s
 4                         ON k.p_sn = s.sysserialno
 5                       LEFT JOIN mfworkorder r
 6                         ON k.wo = r.workorderno
 7                      WHERE 1 = 1 ';
 8 
 9       IF var_smt_wo IS NOT NULL OR var_smt_wo <> ''
10       THEN
11          var_strquery := var_strquery || ' AND k.wo =''' || var_smt_wo || '''';
12       END IF;
13 
14       IF var_dip_wo IS NOT NULL OR var_dip_wo <> ''
15       THEN
16          var_strquery :=
17             var_strquery || ' AND s.workorderno =''' || var_dip_wo || '''';
18       END IF;
19 
20       IF var_skuno IS NOT NULL OR var_skuno <> ''
21       THEN
22          var_strquery :=
23                var_strquery
24             || ' AND (r.skuno ='''
25             || var_skuno
26             || ''''
27             || 'OR s.skuno ='''
28             || var_skuno
29             || ''''
30             || ')';
31       END IF;
32 
33       IF var_sn_code IS NOT NULL OR var_sn_code <> ''
34       THEN
35          var_strquery :=
36             var_strquery || ' AND k.sn_code = ''' || var_sn_code || '''';
37       END IF;
38 
39       IF var_p_sn IS NOT NULL OR var_p_sn <> ''
40       THEN
41          var_strquery := var_strquery || 'AND k.p_sn =''' || var_p_sn || '''';
42       END IF;
43 
44       IF var_from_datetime IS NOT NULL OR var_from_datetime <> ''
45       THEN
46          var_strquery :=
47                var_strquery
48             || ' AND k.work_time >= to_date('
49             || var_from_datetime
50             || ',''yyyy/mm/dd hh24:mi:ss'')';
51       END IF;
52 
53       IF var_to_datetime IS NOT NULL OR var_to_datetime <> ''
54       THEN
55          var_strquery :=
56                var_strquery
57             || ' AND k.work_time <= to_date('
58             || var_to_datetime
59             || ',''yyyy/mm/dd hh24:mi:ss'')';
60       END IF;
61 
62       --动态执行查询语句返回记录数
63       EXECUTE IMMEDIATE var_strquery1 || var_strquery INTO var_rowcount;
原文地址:https://www.cnblogs.com/turnip/p/10452570.html