高分求解，相同的语句执行计划一个是按全表扫描，一个是按索引扫描，为什么呢？

Question

给表tbl_lici_userinfo创建索引如下：
create bitmap index index_status on tbl_lici_userinfo(I_STATUS);
create bitmap index index_period on tbl_lici_userinfo(IUNPERIOD);
create bitmap index index_numtype on tbl_lici_userinfo(IMONITORTYPE);
create  index index_liid on tbl_lici_userinfo(SLIID);
create  index index_num on tbl_lici_userinfo(SMONITORNUM);
create  index index_numinter on tbl_lici_userinfo(SINTERNATIONNUM);
create  index index_numuri on tbl_lici_userinfo(SMONITORURI);
create  index index_numinteruri on tbl_lici_userinfo(SMONITORURI_INTERNATION);查看sql语句的执行计划，相同的语句执行计划一个是按全表扫描，一个是按索引扫描，为什么？SQL> SELECT COUNT(*) FROM ne5_1.TBL_LICI_USERINFO WHERE ILICID = 1 AND ILIGID = 1 AND SMONITORURI = '234fa' AND IMONITORTYPE = 1 AND ( I_STATUS = 1 OR IUNPERIOD = 1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 239174826--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |   144 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                   |     1 |   144 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TBL_LICI_USERINFO |     1 |   144 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | INDEX_NUMURI      |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ILIGID"=1 AND "IMONITORTYPE"=1 AND ("I_STATUS"=1 OR "IUNPERIOD"=1) AND
              "ILICID"=1)
   3 - access("SMONITORURI"='234fa')SQL> SELECT COUNT(*) FROM ne5_1.TBL_LICI_USERINFO WHERE ILICID = 1 AND ILIGID = 1 AND SINTERNATIONNUM = 'ew5352' AND IMONITORTYPE = 1 AND ( I_STATUS = 1 OR IUNPERIOD = 1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 948954670----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |    32 |  9107   (1)| 00:01:50 |
|   1 |  SORT AGGREGATE    |                   |     1 |    32 |            |          |
|*  2 |   TABLE ACCESS FULL| TBL_LICI_USERINFO | 24998 |   781K|  9107   (1)| 00:01:50 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ILIGID"=1 AND "SINTERNATIONNUM"='ew5352' AND "IMONITORTYPE"=1
              AND ("I_STATUS"=1 OR "IUNPERIOD"=1) AND "ILICID"=1)

Answer 1

把表结构描述贴上来看看
desc TBL_LICI_USERINFO;

Answer 2

返回的记录数不一样,所以使用的计划不一样.
如果index和表以及列都进行了正确的统计分析,那么应该:第一个返回的记录很少.第二个返回的记录很多吧.

Answer 3

 index_numinter   这个索引有没有建成功啊？ 以前搞过索引失败的。

Answer 4

顶二楼1.确保索引是成功创建的
2.如果表中的记录变化比较频繁，并使用CBO的策略，进行过表和索引的收集
3.查看两个命令返回的记录数，
若返回的记录太多，ORACLE就不会走索引，而是执行走全表扫描。
analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;

Answer 5

执行计划的选择与语句的选择性 selectivity 有很大的关系。低选择性的语句通常会使用全表扫描。如果一个语句的选择性很低，比如选择了一个表的90%的行，使用索引反而会降低性能。

Answer 6

刚接触oracle不久实在是不太懂。
第二句应该也是选择了一条记录而已啊，还有个Statistics信息我忘了贴了：
第一句的：
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
第二句的：
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      33441  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 7

另外想请问
analyze table t1 compute statistics for table; 
analyze table t2 compute statistics for all columns; 
analyze table t3 compute statistics for all indexed columns; 
analyze table t4 compute statistics; 
这些是做什么用的？

Answer 8

如果统计信息与表中数据的实际分布偏差较大，就会出现选择执行计划的偏差，因为 sql 语句的 selectivity 是根据表、列的统计信息计算得来的。analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics; 
这些语句是用来更新、计算表中数据的统计信息的。

Answer 9

我现在把表drop掉重新创建加索引，表中加入40万条记录后，
再查看这个sql语句
SELECT COUNT(*) FROM ne5_1.TBL_LICI_USERINFO WHERE ILICID = 1 AND ILIGID = 1 AND SINTERNATIONNUM = 'ew5352' AND IMONITORTYPE = 1 AND ( I_STATUS = 1 OR IUNPERIOD = 1 );
是按索引扫描不明白为什么，一会儿是按全表扫描一会儿是按索引扫描，疑惑。
是不是说我之前执行的查询时的统计信息是不准的？所以执行计划偏差了？
那么创建索引时oracle不会自动执行这些统计信息的分析么？

Answer 10

你的SINTERNATIONNUM列是倾斜的吧。
收集下直方图，你执行下：
exec dbms_stats.gather_table_stats('NE5_1','TBL_LICI_USERINFO',cascade=>true)

Answer 11

看你的sql，你这个表怎么不建联合索引？

Answer 12

如果建联合索引的话，不是只有在它的第一个列被where子句引用时,优化器才会选择使用该索引么？

Answer 13

不会的。有index skip scan。
当然在查询条件中最好能固定使用上索引的列。你的查询不都是 ILICID = AND ILIGID =  AND SMONITORURI =  AND IMONITORTYPE =吗？