全表扫描 vs 索引

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

 

SQL> show parameter optimizer_mode;

NAME                        TYPE          VALUE
---------------------      -----------    ------------------------------
optimizer_mode      string         ALL_ROWS

 

1.某表的查询字段上建了索引
2.查询条件中用到了该字段
3.统计信息也比较准确
4.并且返回的数据量非常小(百万级的表只返回1条数据)
5.没有在查询字段上用到函数
6.没有隐式数据类型的转换
7.该字段没有空值
8.该字段索引的选择性较好，没有重复数据

9.没有用hint
10.也没有绑定变量，没有固定执行计划
11.不是分区表

上述前提下简单的单表查询，还有那些情况会导致查询走全表扫，不走索引？
 

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set timing on;
SQL> set autotrace on;
SQL> SELECT * FROM T WHERE STARTNO<='5002' AND   ENDNO>='5003';
STARTN ENDNO
------ ------
5002   5003
已用时间:  00: 00: 00.23
执行计划
----------------------------------------------------------
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2001 | 16008 |     6   (0)|
|   1 |  TABLE ACCESS FULL| T    |  2001 | 16008 |     6   (0)|
---------------------------------------------------------------

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        247  bytes sent via SQL*Net to client
        243  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

怎么让上述查询走索引呢。从开发那了解到STARTNO代表起始段号，ENDNO代表结束段号。在一条记录中起始段号一定小于结束段号。

 

利用这个特性可以将WHERE 条件中的 STARTNO<='5002' 改为 STARTNO='5002'  这样就利用到了索引。

 

大家想想为什么？

 

SQL> SELECT * FROM T WHERE STARTNO='5002' AND   ENDNO>='5003';
STARTN ENDNO
------ ------
5002   5003
已用时间:  00: 00: 00.20

执行计划
----------------------------------------------------------
--------------------------------------------------------------------------------

| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |     8 |     2   (0
)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |     8 |     2   (0
)|

|   2 |   INDEX RANGE SCAN          | IDX_T_STARTNO |     1 |       |     1   (0
)|

--------------------------------------------------------------------------------
--

Note
-----
   - 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        264  bytes sent via SQL*Net to client
        243  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

 

---

 

相关测试脚本：

 

CREATE TABLE t(STARTNO  VARCHAR2(6) ,ENDNO VARCHAR2(6));

 

INSERT INTO T  (SELECT LEVEL+1000, LEVEL + 1001 FROM DUAL CONNECT BY  LEVEL <8000 )

 

create index IDX_T_STARTNO on T (startno);

 

ANALYZE TABLE T COMPUTE STATISTICS;

 

 

---