1. 关于索引跳跃扫描
举个例子说明,表employees上有2列sex,employee_id,在(sex,employee_id)上创建复合索引,这两列上的值分布类似于:
('F',98)
('F',100)
('F',102)
('F',104)
('M',101)
('M',103)
('M',105)
当进行索引范围扫描时,这个索引在逻辑上被分为2个子索引,一个具有键值F,另一个具有键值M。当进行如下查询时:
SELECT *
FROM employees
WHERE employee_id = 101;
数据库不会对整个索引进行完全扫描,而是先对F键值的子索引进行扫描,然后对M键值的子索引扫描。
2。索引跳跃扫描测试
SQL> create index idx_s_id on emp(sex,employee_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select count(distinct(sex)),count(distinct(employee_id)) from emp;
COUNT(DISTINCT(SEX)) COUNT(DISTINCT(EMPLOYEE_ID))
-------------------- ----------------------------
2 107
SQL> select table_name,index_name,column_name,column_position
2 from user_ind_columns where table_name='EMP';
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
---------- ---------- --------------- ---------------
EMP IDX_S_ID SEX 1
EMP IDX_S_ID EMPLOYEE_ID 2
SQL> set autot on
SQL> select * from emp where employee_id=102;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID S
----------- -------------------- ------------------------- ---------- -
102 Lex De Haan AD_VP F
Execution Plan
----------------------------------------------------------
Plan hash value: 860049636
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_S_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=102)
filter("EMPLOYEE_ID"=102)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
663 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可见,采用索引跳跃扫描的情况下,这个sql的逻辑读只有3,优化器再一次发挥了威力。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-536352/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-536352/