Oracle first_rows optimization improvements

Prior to Oracle9i, the only two cost-based optimizer modes were all_rows and first_rows optimization. One of the shortcomings of traditional first_rows SQL optimization was that the first_rows goal did not know the scope of the query and generally favored index access over full-table scans.

In sum, the first_rows optimization algorithm did not limit the scope of the row retrieval, and assume that the query would desire all of the returned rows. The first-N rows optimization instructs the query optimizer to choose a query execution plan that minimizes the response time to produce the first N rows of query results. Starting in Oracle9i, we see the introduction of several new optimizer directives.

• FIRST_ROWS_1
• FIRST_ROWS_10
• FIRST_ROWS_100
• FIRST_ROWS_1000

This new Oracle9i cost-based optimizer (CBO) mode can be set at several levels in our database, either system-wide, at the session level, or at the query level:

alter system set optimizer_mode = first_rows_100;

alter session set optimizer_mode = first_rows_100;

select /*+ first_rows(100) */ from student;

Oracle Corporation states that with first_rows_n optimization, Oracle queries give the best possible response time for the first rows of a result set. Quicker response with the first N rows enhances user satisfaction in many applications, since users receive their initial set of data more rapidly.

By "tipping-off" the Oracle Cost-base optimizer (CBO) that the query will only return a small set of rows, the CBO can use this cardinality information to determine the optimal table join order for multi-table queries.

Everyone knows that all_rows optimization favors full-table scans while first_rows optimization favors index usage, but Oracle has extended this concept with first_rows_n optimization. In traditional first_rows optimization, The Oracle cost-based optimizer will favor an index scan, even if it has a higher overall cost than a full-table scan. This is especially true in the case of a smaller table where a full-table scan is not too expensive.

While we expect the CBO to favor indexes, but it is surprising to see that first_rows optimization chose a more expensive path than the full-table scan. This is a critical point. Prior to Oracle9i, first_rows optimization was a mix of internal rules and costs, while the Oracle9i first_rows optimization is completely cost-based.

Prior to Oracle9i, the Oracle DBA would use the optimizer_index_cost_adj, optimizer_index_caching and optimizer_mode parameters to control the propensity of the cost-based optimizer to choose an index over a full-table scan.

While Oracle claims that first_rows_n optimization results in faster response time for certain queries, we must remember that the performance is achieved via a change to the costing. The Oracle9i Cost-based Optimizer only costs the first rows accessed in the cost of the query and ignores the costs of other rows within the table.

Conclusion:

To recap, all the first_rows_n mode does is allow you to tell the optimizer how many rows you plan to use, thereby allowing the optimizer to make an intelligent execution plan. Since most Oracle9i systems will have many frequently-referenced tables cached in the KEEP pool, the first_rows_n parameter may only be helpful in reducing logical I/O, and not necessarily the more expensive disk I/O.

In sum, the first_rows_n optimization method will improve SQL execution plans for OLTP systems that only need to deliver the first part of a larger solution set.