[Index]Index Scan

Index B-Tree

Index的叶节点指向RowID，然后根据RowID再找对应的Block

语法：CREATE INDEX NAME_SALARY_IDX ON PERSON(LAST_NAME ASC,SALARY DESC);

Index Unique Scan

对于unique index来说,如果where条件后面是=，那么就会执行index unique scan。

AskTom:well, the optimizer can look at an index that is unique and say "ah-hah, if you use "where x =:x and y = :y and ...." I'm going to get ONE row back, I can cost that much better"   (refer)

Index Unique Scan的条件：

1.Index是唯一性索引;

2.where条件类似于 x = :x and y=: y ...;

Example:

CREATE TABLE PT_TEST AS SELECT * FROM DBA_OBJECTS;CREATE UNIQUE INDEX UNIQUE_IDX ON PT_TEST(OBJECT_ID);    --UNIQUE_IDX是唯一索引EXEC DBMS_STATS.GATHER_TABLE_STATS('APPS','PT_TEST', CASCADE=>TRUE ); --重做统计SQL> set auto traceSQL> select * from pt_test where object_id=10;Elapsed: 00:00:00.47Execution Plan----------------------------------------------------------Plan hash value: 2398730171------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |     1 |    97 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| PT_TEST    |     1 |    97 |     3   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN         | UNIQUE_IDX |     1 |       |     2   (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=10)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size       1194  bytes sent via SQL*Net to client        338  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

Performance:index unique scan的性能非常好。

Index Range Scan

Case 1

基于上面的case，还是以object_id这个唯一性索引列为条件

SQL> select owner from pt_test where object_id<10;8 rows selected.Elapsed: 00:00:00.45Execution Plan----------------------------------------------------------Plan hash value: 1470047708------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |     1 |    11 |     4   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| PT_TEST    |     1 |    11 |     4   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | UNIQUE_IDX |     1 |       |     3   (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"<10)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          6  consistent gets          0  physical reads          0  redo size        388  bytes sent via SQL*Net to client        338  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          8  rows processedSQL>

对于上边这个例子，Index Unique Scan和Index Range Scan在B Tree上的搜索路径是一样的，只是Index Unique Scan在找到应该含有要找的Index Key的block后便停止了搜索，因为该键是唯一的。而Index Range Scan还要循着指针继续找下去直到条件不满足时。

Case 2

这个时候给owner字段增加一个非唯一性索引

create index ind_owner on pt_test(owner);  --给owner字段增加非唯一性索引

SQL> select owner from pt_test where owner='SCOTT';123 rows selected.Elapsed: 00:00:00.80Execution Plan----------------------------------------------------------Plan hash value: 2280863269------------------------------------------------------------------------------| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT |           |  1472 |  7360 |     6   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| IND_OWNER |  1472 |  7360 |     6   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("OWNER"='SCOTT')Statistics----------------------------------------------------------          0  recursive calls          0  db block gets         13  consistent gets          0  physical reads          0  redo size       1653  bytes sent via SQL*Net to client        426  bytes received via SQL*Net from client         10  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        123  rows processedSQL>

对于none unique index来说，如果where 条件后面出现了=,>,<,betweed...and...的时候（返回多行值的时候），就有可能执行index range scan。

Performance:It depends ..How selective ? Throw-away later ? single block IO, CF

The problem is that it is impossible to tell how many rows the range scan is scanning. A range scan that scans 5 rows on average will probably be fairly efficient. However a range scan scanning 10000 rows on average will probably be causing grief.

INDEX SKIP SCAN

Reference:http://blog.csdn.net/robinson1988/article/details/4980611

http://www.orafaq.com/tuningguide/range%20scan.html

Oracle Index Access Methods