高效的SQL（index range scan優化排序）

高效的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

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

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

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

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;

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部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-750465/，如需轉載，請註明出處，否則將追究法律責任。

高效的SQL（index range scan優化排序）

相關文章