索引跳跃式扫描（INDEX SKIP SCAN）
索引跳跃式扫描（INDEX SKIP SCAN）适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该 索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中"跳跃"（SKIP）一词的含义。
为什么在where条件中没有对目标索引的前导列指定查询条件但Oracle依然可以用上该索引呢？这是因为Oracle帮你对该索引的前导列的所有distinct值做了遍历。
创建一个测试表EMPLOYEE：
create table employee(gender varchar2(1),employee_id number);
将该表的列EMPLOYEE_ID的属性设为NOT NULL：
alter table employee modify(employee_id not null);
创建一个名为IDX_EMPOLYEE的复合B树索引，其中列GENDER是该索引的前导列，列EMPLOYEE_ID是该索引的第二列：
create index idx_employee on employee(gender,employee_id);
使用如下PL/SQL代码往表EMPLOYEE中插入10,000条记录，其中5,000条记录的列GENDER的值为"F"，另外5,000条记录的列GENDER的值为"M"：
begin
for i in 1..5000 loop
insert into employee values ('F',i);
end loop;
commit;
end;
begin
for i in 5001..10000 loop
insert into employee values ('M',i);
end loop;
commit;
end;
对表EMPLOYEE 及索引收集一下统计信息：
analyze table EMPLOYEE compute statistics for table for all columns for all indexes;
执行以下sql
select * from employee where employee_id = 100;
where条件是"employee_id = 100"，即它只对复合B树索引IDX_EMPOLYEE的第二列EMPLOYEE_ID指定了查询条件，但并没有对该索引的前导列GENDER指定任何查询条件。
set autotrace traceonly select * from employee where employee_id = 100;
执行计划如下:
从上述显示内容可以看出，Oracle在执行时用上了索引IDX_EMPOLYEE，并且其执行计划走的就是对该索引的索引跳跃式扫描。
这里在没有指定前导列的情况下还能用上述索引，就是因为Oracle帮我们对该索引的前导列的所有distinct值做了遍历。
所谓的对目标索引的所有distinct值做遍历，其实际含义相当于对原目标SQL做等价改写（即把要用的目标索引的所有前导列的distinct 值都加进来）。索引IDX_EMPOLYEE的前导列GENDER的distinct值只有"F"和"M"两个值，所以这里能使用索引 IDX_EMPOLYEE的原因可以简单地理解成是Oracle将范例SQL 9等价改写成了如下形式：
select * from employee where gender = 'F' and employee_id = 100 union all select * from employee where gender = 'M' and employee_id = 100;
Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。
索引范围扫描（INDEX RANGE SCAN）
索引范围扫描（INDEX RANGE SCAN）适用于所有类型的B树索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询（谓词条件为 BETWEEN、<、>等）；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制（可以是等值查询，也可以是范围查询）。 索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中"范围"二字的本质含义。
根据索引的类型与where限制条件的不同，有4种类型的索引扫描：
索引唯一扫描(index unique scan)
索引范围扫描(index range scan)
索引全扫描(index full scan)
索引快速扫描(index fast full scan)
对于unique index（唯一性索引，是带唯一性约束的，普通索引则没有唯一性约束。scott.emp表是主键，带有唯一性约束，同时有索引Rowid）来说，如果where 条件后面出现了<,> ,between ...and...的时候，那么就可能执行index range scan,如果where条件后面是=，那么就会执行index unique scan。
例1：where条件后面是=的情况
SQL> explain plan for select * from scott.emp a where a.empno='7369'; Explained SQL> select plan_table_output from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | | |* 2 | INDEX UNIQUE SCAN | PK_EMP | | | | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."EMPNO"=7369) Note: rule based optimization 15 rows selected 例2：where 条件后面出现了<,> ,between ...and...的情况 SQL> explain plan for select * from scott.emp a where a.empno < '7369'; Explained SQL> select plan_table_output from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | | |* 2 | INDEX RANGE SCAN | PK_EMP | | | | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."EMPNO"<7369) filter("A"."EMPNO"<7369) Note: rule based optimization 16 rows selected
2、对于none unique index来说 如果where 条件后面出现了=、>、<、betweed...and...的时候，就有可能执行index range scan。
例子：
SQL> create table emp1 as select * from scott.emp; Table created SQL> create index emp1_name_idx on emp1(ename); Index created SQL> explain plan for select * from emp1 a where a.ename = 'SMITH'; Explained SQL> select plan_table_output from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | | | | |* 2 | INDEX RANGE SCAN | EMP1_NAME_IDX | | | | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."ENAME"='SMITH') Note: rule based optimization 15 rows selected
3、对于组合索引来说，如果where条件后面出现了组合索引的引导列，那么可能执行index range scan。
例：
SQL> create index emp1_name_empno_idx on emp1(empno,ename); Index created SQL> explain plan for select * from emp1 a where a.ename = 'SMITH' and empno='33'; Explained SQL> select plan_table_output from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cos -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | | | |* 2 | INDEX RANGE SCAN | EMP1_NAME_EMPNO_IDX | | | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."EMPNO"=33 AND "A"."ENAME"='SMITH') Note: rule based optimization 15 rows selected
数据库中索引会失效的几种情况（oracle）
创建Oracle 索引的目的是为了避免全表扫描数据，提高查询效率，但是如果sql语句写的不好致使索引失效，反而会影响数据查询效率。以下几种情况就会导致索引失效：
1.查询的数量是大表的大部分
说明：单次查询如果查出表的大部分数据，这会导致编译器认为全表扫描性能比走索引更好，从而导致索引失效。一般单次查询数量大概占大表的30%以上索引会失效。
2.索引本身失效
说明：索引需要定期重建。
重建索引的原因主要包括：
1、 删除的空间没有重用，导致索引出现碎片
2、 删除大量的表数据后，空间没有重用，导致索引"虚高"
3、索引的 clustering_facto 和表不一致
3.查询条件使用函数在索引列上
select * from test where round(id)=10; 此时id的索引已经不起作用了.
正确的例子：
首先建立函数索引:create index test_id_fbi_idx on test(round(id));
然后 select * from test where round(id)=10;这时函数索引起作用了 .
4.对小表查询
说明：对于数据量较少的表，本身不需要创建索引，如果建立了索引，索引未必生效。
5.提示不使用索引
说明：SQL执行时强行指定不走索引。
6.统计数据不真实或者表没分析
说明：数据库会定时对表进行分析，如果表过大导致分析计划没有及时跑完，或者由于其他原因导致统计数据不真实，这样会导致CBO计算走索引花费不准确的情况，可能会导致不走索引而使用全表扫描。这也是为什么当表的数据量达到一定级别的时候，我们建议进行分表分库，因为表数据量过大，可能导致表分析过程没有执行完成。
7.隐式转换导致索引失效
这一点应当引起重视，也是开发中经常会犯的错误.。由于表的字段tu_mdn定义为varchar2(20), 但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.。
错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn='13333333333';
8.对索引列进行运算导致索引失效
对索引列进行运算包括(+，-，*，/，! 等)
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
9.where 子句中使用<>或者!=
说明：形如select * from table where id <>1 这样的情况一般不走索引。
10.like "%****" 百分号在前（like '%XX'或者like '%XX%'）
说明：
select * from table where name like ‘%aaa’ 不走索引
select * from table where name like 'aaa%' 走索引
11.单独引用复合索引里非第一位置的索引列
说明：索引遵循最左原则，形如ABC的复合索引如果单独使用B或者C则不走此复合索引。
复合索引 :ABC
引用情况： ABC（走ABC复合索引） AB(走AB联合索引) AC（使用索引A） BC(不使用索引) A(使用索引A) B(不使用索引) C（不使用索引）
12.not in ,not exist
说明：使用not in ,not exist一般不走索引。
在使用 not in 的时候，需要保证子查询的匹配字段是非空的。如若不然，就会导致 not in 返回的整个结果集为空。
13.is null,is not null
B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走 联合索引的not NULL比较麻烦，实际需要测试，联合索引is not null会走索引，同样也需要满足最左原则。
14.条件中有or
select * from table where id = 10 or pid = 10 ； 此类查询必须满足id和pid均有索引，如果只是id或者pid存在索引，则索引不生效。
可以改为：
select * from table where id = 10 union select * from table where pid = 10 ； 这样的话，就不需要满足id和pid均有索引的条件。
15.in,exists
in： select * from t1 where id in (select id from t2);
大表 t2 做外表还是内表，都会走索引的，小表 t1 做内表时也会走索引,小表t1做外表不走索引
exists: select * from t1 where exists (select 1 from t2 where t1.id=t2.id);
t1 表哪种情况都不会走索引，而 t2 表是有索引的情况下就会走索引。
最终结论： 外层大表内层小表，用in。外层小表内层大表，in和exists效率差不多（甚至 in 比 exists 还快，而并不是网上说的 exists 比 in 效率高）。
PS:使用in需要注意，如果内层不是子查询，而是我们组织出来的数据比如 select * from table where id in (1,2,3,4,5...........) 则内层数据每次查询不能超过1000个，否则会导致SQL执行缓慢，对于部分国产数据库甚至可能出现卡死。
select * from t1 where name in (select name from t2);
对于 in 查询来说，会先执行子查询，如上边的 t2 表，然后把查询得到的结果和外表 t1 做笛卡尔积，再通过条件进行筛选（这里的条件就是指 name 是否相等），把每个符合条件的数据都加入到结果集中。
for(x in A){
for(y in B){
if(condition is true) {result.add();}
}
}select * from t1 where exists (select 1 from t2 where t1.name = t2.name);
对于 exists 来说，是先查询遍历外表 t1 ，然后每次遍历时，再检查在内表是否符合匹配条件，即检查是否存在 name 相等的数据。
for(x in A){
if(exists condition is true){result.add();}
}