Retrieval Performance

The final, and in many ways the most important, event in the SQL processing cycle is the return of data to
the application that requested it. On one level, the speed of this final step is determined by your network,
since the amount of bits that can move from the server to the client is ultimately limited by the amount of
network bandwidth. The effects of this potential limitation cannot be overcome. But you can affect how
much work the Oracle database performs before it starts returning data to the user.

Well, how much work does Oracle have to do before it returns data? You would think the answer to this
question would be self-evident — “As much as it needs to do to get the data.” But how much data does
Oracle need to get before it starts to send data back to the application?

As developers, we tend to think of the end results of an action. Auser requests data, so he or she obviously
must want all of that data to do what he or she has to do. But users tend to be more, shall we say, immediate
in their outlook. For us, performance is the time it takes to complete an SQL operation, such as a query. For a user, performance is how long they wait before something comes back to them.
You can take advantage of this dichotomy by setting the way that Oracle returns data. You can specify
that Oracle should start returning rows to the user as soon as it gets the rows, or you can specify that
Oracle will only start returning rows to the user once it has collected all the rows for the query.
You instruct your Oracle database as to which approach to take by setting a parameter called OPTIMIZER_MODE.
The two settings for OPTIMIZER_MODE that are relevant to this example are ALL_ROWS and FIRST_ROWS, which tell Oracle to only return data once all rows have been fetched or as soon as it can, respectively. You can also use either one of these values as a hint for a particular query.
The best choice for this parameter obviously depends on your application. If a user is unable to do any
work until he or she receives all the data, or if you don’t want the user to do any work until he or she
receives all the data, the ALL_ROWS parameter is the right choice. In applications that typically fetch data
for the user to peruse and possible use, FIRST_ROWS may deliver better perceived performance without
much logical downside. If your application is not retrieving large amounts of data, this particular optimizer
choice shouldn’t really affect performance.

Regardless of the setting of this parameter, there are some times when Oracle will properly wait until it
has retrieved all rows until it returns any rows. One case is when a query includes aggregate values.
Oracle knows that it has to get all the rows before it can calculate aggregate values, so no rows will be
returned until all rows have been retrieved and the calculations performed.

Another case is when you ask for the rows to be returned in sorted order. Normally, Oracle cannot
return the first rows until the sort has been performed, since the sort determines what the first row is,
not the order that the rows are retrieved from the database. The exception to this rule is when the query
has requested a sort order that is already implemented in an index. For instance, a user may request
employee names in alphabetical order based on the last name, and there is an index that sorts the rows
on that criterion. The Oracle database knows that the index has already sorted the rows, so it does not
have to sort them and the first rows can be returned as soon as they are retrieved.