optimizer_mode参数

optimizer_mode是oracle 11g的一个优化器参数，在某些时候可以影响优化器的行为，是个不可忽视的细节参数。

---

SQL> show parameter optimizer;
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

此参数的简单介绍如下，

oracle优化器在解析sql前先查看这个参数的值。参数optimizer_mode 决定取出多少行后开始向用户返回第一批数据，当值为all_rows时，获取完所有的数据后再开始向用户返回数据。
这么看来，貌似感觉和执行计划没多大关系，但是oracle优化器在选择执行计划的时候却是会考虑这个参数的，而这里主要体现在btree索引的特性。

Let's make a test

我们来做一个测试。执行一个sql语句，从t1表中取出object_id列为1-3000的数据，结果以object_id列的值排序。测试情况如下。

我们可以看到，当前optimizer_mode的值为all_rows，优化器选择了全表扫描TABLE ACCESS FULL。那么我们一般的认识是，数据量总量较小或者查询量的总量占比较大的情况下，优化器认为全表扫描的性能较索引检索性能更优。

接下来修改当前会话的optimizer_mode的值为first_rows_10

然后再次测试刚才的SQL语句

呃，，我们发现，调整完这个参数后，优化器立马就选择了索引。

那么，怎么去解释呢？
这里就需要考虑到btree索引的特性了，因为索引的有序性，也就是获取到的数据是根据索引列排好序的。
因此，当进行全表扫描时，由于列值杂乱，所以必须等到所有的数据全部获取完毕后才能进行排序，再开始返回第一批数据；而通过索引返回的数据都是有序的，取到第十行时就可以直接返回给用户。
由于索引的这个小特性，加上optimizer_mode参数对数据操作的影响，间接地影响了优化器的选择行为。