用合适的索引避免不必要的全表扫描

    Oracle 数据库里大部分SQL优化的问题都可以增加或减少索引的方式来解决，但这绝不是全部。当目标SQL语句所要查询的只是目标表中的一部分数据时，通过创建合 适的索引就能够避免在没有索引的情况下为查询这一小部分数据而不得不采用全表扫描的操作，这样就降低了目标SQL语句的资源消耗，同时也会缩短了执行时 间。

创建一张测试表及创建一个普通的单键值B树索引：

复制代码
复制代码
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index idx_t1 on t1(object_id);

Index created.
复制代码
复制代码

   清空缓存数据，我们看下一个SQL查询的执行计划及资源消耗：

复制代码
复制代码
SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from t1 where object_id is null;

no rows selected

Elapsed: 00:00:00.11

Execution Plan

Plan hash value: 3617692013

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 12 | 2484 | 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 12 | 2484 | 291 (1)| 00:00:04 |

Predicate Information (identified by operation id):

1 - filter(“OBJECT_ID” IS NULL)

Note

• dynamic sampling used for this statement (level=2)

Statistics

    308  recursive calls
      0  db block gets
   1151  consistent gets
 1038  physical reads
      0  redo size
   1183  bytes sent via SQL*Net to client
    405  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

复制代码
复制代码

  从上面的查询中可以看到，SQL语句查询走的全表扫描，有1151个一致性读，1038个物理读。

为什么没有走索引哪？

原来对于普通的单键值B树索引而言，NULL值不入索引的，所以即便在OBJECT_ID上有单键值B树索引IDX_T1,在执行上面的查询时也用不上。 对于上面的SQL有没有办法让其走索引那？如果你对Oracle数据库里B树索引的结构和原理都很了解的话就不难回答这个问题。

这里只需建立一个复合B树索引就可以了。

删除原来的IDX_T1索引，重新创建一个同名的复合B树索引IDX_T1，其前导列依然是object_id,但第二列是一个常数0，这里利用的原理是 —虽然对于单键值B树索引而言NULL值不入索引，但对于复合B树索引来说，NULL值是入索引的。

SQL> drop index idx_t1;

Index dropped.
SQL> create index idx_t1 on t1(object_id,0);

Index created

 重新执行同样的SQL查询：

复制代码
复制代码
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.11
SQL>
SQL> select * from t1 where object_id is null;

no rows selected

Elapsed: 00:00:00.01

Execution Plan

Plan hash value: 50753647

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 12 | 2484 | 97 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 12 | 2484 | 97 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 4314 | | 11 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access(“OBJECT_ID” IS NULL)

Note

• dynamic sampling used for this statement (level=2)

Statistics

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

复制代码
复制代码

     创建复合索引重新执行后可以看到Oracle这次查询就用到了B树复合索引IDX_T1，数据从全表扫描变成了索引范围扫描，数据耗费时间、一致性读和物理读也大幅度下降了。
 通过创建合适的索引来避免不必要的全表扫描，大幅降低了目标SQL 语句的资源消耗，进而大幅度的缩短了SQL的执行时间。