图解：千万级记录的海量数据表使用Hints优化----从3分钟优化到几十毫秒

问题描述：

下面分析一个案例，千万级记录的海量数据表使用Hints优化----从3分钟优化到40ms。

为了说明这个过程，我构造了一个具有千万级记录的海量数据表Emp2，具有16777422条雇员的信息。

统计结果如图所示：

需要执行一个简单的等值查询：

如图所示：

从执行结果看，花费了大约3分钟才得到查询结果。

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     161483  consistent gets
     138827  physical reads
          0  redo size
       1098  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

这个查询仅仅获得了12行记录。换句话说，在这个千万级记录的大表中，仅仅存在12条记录满足查询条件。那这个查询需要执行3分钟，是无法容忍的。

需要对这个查询进行优化。

问题分析：

通过对SQL的执行计划分析，首先需要消除在查询过程中，对Emp2表进行全表扫描（FTS，Full Table Scan）。因为在千万级记录中，符合条件的记录仅仅有12条，这个可选择性是非常高的。

经过检查，在Emp2表的Department_id列上创建了索引：IDX_EMP2_DEPTID，但是为什么在上述查询时，没有使用到该索引？如果在查询中能够使用到该索引，是不是就能使得查询速度快一些呢？

于是，最直接的判断就是Oracle的SQL 优化器使用了错误的执行计划，使用hints提示来让优化器强制使用Emp2表中department_id列上的索引IDX_EMP2_DEPTID。

优化过程：

首先以SYS用户连接测试服务器，执行一些清理任务：

加入hints提示，修改需要执行的SQL语句为：

SQL>    select /*+ index(e IDX_EMP2_DEPTID) */
  2     e.employee_id,
  3     e.last_name,
  4     e.first_name,
  5     e.salary,
  6     d.department_id,
  7     d.department_name
  8      from  emp2 e,departments d
  9     where  e.department_id=d.department_id
 10     and e.department_id=30;

发现执行时间变为了：已用时间:  00: 00: 00.40

如图所示：

统计数据为：

通过上述图解，完成了对该SQL语句的优化。