> 作者：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查看数据库的选择执行路径的情况，也可以有很多方法来解决，再众多方法中，我们需要找出更高效的方法。

ITPub博客

【SQL】Oracle数据库sql优化小技巧索引不管用怎么办01

最新文章