> 作者：Yunlong Yang
> 本節兩個小案例，一個是求最小值和最大值，另一個是找出空值，不走索引，如何提高查詢效率

#### 先看看求最大值和最小值

``` sql
SQL> select min(object_id),max(object_id) from t1;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
	     2		90852

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    13 |   347   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	  |	1 |    13 |	       |	  |
|   2 |   TABLE ACCESS FULL| T1   | 89081 |  1130K|   347   (1)| 00:00:05 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
       1247  consistent gets
	  0  physical reads
	  0  redo size
	613  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
```
如上例所示，求表t1的最大值和最小值，t1表object_id有索引，但是資料庫沒有選擇，最後選擇全表掃描，這樣效率比較慢。主要原因是執行計劃或者說是資料庫本身無法在索引掃描方式決定，無法同時從兩端開始掃描（或者說是獨立完成最大最小值掃描）。那麼我們可以分開求值，如下所示，4個邏輯讀即可，雖然拆分成兩個子查詢，但因為sql語句走索引，提高了效率：

``` sql
SQL> select (select min(object_id)  from t1) min_id,(select max(object_id) from t1) max_id from dual;

MIN_ID     MAX_ID
---------- ----------
	 2	90852

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1709123826

----------------------------------------------------------------------------------------
| Id  | Operation		   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |	       |     1 |       |     2	 (0)| 00:00:01 |
|   1 |  SORT AGGREGATE 	   |	       |     1 |    13 |	    |	       |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IDX_ID |     1 |    13 |     2	 (0)| 00:00:01 |
|   3 |  SORT AGGREGATE 	   |	       |     1 |    13 |	    |	       |
|   4 |   INDEX FULL SCAN (MIN/MAX)| T1_IDX_ID |     1 |    13 |     2	 (0)| 00:00:01 |
|   5 |  FAST DUAL		   |	       |     1 |       |     2	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  4  consistent gets
	  0  physical reads
	  0  redo size
	597  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

```

#### null值不走索引

t2表查詢object_id為null的，無法走索引，單列索引，我們都知道不儲存null值，那麼複合索引，如果符合索引中所有列均為null值，也不會儲存，索引該效果也不行，怎麼辦？ 先看看原始執行計劃：
``` sql
SQL> select object_id from t2 where object_id is null;

OBJECT_ID
----------

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     8 |   104 |   141   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T2	 |     8 |   104 |   141   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID" IS NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
	  6  recursive calls
	  0  db block gets
	517  consistent gets
	  0  physical reads
	  0  redo size
	587  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  4  rows processed
```

根據索引特性及複合索引，我們可以建立一個複合索引並儲存一個常量，這樣所有值都能存入。如下所示：

``` sql
--建立索引語句
SQL> create index t2_id_1 on t2(object_id,1);

Index created.

Elapsed: 00:00:00.32
--再次查詢，走該複合索引了，邏輯讀明顯減少
SQL> select object_id from t2 where object_id is null;

OBJECT_ID
----------

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2188508051

----------------------------------------------------------------------------
| Id  | Operation	 | Name    | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	   |	 8 |   104 |	14   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T2_ID_1 |	 8 |   104 |	14   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID" IS NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
	  5  recursive calls
	  0  db block gets
	 74  consistent gets
	  1  physical reads
	  0  redo size
	587  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  4  rows processed
```

有很多時候，我們建立了索引，資料庫不走，我們可以透過10053檢視資料庫的選擇執行路徑的情況，也可以有很多方法來解決，再眾多方法中，我們需要找出更高效的方法。

【SQL】Oracle資料庫sql最佳化小技巧索引不管用怎麼辦01

相關文章