In general,
sort operations are inappropriate for OLTP applications. Sort operations do not return any rows
to the user until the entire set of rows is sorted. Row operations, on the other hand, return rows to
the user as soon as those rows are available.
Consider the following simple test: Perform a full table scan of a large table. As soon as the
query starts to execute, the first rows are displayed. Now, perform the same full table scan but add
an order by clause on an unindexed column. No rows will be displayed until all the rows have
been sorted. Why does this happen? Because for the second query Oracle performs a SORT
ORDER BY operation on the results of the full table scan. Because it is a set operation, the set
must be completed before the next operation is performed.
Now, imagine an application in which there are many queries executed within a procedure.
Each of the queries has an order by clause. This turns into a series of nested sorts??”no operation
can start until the one before it completes.
Note that union operations perform sorts. If it is appropriate for the business logic, use a union
all operation in place of a union, because a union all does not perform a sort.
NOTE
A union all operation does not eliminate duplicate rows from the
result set, so it may generate more rows??”and therefore different
results??”than a union.
Eliminate the Need to Query Undo Segments
When performing a query, Oracle will need to maintain a read-consistent image of the rows
queried.
Pages:
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235