128 Oracle Database 11g DBA Handbook
For example, a batch operation may combine data from multiple tables, perform joins and
sorts, and then insert the result into a table. On a small scale, this may perform satisfactorily. On
a large scale, you may have to divide this operation into multiple steps:
1. Create a work table. Insert rows into it from one of the source tables for the query,
selecting only those rows and columns that you care about later in the process.
2. Create a second work table for the columns and rows from the second table.
3. Create any needed indexes on the work tables. Note that all the steps to this point can be
parallelized??”the inserts, the queries of the source tables, and the creation of the indexes.
4. Perform the join, again parallelized. The join output may go into another work table.
5. Perform any sorts needed. Sort as little data as possible.
6. Insert the data into the target table.
Why go through all these steps? Because you can tune them individually, you may be able
to tune them to complete much faster individually than Oracle can complete them as a single
command. For batch operations, you should consider making the steps as simple as possible.
You will need to manage the space allocated for the work tables, but this approach can generate
significant benefits to your batch-processing performance.
Eliminate Unnecessary Sorts
As part of the example in the preceding section, the sort operation was performed last.
Pages:
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234