实验一：字段类型设计或SQL条件写法有误导致无法选择索引
drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3
varchar2(20));
insert into t_col_type select rownum,'abc','efg' from dual connect
by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonly
select * from t_col_type where id=6;
--------------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------------
|
|*
--------------------------------------------------------------------------------
奇怪吧？明明id列有索引为什么还走全表扫描？统计信息不准造成的？那我们收集一下统计信息再执行
exec dbms_stats.gather_table_stats(user,'t_col_type',cascade=>true);
select * from t_col_type where id=6;
--------------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------------
|
|*
--------------------------------------------------------------------------------
1 - filter(TO_NUMBER("ID")=6)
执行计划没变，后来发现红色加粗部分对条件进行了类型转换，因此ORACLE无法选择索引，修改where条件id='6'看看效果如何：
select * from t_col_type where id='6';
------------------------------------------------------------------------------------------
| Id
------------------------------------------------------------------------------------------
|
|
|*
------------------------------------------------------------------------------------------
从执行计划来看，查询语句选择走索引，并且执行计划2中变量类型没有转换
实验结论：在SQL写法或在表设计时，选择正确的类型很关键，如果表是varchar2类型，那么where条件中该列值就要添加引号，否则会造成ORACLE生成错误执行计划
知识补充：
select * from t_col_type where to_number(id)=6
select * from t_col_type where to_number = 6
就该实验表的字段类型来看，上面这两条SQL是完全等价的，虽然SQL_ID不一样，但执行计划完全一样，但是
select * from t_col_type where to_number(id)=6
该SQL是开发中新手容易犯错的地方，对where条件后面的谓语加函数进行处理也是SQL执行低下案例中常见的类型，正确的写法应该是将函数处理加在=号后面
select * from t_col_type where id=to_char(6)
这个案例中有朋友可能会说我添加hint 会不会走索引呢？答案是：不会，有兴趣的朋友自己做实验
其实知识补充这个案例完全可以当做一个执行计划不走索引经典案例，不说了，看实验二
实验二：列中有null值，导致ORACLE无法选择索引
create table test as select * from dba_objects;
create index i_object_id on test(object_id);
exec dbms_stats.gather_table_stats(user, 'TEST',cascade =>
true);
set autot trace exp stat
select max(object_id),min(object_id) from test;
---------------------------------------------------------------------------
| Id
---------------------------------------------------------------------------
|
|
|
---------------------------------------------------------------------------
select max(object_id),min(object_id) from test;
---------------------------------------------------------------------------
| Id
---------------------------------------------------------------------------
|
|
|
---------------------------------------------------------------------------
从执行计划输出来看，添加hint在这里也是无用武之地，那我添加一个where条件如何？
select max(object_id),min(object_id) from test where object_id>0;
-------------------------------------------------------------------------------------
| Id
-------------------------------------------------------------------------------------
|
|
|*
-------------------------------------------------------------------------------------
delete from test where object_id is
null;
alter table test modify object_id not null;
select max(object_id),min(object_id) from test where object_id>0;
-------------------------------------------------------------------------------------
| Id
-------------------------------------------------------------------------------------
|
|
|*
-------------------------------------------------------------------------------------
实验结论：对于索引的创建，一定要创建在not null列上，如果创建的索引列是nullable，那么该索引实际上是无效的，切记！
实验三：创建反向索引导致执行计划不走索引
drop table t purge;
create table t (id number,deal_date date,area_code number,nbr
number,contents varchar2(4000));
set autotrace off
insert into t(id,deal_date,area_code,nbr,contents)
commit;
--创建正常索引
create index idx_normal on t(id);
select * from t where id>=28 and
id<=50;
------------------------------------------------------------------------------------------
| Id
------------------------------------------------------------------------------------------
|
|
|*
------------------------------------------------------------------------------------------
--创建反向索引
create index idx_reverse on t(id)
reverse;
select * from t where
id>=28 and id<=50;
执行计划
--------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------
|
|*
--------------------------------------------------------------------------
实验结论：反向索引可以很好解决索引热块问题，但是要明确一点，反向索引只适用于等值查询，如果业务中存大大量范围查询，那么创建反向索引会导致执行计划走全表扫描，创建反向索引需要仔细确认业务需要（切记）
温馨提示:使用hint指定index的时候需要慎重，如果不合适或者无效，可能导致程序效率更低，因为如果我们使用hint指定了一个无效的index，优化器会扫描表中所有可以使用的index，然后选择cost最小的index或者index组合，而不会选择全表扫描。
ORACLE虚拟索引说解：http://blog.sina.com.cn/s/blog_61cd89f60102eeyb.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29257188/viewspace-1116222/ ，如需转载，请注明出处，否则将追究法律责任。
