首页 > 代码库 > Oracle 表的访问方式(2)-----索引扫描
Oracle 表的访问方式(2)-----索引扫描
索引扫描(Index scan)
我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值),然后根据rowid直接从表中得到具体的数据,这种查找方式称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据,该行对应的数据块是通过一次i/o得到的,在此情况下该次i/o只会读取一个数据库块。在索引中,除了存储每个索引的值外,索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成: (1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。
根据索引的类型与where限制条件的不同,有5种类型的索引扫描:
1)索引唯一扫描(index unique scan)
2)索引范围扫描(index range scan)
3)索引全扫描(index full scan)
4)索引快速扫描(index fast full scan)
5)索引跳跃扫描(INDEX SKIP SCAN)
索引唯一扫描(INDEX UNIQUE SCAN)
通过唯一索引查找一个数值经常返回单个ROWID
唯一索引由单独列组成:
1 --收集统计信息 2 SQL> exec dbms_stats.gather_table_stats(‘SCOTT‘,‘EMP‘); 3 4 PL/SQL procedure successfully completed. 5 6 Commit complete. 7 SQL> 8 9 10 --获取创建索引语句11 SQL> SELECT DBMS_METADATA.GET_DDL(‘INDEX‘,u.index_name)12 2 FROM USER_INDEXES u13 3 WHERE u.TABLE_NAME=‘EMP‘;14 15 DBMS_METADATA.GET_DDL(‘INDEX‘,U.INDEX_NAME)16 --------------------------------------------------------------------------------17 CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")18 PCTFREE 1019 20 SQL>21 22 1.索引名称 PK_EMP23 2.索引包含列 EMPNO24 3.索引为唯一索引25 26 --执行计划走唯一索引的语句27 SQL> SELECT * FROM SCOTT.EMP WHERE EMPNO=‘7369‘;28 29 Execution Plan30 ----------------------------------------------------------31 Plan hash value: 294954413932 33 --------------------------------------------------------------------------------------34 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |35 --------------------------------------------------------------------------------------36 | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |37 | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |38 |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |39 --------------------------------------------------------------------------------------40 41 Predicate Information (identified by operation id):42 ---------------------------------------------------43 44 2 - access("EMPNO"=7369)45 46 47 48 SQL> SELECT * FROM SCOTT.EMP WHERE EMPNO IN (‘7499‘,‘7521‘);49 50 51 Execution Plan52 ----------------------------------------------------------53 Plan hash value: 235504992354 55 ---------------------------------------------------------------------------------------56 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |57 ---------------------------------------------------------------------------------------58 | 0 | SELECT STATEMENT | | 2 | 76 | 2 (0)| 00:00:01 |59 | 1 | INLIST ITERATOR | | | | | |60 | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 76 | 2 (0)| 00:00:01 |61 |* 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)| 00:00:01 |62 ---------------------------------------------------------------------------------------63 64 Predicate Information (identified by operation id):65 ---------------------------------------------------66 67 3 - access("EMPNO"=7499 OR "EMPNO"=7521)68 69 SQL> SELECT * FROM SCOTT.EMP WHERE EMPNO=‘7499‘ OR EMPNO=‘7521‘;70 71 Execution Plan72 ----------------------------------------------------------73 Plan hash value: 235504992374 75 ---------------------------------------------------------------------------------------76 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |77 ---------------------------------------------------------------------------------------78 | 0 | SELECT STATEMENT | | 2 | 76 | 2 (0)| 00:00:01 |79 | 1 | INLIST ITERATOR | | | | | |80 | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 76 | 2 (0)| 00:00:01 |81 |* 3 | INDEX UNIQUE SCAN | PK_EMP | 2 | | 1 (0)| 00:00:01 |82 ---------------------------------------------------------------------------------------83 84 Predicate Information (identified by operation id):85 ---------------------------------------------------86 87 3 - access("EMPNO"=7499 OR "EMPNO"=7521)88 SQL>
SELECT * FROM SCOTT.EMP WHERE EMPNO=‘7369‘;
SELECT * FROM SCOTT.EMP WHERE EMPNO IN (‘7499‘,‘7521‘);
SELECT * FROM SCOTT.EMP WHERE EMPNO=‘7499‘ OR EMPNO=‘7521‘
总结,索引在where条件中,且谓词条件可以确定唯一值时,走唯一索引。思考下2,3语句的查询过程
唯一索引由多个列组成(即组合索引)
1 --创建一个唯一索引(优质索引) 2 create unique index scott.idx_test on scott.emp(ename, deptno); --ename为引导列,表中ename列值具有唯一性 3 4 --谓词条件中的列顺序与索引的列顺序完全一致,走唯一索引 5 SQL> select * from scott.emp where ename = ‘ALLEN‘ and deptno = 20 ; 6 7 no rows selected 8 9 10 Execution Plan11 ----------------------------------------------------------12 Plan hash value: 401058387713 14 ----------------------------------------------------------------------------------------15 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |16 ----------------------------------------------------------------------------------------17 | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |18 | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |19 |* 2 | INDEX UNIQUE SCAN | IDX_TEST | 1 | | 0 (0)| 00:00:01 |20 ----------------------------------------------------------------------------------------21 22 Predicate Information (identified by operation id):23 ---------------------------------------------------24 25 2 - access("ENAME"=‘ALLEN‘ AND "DEPTNO"=20)26 27 28 --谓词条件中的列顺序与唯索引的列顺序不一致 ,走唯一索引29 SQL> select * from scott.emp where deptno = 20 and ename = ‘ALLEN‘; 30 31 no rows selected32 33 34 Execution Plan35 ----------------------------------------------------------36 Plan hash value: 401058387737 38 ----------------------------------------------------------------------------------------39 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |40 ----------------------------------------------------------------------------------------41 | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |42 | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |43 |* 2 | INDEX UNIQUE SCAN | IDX_TEST | 1 | | 0 (0)| 00:00:01 |44 ----------------------------------------------------------------------------------------45 46 Predicate Information (identified by operation id):47 ---------------------------------------------------48 49 2 - access("ENAME"=‘ALLEN‘ AND "DEPTNO"=20)50 51 --只有引导列在谓词条件中52 SQL> select * from scott.emp where ename = ‘ALLEN‘; --即使是唯一数据 也不走唯一索引53 54 55 Execution Plan56 ----------------------------------------------------------57 Plan hash value: 231753838558 59 ----------------------------------------------------------------------------------------60 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |61 ----------------------------------------------------------------------------------------62 | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |63 | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |64 |* 2 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 |65 ----------------------------------------------------------------------------------------66 67 Predicate Information (identified by operation id):68 ---------------------------------------------------69 70 2 - access("ENAME"=‘ALLEN‘)71 72 73 --引导列不在谓词条件中74 SQL> select * from scott.emp where deptno = 20;75 76 77 Execution Plan78 ----------------------------------------------------------79 Plan hash value: 395616093280 81 --------------------------------------------------------------------------82 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |83 --------------------------------------------------------------------------84 | 0 | SELECT STATEMENT | | 5 | 190 | 3 (0)| 00:00:01 |85 |* 1 | TABLE ACCESS FULL| EMP | 5 | 190 | 3 (0)| 00:00:01 |86 --------------------------------------------------------------------------87 88 Predicate Information (identified by operation id):89 ---------------------------------------------------90 91 1 - filter("DEPTNO"=20)92 93 SQL>
1 --创建一个唯一索引(劣质索引) 2 create unique index idx_test on scott.emp(deptno,ename); --deptno为引导列,表中deptno列值不具有唯一性 3 4 分别对别如下sql的执行计划: 5 --谓词条件中的列顺序与索引的列顺序完全一致,,走唯一索引 6 SQL> select * from scott.emp where deptno = 20 and ename = ‘ALLEN‘; 7 8 no rows selected 9 10 Execution Plan11 ----------------------------------------------------------12 Plan hash value: 153105832613 14 ------------------------------------------------------------------------------------------15 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |16 ------------------------------------------------------------------------------------------17 | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |18 | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |19 |* 2 | INDEX UNIQUE SCAN | IDX_TEST01 | 1 | | 0 (0)| 00:00:01 |20 ------------------------------------------------------------------------------------------21 22 Predicate Information (identified by operation id):23 ---------------------------------------------------24 25 2 - access("DEPTNO"=20 AND "ENAME"=‘ALLEN‘)26 27 SQL> 28 29 --谓词条件中的列顺序与唯索引的列顺序不一致 ,走唯一索引30 SQL> select * from scott.emp where ename = ‘ALLEN‘ and deptno = 20 ;31 32 no rows selected33 34 Execution Plan35 ----------------------------------------------------------36 Plan hash value: 153105832637 38 ------------------------------------------------------------------------------------------39 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |40 ------------------------------------------------------------------------------------------41 | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |42 | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |43 |* 2 | INDEX UNIQUE SCAN | IDX_TEST01 | 1 | | 0 (0)| 00:00:01 |44 ------------------------------------------------------------------------------------------45 46 Predicate Information (identified by operation id):47 ---------------------------------------------------48 49 2 - access("DEPTNO"=20 AND "ENAME"=‘ALLEN‘)50 51 SQL> 52 53 --只有引导列在谓词条件中54 SQL> select * from scott.emp where deptno = 20;55 56 Execution Plan57 ----------------------------------------------------------58 Plan hash value: 56073756259 60 ------------------------------------------------------------------------------------------61 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |62 ------------------------------------------------------------------------------------------63 | 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |64 | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |65 |* 2 | INDEX RANGE SCAN | IDX_TEST01 | 5 | | 1 (0)| 00:00:01 |66 ------------------------------------------------------------------------------------------67 68 Predicate Information (identified by operation id):69 ---------------------------------------------------70 71 2 - access("DEPTNO"=20)72 SQL> 73 74 --引导列不在谓词条件75 SQL> select * from scott.emp where ename = ‘ALLEN‘;76 77 Execution Plan78 ----------------------------------------------------------79 Plan hash value: 395616093280 81 --------------------------------------------------------------------------82 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |83 --------------------------------------------------------------------------84 | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |85 |* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |86 --------------------------------------------------------------------------87 88 Predicate Information (identified by operation id):89 ---------------------------------------------------90 91 1 - filter("ENAME"=‘ALLEN‘)92 93 SQL>
总结:使用组合索引时,遵守以下原则:
1.引导列标识性要强;
2.索引列尽量全部出现在谓词条件中
3.引导列尽量出现在谓词条件中
索引范围扫描(INDEX RANGE SCAN)
使用一个索引存取多行数据,在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符 (如>、<、<>、>=、<=、between)。在非唯一索引上,谓词"="也可能返回多行数据,所以在非唯一索引上都使用索引范围扫描。
使用index rang scan的3种情况: 1.在唯一索引列上使用了range操作符(> < <> >= <= between) 2.在组合索引上,只使用部分列进行查询,导致查询出多行 3.对非唯一索引列上进行的任何查询。
索引全扫描(index full scan)
与全表扫描对应,也有相应的全索引扫描。在某些情况下,可能进行全索引扫描而不是范围扫描,需要注意的是全索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时,才进行全索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。
一般通过索引进行排序时,会用到(index full scan)
索引快速扫描(index fast full scan)
扫描索引中的所有的数据块,与 index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。在这种存取方法中,可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。
索引跳跃扫描(INDEX SKIP SCAN)
Skip Scans are initiated by probing the index for distinct values of the prefix column. Each of these distinct values is then used as a starting point for a regular index search. The result is several separate searches of a single index that, when combined, eliminate the affect of the prefix column.
skip scan会探测出索引前导列的唯一值个数,每个唯一值都会作为常规扫描的入口,在此基础上做一次查找,最后合并 这些查询。例如:表employees (sex, employee_id, address) ,有一个组合索引(sex, employee_id). 在索引跳跃的情况 下,我们可以逻辑上把他们看成两个索引,一个是(男,employee_id),一个是(女,employee_id). select * from employees where employee_id=1;发出这个查询后,oracle先进入sex为男的入口,查找employee_id=1的条目。 再进入sex为女的入口,查找employee_id=1的条目。最后合并两个结果集
参考blog:http://www.itpub.net/thread-1372696-1-1.html
http://blog.csdn.net/dba_waterbin/article/details/8550405
Oracle 表的访问方式(2)-----索引扫描