本文共 1223 字,大约阅读时间需要 4 分钟。
问题描述:
下面分析一个案例,千万级记录的海量数据表使用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语句的优化。