公司系统中有个界面的查询显示比较慢，显示首页的速度需要20s以上

同事把查询发给我讨论下如何优化，查询大概是这样

select a,b,c,d,e,col1,col2,col3…..col50 from table_a where ….

关于这个sql，有以下几点：

1 a,b,c,d,e已经建立了索引

2 select的col1到col50都是属性列，属性比较多，数据量比较大

3 where后的条件比较多，不一一列出

4 table_a的数据量大概是170万，查询的结果的数据量是2万

因为数据库是10g，首先我使用了SQL Tuning Advisor看看Oracle有什么建议，结果SQL Tuning Advisor没有任何建议

然后看这个查询的执行计划，发现个问题：查询没有走索引

如果将查询修改为select a,b,c,d,e from table_a where …,将属性列删除，查询肯定是走索引的，索引应该没有什么问题

研究了半天，发现没有走索引的原因就在于数据库的OPTIMIZER_MODE是ALL_ROWS

Value	Description
ALL_ROWS	The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.
FIRST_ROWS_n	The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.
FIRST_ROWS	The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

如果OPTIMIZER_MODE是ALL_ROWS,SQL语句执行是不管统计的，而是要以最好的吞吐量为目地，吞吐量：使用最小的资源。这是一个默认值

如果OPTIMIZER_MODE是FIRST_ROWS，SQL语句的执行是以获取开始的行的速度性为目地的。

这里有两个注意点：

1 FIRST_ROWS有时意味着更大的COST

2 FIRST_ROWS是为了向后兼容，可以使用FIRST_ROWS_n代替

从我们系统的需求来看，

首先，修改系统级的OPTIMIZER_MODE是不可能的，因为系统比较大，不知道会出现什么问题

其次，修改Session级的OPTIMIZER_MODE也是不太可能的，因为一个Session可能处理很多的数据，修改可能会导致不可预知的后果

第三，由于界面上已经分页，用户对查询速度的体验以及转化为对首页的查询速度，为了改进用户的体验（原20s），必须让查询速度更快

第四，我们系统存在使用9i和10g，并没有使用到8i，所以可以使用FIRST_ROWS_n

考虑几点后，我加上了/*+ FIRST_ROWS */这个hint

select /*+ FIRST_ROWS */a,b,c,d,e,col1,col2,col3…..col50 from table_a where …
测试的结果如下：

1 执行计划上，加hint后走的是索引

2 执行速度上，加hint前平均20s，加hint后第一次4s，之后每次1秒内

3 执行成本上，加hint前是17000多，加hint后是14000多，相差并不是很大

ITPub博客

Hint - FIRST_ROWS