0. 创建测试环境

-- Create table
drop table alex_t01 purge;
create table alex_t01 (id number, a number, b number, c number, name varchar2(100));

-- Create/Recreate primary, unique and foreign key constraints 
alter table alex_t01 add constraint pk_alex_t01 primary key (id) using index;
create index idx_alex_t01_id_ab on alex_t01 (a, b);
create index idx_alex_t01_id_c on alex_t01 (c);

-- Insert table
declare
begin
for i in 1..100000 loop
insert into alex_t01 values (i, mod(i,2), mod(i,20000), mod(i,20000), 'alex');
end loop;
commit;
end;
/

--Gather Statistics
exec dbms_stats.gather_table_stats('dbmgr','alex_t01')
exec dbms_stats.gather_index_stats('dbmgr','pk_alex_t01')
exec dbms_stats.gather_index_stats('dbmgr','idx_alex_t01_id_ab')
exec dbms_stats.gather_index_stats('dbmgr','idx_alex_t01_id_c')

1. 索引唯一扫描(Index Unique Scan)
通过唯一索引查找一个数值经常返回单个ROWID,如果该语句只返回一行,则存取方法称为索引唯一扫描。

select id, name from alex_t01 where id=400;
select /*+ index(alex_t01 pk_alex_t01) */ id, name from alex_t01 where id=400;
----------------------------------------------------------
Plan hash value: 3463029876
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01    |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ALEX_T01 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

2. 索引范围扫描( Index Range Scan )
使用一个索引存取多行数据称为索引范围扫描。

select id, name from alex_t01 where id<4;
select /*+ index_rs(alex_t01 pk_alex_t01) */ id, name from alex_t01 where id<4;
----------------------------------------------------------
Plan hash value: 3186038864
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_ALEX_T01 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

使用index rang scan的3种情况:
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)。
(b) 在组合索引上,只使用部分列进行查询,导致查询出多行。
(c) 对非唯一索引列上进行的任何查询。

3. 索引范围扫描( Index Range Scan DESC/ASC)

select id, name from alex_t01 where id<4 order by id desc;
select /*+ index_desc(alex_t01 pk_alex_t01) */ id, name from alex_t01 where id<4 order by id desc;
----------------------------------------------------------
Plan hash value: 597617196
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ALEX_T01    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| PK_ALEX_T01 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

4. 索引跳跃扫描( Index Skip Scan)

select a, b, name from alex_t01 where b=600;
select /*+ index_ss(alex_t01 idx_alex_t01_id_ab) */ a, b, name from alex_t01 where b=600;
----------------------------------------------------------
Plan hash value: 68523778
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     5 |    65 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01           |     5 |    65 |     8   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_ALEX_T01_ID_AB |     5 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

INDEX SKIP SCAN,发生在多个列建立的复合索引上,如果SQL中谓词条件只包含索引中的部分列,并且这些列不是建立索引时的第一列时,就可能发生INDEX SKIP SCAN。这里SKIP的意思是因为查询条件没有第一列或前面几列,被忽略了。
skip scan 让组合索引(composite index)逻辑的split 成几个子索引。如果在在查询时,第一个列没有指定,就跳过它。
建议将distinct 值小的列作为组合索引的引导列,即第一列。

5. 索引全扫描( Index Full Scan )

select * from alex_t01 order by id;
select /*+ index_fs(alex_t01 pk_alex_t01) */ * from alex_t01 order by id;
----------------------------------------------------------
Plan hash value: 4281092675
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   100K|  2050K|   560   (2)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T01    |   100K|  2050K|   560   (2)| 00:00:07 |
|   2 |   INDEX FULL SCAN           | PK_ALEX_T01 |   100K|       |   191   (2)| 00:00:03 |
-------------------------------------------------------------------------------------------

全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时,才进行全Oracle索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。

6. 索引快速全扫描( Index Fast Full Scan )

select a, b from alex_t01 where b>600;
select /*+ index_ffs(alex_t01 idx_alex_t01_id_ab) */ a, b from alex_t01 where b>600;
----------------------------------------------------------
Plan hash value: 2753535630
-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    | 97038 |   758K|    83   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_ALEX_T01_ID_AB | 97038 |   758K|    83   (4)| 00:00:01 |
-------------------------------------------------------------------------------------------

扫描索引中的所有的数据块,与 index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。

7. Index Combine

select * from alex_t01 where id>500 and b=600;
select /*+ index_combine(alex_t01 pk_alex_t01 idx_alex_t01_id_b) */ * from alex_t01 where id>500 and b=600;
----------------------------------------------------------
Plan hash value: 3016712838
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     5 |   105 |       |   677   (4)| 00:00:09 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | ALEX_T01          |     5 |   105 |       |   677   (4)| 00:00:09 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                   |       |       |       |            |          |
|   3 |    BITMAP AND                    |                   |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                   |       |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IDX_ALEX_T01_ID_C |       |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                   |       |       |       |            |          |
|   7 |      SORT ORDER BY               |                   |       |       |  1576K|            |          |
|*  8 |       INDEX RANGE SCAN           | PK_ALEX_T01       |       |       |       |   191   (2)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------

8. Index Join

select id, b from alex_t01 where id>500 and b>600;
select /*+ index_join(alex_t01 pk_alex_t01 idx_alex_t01_id_b) */ id, b from alex_t01 where id>500 and b>600;
----------------------------------------------------------
Plan hash value: 4090266087
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   | 96576 |   943K|   756   (2)| 00:00:10 |
|*  1 |  VIEW              | index$_join$_001  | 96576 |   943K|   756   (2)| 00:00:10 |
|*  2 |   HASH JOIN        |                   |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| PK_ALEX_T01       | 96576 |   943K|   675   (4)| 00:00:09 |
|*  4 |    INDEX RANGE SCAN| IDX_ALEX_T01_ID_C | 96576 |   943K|   887   (3)| 00:00:11 |
----------------------------------------------------------------------------------------
Trackback

no comment untill now

Add your comment now

切换到手机版