Oracle访问索引的执行计划(一)
Oracle访问索引的执行计划(二)
Oracle访问索引的执行计划(三)
Oracle访问索引的执行计划(四)
对于复合索引,如果前导列的不同值很少,而非前导列的不同值很多,那么在查询谓词条件中没有指定复合索引的前导列,则可能会使用上索引跳跃扫描(INDEX SKIP SCAN)。
SQL> create table emp as select * from employees;
Table created.
SQL> alter table emp add(gender varchar2(10));
Table altered.
SQL> update emp set gender='M' where employee_id<155;
55 rows updated.
SQL> update emp set gender='F' where employee_id>=155;
52 rows updated.
SQL> commit;
Commit complete.
SQL> create index idx_emp_gender_id on emp(gender,employee_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','EMP',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
SQL> select employee_id,last_name,salary,gender from emp where employee_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 3822662258
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_EMP_GENDER_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2140113/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2140113/