Oracle PL/SQL for DBAs

Taking Advantage of Parallel Query

Parallelization helps all types of queries, whether or not they use table functions. Queries that do not use table functions take advantage of the extra throughput of PQ to assemble their final result set faster. Queries that do use table functions also benefit from the added throughput of PQ servers, but they take things a step further by assembling results sets along the way.

Let’s illustrate using the following table, which holds one record for every single transaction at a large bank:

    SQL> DESC acct_transactions
     Name                                      Null?    Type
     ----------------------------------------- -------- ------------
     AREA                                               VARCHAR2(10)
     TRX_DATE                                           DATE
     TRX_AMT                                            NUMBER

Suppose that I want to build a function to summarize the transactions by area. The first thing I considered implementing here was a query grouped by area. But that is negated by the need for a series of complex validation processes; for the sake of demonstration, I’ve hidden those in the function named super_complex_validation in the following examples.

Here are the results I’m looking for based on a demonstration data set, assuming that all transactions pass the complex validation.

    SQL> SELECT area,
      2         SUM(trx_amt)
      3    FROM acct_transactions
      4  GROUP BY area;

    AREA       SUM(TRX_AMT)
    ---------- ------------
    1                   460
    10                  550
    2                   470
    3                   480
    4                   490
    5                   500
    6                   510
    7                   520
    8                   530
    9                   540

To achieve this result faster with a large number of records, I decide to use parallel processing. With table functions, this means that Oracle will run multiple ...