高效的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<'5'and (sum> '4' and sum<'6')

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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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<'5';

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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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<'6';

ID NAME SUM

---------- ---------------------------------------- ----------------------------------------

8 doudou+8 5

9 doudou+9 5

Execution Plan

----------------------------------------------------------

Plan hash value: 3049432069

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

-

|

----------------------------------------------------------------------------------------------------

-

| 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博客

Oracle 优化之SQL（index range scan优化排序）

最新文章