1、索引列有隐式转换，不走索引
2、索引列倾斜，个别值查询时，走索引代价比走全表扫描更高，不走索引
3、索引列没有限制 not null，因索引不存储空值，如果不限制索引列是not null，oracle会认为索引列有可能存在空值，所以不会按照索引计算。
下面是对每种情况的，对比测试结果：
SQL> remark 创建测试数据
SQL> drop table test purge;
SQL> create table test(id varchar2(100),name varchar2(100));
SQL> INSERT INTO test
2(id, NAME)
3SELECT to_char(rownum) AS id, 'name_' || rownum AS NAME
4 FROM dual
5CONNECT BY rownumcreate index ind_test on test (id);
SQL> remark 因为隐式转换，查询不走索引
SQL> ALTER system flush shared_pool;
SQL> ALTER system flush BUFFER_CACHE;
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select * from test where id=1;--因发生了隐式转换，不走索引（第一种情况）
已用时间:00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 4 | 416 | 104 (2)| 00:00:02 |
|*1 |TABLE ACCESS FULL| TEST | 4 | 416 | 104 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=1)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_9f0t61rapfyzd97bbe3d0" used for this statement
SQL> select * from test where id='1'; --没有发生隐式转换，所以走索引。
已用时间:00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 | 104 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TEST | 1 | 104 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN
| IND_TEST | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='1')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_bdcx4n8hmwgbdb8b4d7d8" used for this statement
SQL> set autotrace off;
SQL> set timing off;
SQL>
SQL> remark 索引列倾斜，查询个别值不走索引（走索引代价比全表扫描更高）
SQL> update test set id = 1 where idupdate test set id = 2 where id between 11 and 2000;
SQL> update test set id = 3 where id > 2000;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('lhq','test',method_opt=>'for all indexed columns',cascade=>true);
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select * from test where id='3';--该值倾斜，走全表扫描性能更好，不走索引（第二种情况）。
已用时间:00: 00: 25.68
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 98050 |1244K| 103 (1)| 00:00:02 |
|*1 |TABLE ACCESS FULL| TEST | 98050 |1244K| 103 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"='3')
Note
-----
- SQL plan baseline "SQL_PLAN_89ypdy6tjpp1u97bbe3d0" used for this statement
SQL> select * from test where id='1';
---走索引性能更好。
已用时间:00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
|18 | 234 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TEST |18 | 234 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN
| IND_TEST |18 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='1')
Note
-----
- SQL plan baseline "SQL_PLAN_bdcx4n8hmwgbdb8b4d7d8" used for this statement
SQL> set autotrace off;
SQL> set timing off;
SQL>
SQL> remark 索引列不限制 not null，会不走索引（索引不存储空值，如果不说明索引列是not null，索引列可能存在空值，所以不会按照索引计算）
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select count(*) from test;- -因为没有限制索引列 not null，oracle认为索引列可能有空值，所以不会按照索引统计。不走索引（第三种情况）。
已用时间:00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id| Operation
| Name | Rows| Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 103 (1)| 00:00:02 |
| 1 |SORT AGGREGATE|| 1 |
|
|
| 2 | TABLE ACCESS FULL| TEST | 100K| 103 (1)| 00:00:02 |
-------------------------------------------------------------------
SQL> set autotrace off;
SQL> set timing off;
SQL>
SQL> alter table test modify id not null;--添加限制索引列为not null
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select count(*) from test; --走索引
已用时间:00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 2611454708
--------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|
| 1 |90 (2)| 00:00:02 |
| 1 |SORT AGGREGATE |
| 1 |
|
|
| 2 | INDEX FAST FULL SCAN| IND_TEST | 100K|90 (2)| 00:00:02 |
--------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_5tjk9d7zy6t9r848d8310" used for this statement
SQL> set autotrace off;
SQL> set timing off;
SQL> spool off;
|
