Selectivity-索引的可选择性

2019年12月26日 1262点热度 0人点赞 2条评论

同事反应这个查询“较慢”,并且没有走索引:
select * from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9102';

经分析,这是一个400万的表:
select count(*) from pl_georoute;
4473003

以上查询结果集达到50万:
select count(*) from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9102';
508381

于是想到了,根据oracle对于SQL执行计划基于成本的优化原则,这么大的结果集,相对于400万的表来讲,索引不如全表扫描快。
之所以反应慢,估计是结果太大,加上网络传输等其他环节,因此在应用看来“较慢”。

以下做验证。

对于相对较大的结果集,尽管建立了相应的索引,但是oracle可以“任性的无视”。
SQLPLUS> explain plan for select * from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9102';
Explained.
SQLPLUS> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   472K|   195M| 55636   (1)| 00:11:08 |
|*  1 |  TABLE ACCESS FULL| PL_GEOROUTE |   472K|   195M| 55636   (1)| 00:11:08 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ROUTE_TYPE"=U'9102' AND "STATEFLAG"=0)

而对于可选择性【selectivity】较好的查询,oracle会乖乖的重视这个索引。
SQLPLUS> explain plan for select * from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9101';
Explained.
SQLPLUS> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   788 |   334K|    96   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PL_GEOROUTE  |   788 |   334K|    96   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | R175_UNION_1 |   903 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ROUTE_TYPE"=U'9101' AND "STATEFLAG"=0)

liking

我是雪人

文章评论

  • liking

    select * from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9101';
    no rows selected
    第二个查询条件结果。

    2019年12月26日
  • liking

    关于filter和access的解读:
    以上两个查询SQL语句的predicate谓词条件有区别,一个是9102,一个是9101
    filter("ROUTE_TYPE"=U'9102' AND "STATEFLAG"=0)
    access("ROUTE_TYPE"=U'9101' AND "STATEFLAG"=0)
    filter是指:没有合适的访问路径,全表扫了,谓词条件只是起到了filter过滤作用;
    access是指:找到合适的访问路径,走索引了,谓词条件起到了直接access读取作用。

    2019年12月27日