高效的SQL（index range scan优化排序）
一、 实验表
表的结构、内容、索引(Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.)
doudou@TEST> desc range_scan
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
NAME VARCHAR2(20)
SUM VARCHAR2(20)
doudou@TEST> select * from range_scan;
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
2 doudou+2 3
3 doudou+3 3
4 doudou+4 3
5 doudou+5 4
6 doudou+6 4
7 doudou+7 4
8 doudou+8 5
9 doudou+9 5
10 doudou+10 6
11 doudou+11 6
12 doudou+12 6
11 rows selected.
doudou@TEST> select index_name,column_name,table_name from user_ind_columns where table_name='RANGE_SCAN';
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ ------------------------------ ------------------------------
INDEX_RANGE_SCAN_SUM SUM RANGE_SCAN
二、 开启执行计划，执行SQL
The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions（优化器使用一个范围扫描当找到一个或多个列的索引指定主要在条件。）
1、例如：sum=’5’； sum>'5'；sum '4' and sum
doudou@TEST> select * from range_scan where sum='5';
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
8 doudou+8 5
9 doudou+9 5
Execution Plan
----------------------------------------------------------
Plan hash value: 3049432069
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RANGE_SCAN | 2 | 74 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_RANGE_SCAN_SUM | 2 | | 1 (0)| 00:00:01 |
doudou@TEST> select * from range_scan where sum>'5';
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
10 doudou+10 6
11 doudou+11 6
12 doudou+12 6
Execution Plan
----------------------------------------------------------
Plan hash value: 3049432069
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 111 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RANGE_SCAN | 3 | 111 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_RANGE_SCAN_SUM | 3 | | 1 (0)| 00:00:01 |
doudou@TEST> select * from range_scan where sum;
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
2 doudou+2 3
3 doudou+3 3
4 doudou+4 3
5 doudou+5 4
6 doudou+6 4
7 doudou+7 4
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3049432069
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 222 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RANGE_SCAN | 6 | 222 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_RANGE_SCAN_SUM | 6 | | 1 (0)| 00:00:01 |
doudou@TEST> select * from range_scan where sum >'4' and sum;
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
8 doudou+8 5
9 doudou+9 5
Execution Plan
----------------------------------------------------------
Plan hash value: 3049432069
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RANGE_SCAN | 2 | 74 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_RANGE_SCAN_SUM | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
2、index range scan使用通配符需注意 ‘%5’像这样，通配符在前的不走范围索引；’5%’通配符在最后的正常走范围索引
doudou@TEST> select * from range_scan where sum like '%5';
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
8 doudou+8 5
9 doudou+9 5
Execution Plan
----------------------------------------------------------
Plan hash value: 1933872984
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| RANGE_SCAN | 2 | 74 | 3 (0)| 00:00:01 |
【使用通配符不恰当，导致不走索引（低效）】
doudou@TEST> select * from range_scan where sum like '5%';
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
8 doudou+8 5
9 doudou+9 5
Execution Plan
----------------------------------------------------------
Plan hash value: 3049432069
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RANGE_SCAN | 2 | 74 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_RANGE_SCAN_SUM | 2 | | 1 (0)| 00:00:01 |
【正确的使用通配符’5%’，（高效）】
3、当在索引列分组或排序的时候使用范围扫描，order by 索引列可以避免排序
doudou@TEST> select * from range_scan where sum='5' order by sum;
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
8 doudou+8 5
9 doudou+9 5
Execution Plan
----------------------------------------------------------
Plan hash value: 3049432069
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RANGE_SCAN | 2 | 74 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_RANGE_SCAN_SUM | 2 | | 1 (0)| 00:00:01 |
【索引列进行了order by，索引没有进行更多的sort order by(高效)】
doudou@TEST> select * from range_scan where sum='5' order by id ;
ID NAME SUM
---------- ---------------------------------------- ----------------------------------------
8 doudou+8 5
9 doudou+9 5
Execution Plan
----------------------------------------------------------
Plan hash value: 823340891
----------------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 2 | 74 | 3 (34)| 00:00:01
|
| 1 | SORT ORDER BY | | 2 | 74 | 3 (34)| 00:00:01
|
| 2 | TABLE ACCESS BY INDEX ROWID| RANGE_SCAN | 2 | 74 | 2 (0)| 00:00:01
|
|* 3 | INDEX RANGE SCAN | INDEX_RANGE_SCAN_SUM | 2 | | 1 (0)| 00:00:01
|
----------------------------------------------------------------------------------------------------
【order by id，id不是索引列，这里又进行了排序，浪费了cost（低效）】
三、 总结
1、index range scan 使用条件：(Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.)
2、index range scan使用通配符需注意： ‘%name’不恰当使用不走索引，’name%’正确使用走索引
3、index range scan优点：group by 或order by 使用索引列这样就避免了再次sort，提高了性能
附表：
1、实验数据库环境
sys@TEST> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_mode string ALL_ROWS
sys@TEST> select * from v$version;
BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 – Production
2、官档
13.5.3.3.1 When the Optimizer Uses Index Range Scans
The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:
- col1 = :b1
- col1 < :b1
- col1 > :b1
- AND combination of the preceding conditions for leading columns in the index
- col1 like 'ASD%' wild-card searches should not be in a leading position otherwise the condition col1 like '%ASD' does not result in a range scan.
Range scans can use unique or non-unique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-750465/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-750465/
1440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
