总共只有两种扫描方式：全表扫描和索引扫描。
索引扫描还分几种：索引范围扫描、索引唯一扫描、索引全扫描、索引跳跃扫描、索引快速全扫描。
先创建数据用于测试：
CREATE TABLE TB_USER
(
ID INTEGER NOT NULL,
USER_NAME VARCHAR2(20) NOT NULL,
USER_AGE INTEGER NOT NULL
);
CREATE SEQUENCE SEQ_USER
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW
BEGIN
SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
DECLARE
V_AGE TB_USER.USER_AGE%TYPE;
V_NAME TB_USER.USER_NAME%TYPE;
BEGIN
FOR I IN 1..100000 LOOP
SELECT DBMS_RANDOM.value(1, 100) INTO V_AGE FROM DUAL;
V_NAME := 'USER_' || V_AGE;
INSERT INTO TB_USER(USER_NAME, USER_AGE) VALUES (V_NAME, V_AGE);
END LOOP;
COMMIT;
END;全表扫描(TABLE ACCESS FULL)
oracle官方解释：http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i44851
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. The high water mark indicates the amount of used space, or space that had been formatted to receive data. Each row is examined to determine whether it satisfies the statement's WHERE clause.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameterDB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.
执行计划：
SQL> set autotrace traceonly;
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_user where id=5000;
执行计划
----------------------------------------------------------
Plan hash value: 1576953371
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 72 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_USER | 3 | 114 | 72 (5)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=5000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
382 recursive calls
0 db block gets
422 consistent gets
319 physical reads
0 redo size
530 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>从执行计划我们可以看到所采用的查询方式是“TABLE ACCESS FULL”，这就是全表扫描。也正是因为采用全表扫描，所以consistent gets、physical reads会很大。
索引唯一扫描(INDEX UNIQUE SCAN)
官方解释：http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i44986
This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.
《Oracle SQL高级编程》相关知识如图：
现在增加PRIMARY KEY约束，并查看执行计划：
SQL> alter table tb_user add constraint pk_id primary key(id);
表已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_user where id=5000;
执行计划
----------------------------------------------------------
Plan hash value: 2780460684
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:
00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_USER | 1 | 38 | 2 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ID | 1 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=5000)
统计信息
----------------------------------------------------------
368 recursive calls
0 db block gets
71 consistent gets
21 physical reads
0 redo size
438 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>从执行计划我们可以看到所采用的查询方式是“TABLE ACCESS BY INDEX ROWID”。consistent gets、physical reads明显减小很多。
现在改为UNIQUE约束：
SQL> alter table tb_user drop constraint pk_id;
表已更改。
SQL> alter table tb_user add constraint unique_id primary key(id);
表已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL>
SQL> select * from tb_user where id=5000;
执行计划
----------------------------------------------------------
Plan hash value: 4155757343
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_USER | 1 | 38 | 2 (0)| 0
0:00:01 |
|* 2 | INDEX UNIQUE SCAN | UNIQUE_ID | 1 | | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=5000)
统计信息
----------------------------------------------------------
388 recursive calls
0 db block gets
73 consistent gets
21 physical reads
0 redo size
438 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
索引范围扫描(INDEX RANGE SCAN)
An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.
《Oracle SQL高级编程》相关知识如图：
此时已经有了PRIMARY KEY约束。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_user where id between 5000 and 6000;
已选择1001行。
执行计划
----------------------------------------------------------
Plan hash value: 3314415347
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1020 | 15300 | 7 (0)| 00:
00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_USER | 1020 | 15300 | 7 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | PK_ID | 1020 | | 4 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=5000 AND "ID"<=6000)
统计信息
----------------------------------------------------------
368 recursive calls
0 db block gets
212 consistent gets
37 physical reads
0 redo size
29069 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1001 rows processed
SQL>
like 'abc%'之类的不适合索引范围扫描
SQL> create index idx_name on tb_user(user_name);
索引已创建。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_user where user_name like 'USER_5%';
已选择11146行。
执行计划
----------------------------------------------------------
Plan hash value: 1576953371
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 1475K| 74 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_USER | 100K| 1475K| 74 (7)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("USER_NAME" LIKE 'USER_5%')
统计信息
----------------------------------------------------------
433 recursive calls
0 db block gets
1135 consistent gets
320 physical reads
0 redo size
284388 bytes sent via SQL*Net to client
8558 bytes received via SQL*Net from client
745 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
11146 rows processed
SQL>是全表扫描，而不是索引范围扫描。
索引范围扫描降序(INDEX RANGE SCAN DESCENDING)
官方解释：http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i45191
An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. Usually, this scan is used when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value.
可以使用索引范围扫描来避免排序
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> set autotrace on
SQL> select * from tb_user where id in (5000, 6000) order by id desc;
ID USER_NAME USER_AGE
---------- -------------------- ----------
6000 USER_72 72
5000 USER_85 85
执行计划
----------------------------------------------------------
Plan hash value: 965284013
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 2 | 30 | 4 (0)| 0
0:00:01 |
| 1 | INLIST ITERATOR | | | | |
|
| 2 | TABLE ACCESS BY INDEX ROWID | TB_USER | 2 | 30 | 4 (0)| 0
0:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| PK_ID | 2 | | 3 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=5000 OR "ID"=6000)
filter("ID"=5000 OR "ID"=6000)
统计信息
----------------------------------------------------------
425 recursive calls
0 db block gets
89 consistent gets
34 physical reads
0 redo size
587 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
索引全扫描(INDEX FULL SCAN)
官方解释：http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i82107
A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:
-
All of the columns in the table referenced in the query are included in the index.
-
At least one of the index columns is not null.
A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.
《Oracle SQL高级编程》相关知识如图：
SQL> create index idx_id on tb_user(id);
索引已创建。
SQL> create index idx_name_age on tb_user(user_name, user_age);
索引已创建。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select id, user_name, user_age from tb_user where user_age=50;
已选择1043行。
执行计划
----------------------------------------------------------
Plan hash value: 1576953371
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1007 | 15105 | 74 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_USER | 1007 | 15105 | 74 (7)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("USER_AGE"=50)
统计信息
----------------------------------------------------------
405 recursive calls
0 db block gets
459 consistent gets
319 physical reads
0 redo size
19641 bytes sent via SQL*Net to client
1144 bytes received via SQL*Net from client
71 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1043 rows processed
SQL>竟然没有走索引，那就使用INDEX提示来强制走索引：
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /*+index(tb_user idx_name_age)*/ id, user_name, user_age from tb_user where user_age=50;
已选择1043行。
执行计划
----------------------------------------------------------
Plan hash value: 3192379470
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1007 | 15105 | 588 (1)
| 00:00:08 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_USER | 1007 | 15105 | 588 (1)
| 00:00:08 |
|* 2 | INDEX FULL SCAN | IDX_NAME_AGE | 1007 | | 312 (2)
| 00:00:04 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("USER_AGE"=50)
filter("USER_AGE"=50)
统计信息
----------------------------------------------------------
425 recursive calls
0 db block gets
790 consistent gets
653 physical reads
0 redo size
31317 bytes sent via SQL*Net to client
1144 bytes received via SQL*Net from client
71 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1043 rows processed
SQL>看来优化器还是对的，强制走索引，还不如全表扫描。
通过USER ROWID访问表(TABLE ACCESS BY USER ROWID)
The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.
To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes. Oracle then locates each selected row in the table based on its rowid.
SQL> set autotrace off
SQL> select rowid from tb_user where rownum=1;
ROWID
------------------
AAAM7qAAEAAAAH1AAA
SQL> set autotrace traceonly
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_user where rowid = chartorowid('AAAM7qAAEAAAAH1AAA');
执行计划
----------------------------------------------------------
Plan hash value: 3665941029
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 15 | 1 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY USER ROWID| TB_USER | 1 | 15 | 1 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
统计信息
----------------------------------------------------------
425 recursive calls
0 db block gets
82 consistent gets
20 physical reads
0 redo size
530 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
索引快速全扫描(INDEX FAST FULL SCAN)
官方解释：http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i52044
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
创建索引：
SQL> create index idx_name on tb_user(user_name);
索引已创建。
SQL> create index idx_age on tb_user(user_age);
索引已创建。
USER_NAME列：
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select user_name from tb_user;
已选择100000行。
执行计划
----------------------------------------------------------
Plan hash value: 1371489473
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 101K| 794K| 61 (4)| 00:00:01
|
| 1 | INDEX FAST FULL SCAN| IDX_NAME | 101K| 794K| 61 (4)| 00:00:01
|
--------------------------------------------------------------------------------
-
统计信息
----------------------------------------------------------
421 recursive calls
0 db block gets
7010 consistent gets
275 physical reads
0 redo size
1347806 bytes sent via SQL*Net to client
73711 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>USER_AGE列：
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select user_age from tb_user;
已选择100000行。
执行计划
----------------------------------------------------------
Plan hash value: 3154918777
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 298K| 46 (5)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_AGE | 101K| 298K| 46 (5)| 00:00:01 |
--------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
429 recursive calls
0 db block gets
6959 consistent gets
209 physical reads
0 redo size
1347313 bytes sent via SQL*Net to client
73711 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>不使用组合索引
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select user_age, user_name from tb_user;
已选择100000行。
执行计划
----------------------------------------------------------
Plan hash value: 1576953371
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 1092K| 73 (6)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TB_USER | 101K| 1092K| 73 (6)| 00:00:01 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
438 recursive calls
0 db block gets
7047 consistent gets
320 physical reads
0 redo size
2058955 bytes sent via SQL*Net to client
73711 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>此时变成了全表扫描。
使用组合索引
SQL> create index idx_age_name on tb_user(user_age, user_name);
索引已创建。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select user_age, user_name from tb_user;
已选择100000行。
执行计划
----------------------------------------------------------
Plan hash value: 2980266026
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 101K| 1092K| 71 (3)| 00:00
:01 |
| 1 | INDEX FAST FULL SCAN| IDX_AGE_NAME | 101K| 1092K| 71 (3)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
统计信息
----------------------------------------------------------
432 recursive calls
0 db block gets
7053 consistent gets
312 physical reads
0 redo size
1348164 bytes sent via SQL*Net to client
73711 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>去除NULL约束
SQL> alter table tb_user modify (user_age null);
表已更改。
SQL> alter table tb_user modify (user_name null);
表已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select user_age, user_name from tb_user;
已选择100000行。
执行计划
----------------------------------------------------------
Plan hash value: 1576953371
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 1092K| 73 (6)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TB_USER | 101K| 1092K| 73 (6)| 00:00:01 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
486 recursive calls
0 db block gets
7051 consistent gets
321 physical reads
0 redo size
2058955 bytes sent via SQL*Net to client
73711 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>至少一列存在NULL约束
SQL> alter table tb_user modify (user_age not null);
表已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select user_age, user_name from tb_user;
已选择100000行。
执行计划
----------------------------------------------------------
Plan hash value: 2980266026
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 101K| 1092K| 71 (3)| 00:00
:01 |
| 1 | INDEX FAST FULL SCAN| IDX_AGE_NAME | 101K| 1092K| 71 (3)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
统计信息
----------------------------------------------------------
506 recursive calls
0 db block gets
7064 consistent gets
322 physical reads
0 redo size
1348164 bytes sent via SQL*Net to client
73711 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>
索引跳跃扫描(INDEX SKIP SCAN)
官方解释：http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i51571
Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
Consider, for example, a table employees (sex, employee_id, address) with a composite index on (sex, employee_id). Splitting this composite index would result in two logical subindexes, one for M and one for F.
For this example, suppose you have the following index data:
('F',98)
('F',100)
('F',102)
('F',104)
('M',101)
('M',103)
('M',105)
The index is split logically into the following two subindexes:
-
The first subindex has the keys with the value
F. -
The second subindex has the keys with the value
M.
The column sex is skipped in the following query:
SELECT * FROM employees WHERE employee_id = 101;
A complete scan of the index is not performed, but the subindex with the value F is searched first, followed by a search of the subindex with the value M.
创建数据：
CREATE TABLE TB_PERSON
(
PERSON_ID INTEGER NOT NULL,
SEX VARCHAR2(1) NOT NULL
);
BEGIN
FOR I IN 1..5000 LOOP
INSERT INTO TB_PERSON (PERSON_ID, SEX) VALUES (I, 'F');
END LOOP;
FOR I IN 5001..10000 LOOP
INSERT INTO TB_PERSON (PERSON_ID, SEX) VALUES (I, 'M');
END LOOP;
COMMIT;
END;创建索引，并看执行计划：
SQL> create index idx_person on tb_person(sex, person_id);
索引已创建。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_person where person_id = 10000;
执行计划
----------------------------------------------------------
Plan hash value: 804511024
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_PERSON | 1 | 15 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PERSON_ID"=10000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
384 recursive calls
0 db block gets
108 consistent gets
31 physical reads
0 redo size
464 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>看到是全表扫描。
使用DBMS_STATS.GATHER_TABLE_STATS：
SQL> exec dbms_stats.gather_table_stats(user, 'tb_person');
PL/SQL 过程已成功完成。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_person where person_id = 10000;
执行计划
----------------------------------------------------------
Plan hash value: 867282108
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_PERSON | 1 | 5 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PERSON_ID"=10000)
filter("PERSON_ID"=10000)
统计信息
----------------------------------------------------------
330 recursive calls
0 db block gets
61 consistent gets
34 physical reads
0 redo size
464 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>看到的确是索引跳跃扫描。
现在使用full提示：
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /*+full(tb_person)*/ * from tb_person where person_id = 10000;
执行计划
----------------------------------------------------------
Plan hash value: 804511024
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_PERSON | 1 | 5 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PERSON_ID"=10000)
统计信息
----------------------------------------------------------
350 recursive calls
0 db block gets
82 consistent gets
30 physical reads
0 redo size
464 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>看到全表扫描的确慢一些。
现在改变组合索引里的列的顺序：
SQL> drop index idx_person;
索引已删除。
SQL> create index idx_person on tb_person(person_id, sex);
索引已创建。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_person where person_id = 10000;
执行计划
----------------------------------------------------------
Plan hash value: 4010638092
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_PERSON | 1 | 5 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PERSON_ID"=10000)
统计信息
----------------------------------------------------------
751 recursive calls
0 db block gets
131 consistent gets
20 physical reads
0 redo size
464 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>看到使用的是索引范围扫描。
换回来：
SQL> drop index idx_person;
索引已删除。
SQL> create index idx_person on tb_person(sex, person_id);
索引已创建。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from tb_person where person_id = 10000;
执行计划
----------------------------------------------------------
Plan hash value: 867282108
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_PERSON | 1 | 5 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PERSON_ID"=10000)
filter("PERSON_ID"=10000)
统计信息
----------------------------------------------------------
330 recursive calls
0 db block gets
61 consistent gets
18 physical reads
0 redo size
464 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>所以，Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少，后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。
5401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
