Oracle

A colleague asked me some questions about FIRST_ROWS and ALL_ROWS, but I'm hesitant to blog about it because it's already been done so well by others -- the best example would probably be Sachin Arora.

Nevertheless, it never hurts to lend another voice to the Oracle choir, so here's everything I know on the topic.

FIRST_ROWS and ALL_ROWS are values for the optimizer setting OPTIMIZER_MODE. You can set it in your init.ora file, or you can set it on a session-by-session basis (using alter session). You can also set it on a query-by-query basis by using hints (although such hints may be ignored in certain cases, like where all rows must be accessed, as in a GROUP BY).

Your basic choices for OPTIMIZER_MODE include CHOOSE by default, RULE, ALL_ROWS, FIRST_ROWS and its variant FIRST_ROWS_N (where N is 1, 10, 100 or 1000). So far I'm not telling you anything that wouldn't be obvious with a glance at the documentation, but let me explain a little bit about the latter two.

ALL_ROWS and FIRST_ROWS have a lot in common. They're the only two settings that force the use of the CBO (Cost-Based Optimizer), and therefore they're collectively referred to as cost-based, since the concept of cost is directly related to the CBO.

If your statistics are up to date -- which they should be!! -- then the CHOOSE option will generally use the CBO as well. Possibly always, but I'd rather not use the word "always" unless I were certain. If you turn tracing on, you can see the optimizer setting that was used, and if you see "Cost=" then you know the CBO came into play.

The most important thing to keep in mind when using CHOOSE or a cost-based optimizer setting is that your statistics are up to date, otherwise it might default to RULE (if it's set to the default value of CHOOSE), or will use default statistics.

"The CBO without stats is like a morning without coffee. A really bad idea."
- Tom Kyte (though I'm sure Jon Emmons would agree).

The difference between FIRST_ROWS and ALL_ROWS is exactly what you'd think - the former instructs the CBO to get the first rows as quickly as possible even if the overall query takes longer, whereas the latter simply gets you all the data as quickly as possible.

As you would probably guess, FIRST_ROWS will likely be better in an interactive client/server situation where there is a benefit to getting some of the results before the rest. FIRST_ROWS is also found to use index scans instead of full table scans more often than otherwise - but for more detail on its peculiarities, check Sachin's article or search the Oracle forums.

As far as I know, the only real difference between ALL_ROWS and CHOOSE is that ALL_ROWS will use the CBO regardless of whether you have up-to-date statistics. Basically ALL_ROWS doesn't make as much sense to me, because if you care about performance to the point where you're modifying optimizer settings, then you probably care enough to have up-to-date statistics, in which case you'd leave it on the default value of CHOOSE.