oracle理解执行计划之索引相关

一、什么是执行计划 所谓执行计划，就是在执行一个SQL前，做出的一份数据库认为最佳的方案。好比在北京上班，是做地铁去还是公交车亦或开车自驾等等。如果做地铁，需要从哪儿换成之类的，... ...从各种方案中选择一个自认为最佳的方案。这个方案在数据库里面即为执行计划。己认为最合适的方案。 二、解析的概念 解析就是为sql生成执行计划的过程。解析分为软解析和硬解析。 三、统计信息与动态采样 统计信息的作用就是为解析sql提供的数据支持，也就是为了更好的选择执行计划。简单说就是统计信息反映表中数据分布的情况。         如果统计信息没有收集，数据库在解析sql的过程中会根据一定的比例去表中采样，采样的结果作为sql执行路径所需代价的依据。这就是动态采样。 四、NULL这个特殊的东西 NULL是个特殊的一个存在。         从定义上来讲，NULL就是一个不确定的数据。所以无论对NULL做任何操作，结果还是NULL。         另外，对于oracle数据库来说，索引里面没有null，索引里面没有NULL会有什么影响呢？哈哈，见“索引快速全扫描”部分。 五、索引相关执行计划(补充索引选择性的概念) 1 全表扫描（full table scan） 1) 测试数据 create table t1 as select * from dba_objects; 2）看执行计划 explain plan for select * from t1 where t1.object_id=19791; select * from table(dbms_xplan.display()); 3）注意动态采样与filter 2 索引唯一扫描(index unique scan) 1）创建唯一性索引 create unique index t1_objectid on t1(object_id); 2）收集统计信息 exec dbms_stats.gather_table_stats('ZQ','T1',cascade=>true); 3) 查看执行计划 explain plan for select * from t1 where t1.object_id=19791; select * from table(dbms_xplan.display()); 4）注意动态采样的消失与access 3 索引范围扫描(index range scan) 1）测试数据 create table t2 as select * from dba_objects; 2) 创建非唯一性索引 create index t2_objectid on t2(object_id); 3) 收集统计信息 exec dbms_stats.gather_table_stats('ZQ','T2',cascade=>true); 4）查看执行计划 explain plan for select * from t2 where t2.object_id=19791; select * from table(dbms_xplan.display()); 5）查看执行计划 explain plan for select * from t1 where t1.object_id>131790; select * from table(dbms_xplan.display()); 4 索引快速全扫描(index fast full scan) 1) 查看执行计划 explain plan for select /*+index(t2 t2_objectid)*/object_id from t2 order by object_id; select * from table(dbms_xplan.display());             --走全表扫描                 insert into t2 select * from t2;                 commit; insert into t2 select * from t2;                 commit;          2) 收集统计信息，然后看执行计划 exec dbms_stats.gather_table_stats('ZQ','T1',cascade=>true);  explain plan for select object_id from t2 order by object_id; select * from table(dbms_xplan.display());             --走全表扫描 3） 创建组合索引,并收集统计信息 create index t2_id_name on t2(object_id,object_name); exec dbms_stats.gather_table_stats('ZQ','T2',cascade=>true); explain plan for select object_id,object_name from t2; select * from table(dbms_xplan.display());    --走全表扫描 explain plan for select /*+index(t2,t2_id_name)*/object_id,object_name from t2; select * from table(dbms_xplan.display()); ---------------------为啥，哈哈哈哈哈哈，not null 4) object_id字段设置成not Null，走索引快速扫描 alter table t2 modify object_id not null; explain plan for select /*+index(t2,t2_id_name)*/object_id,object_name from t2; select * from table(dbms_xplan.display()); 4) 使用count，索引全扫描 explain plan for select  count(object_id) from t2 ; select * from table(dbms_xplan.display()); 5 索引全扫描(index full scan) 1）查看执行计划 explain plan for select t1.object_id from t1 order by t1.object_id; select * from table(dbms_xplan.display()); --注意索引快速全扫描无序，索引全扫描有序