Oracle数据库中大型表查询优化研究

核心提示： （2）添加提示在Oracle中，我们可以在SQL语句中加入提示，Oracle数据库中大型表查询优化研究(4)，来影响Oracle对优化模式的选择，从而生成最优的执行计划，（3）如果表有多个索引，要保证Oracle正在使用对此SQL最优化的索引，于是我们在SQL语句中加入提示，强制优化器在生

（2）添加提示

在Oracle中，我们可以在SQL语句中加入提示，来影响Oracle对优化模式的选择，从而生成最优的执行计划。于是我们在SQL语句中加入提示，强制优化器在生成执行计划时将表的访问方式从全表扫描改为索引范围扫描。结果该查询花费了比全表扫描多得多的响应时间。仔细查看这个SQL语句的执行计划，我们发现Oracle对这个索引进行了全索引扫描，这样造成的物理读写数量比起全表扫描不仅没有减少，反而大幅上升。寻找原因，问题出在我们索引的字段上。我们索引的AGE字段为了测试方便，每条记录的值都不相同，这样的字段其实并不具备建立索引的条件。在该测试中，我们首先要将索引载入内存，由于索引各不相同，Oracle采取了全索引扫描的方式访问索引，我们要读取的是30万条记录（AGE＞200 k），通过索引，我们获得的将是30万个记录的ROWID，如果这些记录在磁盘上的物理位置没有按索引顺序排序，那么我们就不得不通过30万个ROWID来访问这些记录，这样造成的物理读写是相当惊人的，这就是为什么我们使用了索引，却反而查询速度更慢的原因所在。事实上Oracle的优化器选择全表访问是已经对不同的执行计划作过比较的了。同时，这里同样违背了一个Oracle优化的准则，对于没有按索引顺序排序的表，如果检索记录数小于总记录数的7％，用索引比全表扫描快。

（3）更改优化器模式

优化器是Oracle数据库中接受来自产生器处理过的SQL语句的程序。他将对SQL进行优化，生成内部执行计划，这个计划是Oracle存取物理磁盘数据的路径。根据优化器模式的不同，生成的内部执行计划也不同，而对于相同的SQL，永远只有一个最优的执行计划，因此选择合适的优化器模式，是很重要的。Oracle有2种优化器模式：基于规则的优化模式和基于成本的优化模式。在这次测试中，我们使用的优化器模式参数为CHOOSE，这样Oracle将根据SQL语句相关表索引的有无，统计资料的有无以及SQL语句中的提示，自动判断使用哪种优化模式。

（4）将调整持久化

Oracle的执行计划是根据各种情况，比如表的统计资料变化的，但有时这种变化是我们不希望的。为了将我们已经调整好的SQL执行计划固定，我们可以 用Oracle的工具将执行计划持久化存储。

2．4　调整结果

通过对这个测试用例的调整，我们最终将此大表的响应时间从5 s缩短到不足2 s。经过调整后的缓冲区高速缓存达到113 M，大约占物理内存的50％，调整后的SQL语句为：

　　SELECT name，age，memo FROM
　 （SELECTname，age，memo，rownum ASmynum FROM test2）
　　WHEREmynum＞400000 and mynum＜＝400010；

我们测试所用的机器配置为CELETRON500 M，内存256 M，硬盘为IDE8．4 G。可以说是运行Oracle8I的最低配置。启动Oracle 8I数据库后，内存使用达到300 M以上，也就是说已经在用虚拟内存；测试开始后，CPU保持或接近满载，这些都会影响Oracle的性能表现，也会影响测试结果。

3　结　语

以下几点可以作为通用的Oracle SQL调整原则：

（1）消除不必要的全表扫描，可以通过添加索引达到。

（2）缓存小型表的全表扫描，可以通过将小型表置入缓冲区高速缓存的KEEP池中实现。

（3）如果表有多个索引，要保证Oracle正在使用对此SQL最优化的索引，可以通过添加提示实现。