serial_to_parallel

gistfile1.sql

with v as(
     select--+ materialize
         rowid rid
     from z_line_items li
     where li.item_id = 42
)
select--+ leading(v li o) parallel(v 4)
    sum(li.price)
  from v
     , z_line_items li
     , z_orders o
 where li.rowid = v.rid
   and o.id = li.order_id
   and o.order_type != 0;

SQL Plan Monitoring Details (Plan Hash Value=3548249137)
=======================================================================================================================================================================================
| Id |              Operation               |            Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity | Activity Detail |
|    |                                      |                            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |   (# samples)   |
=======================================================================================================================================================================================
|  0 | SELECT STATEMENT                     |                            |         |      |         1 |     +1 |     1 |        1 |      |       |       |          |                 |
|  1 |   TEMP TABLE TRANSFORMATION          |                            |         |      |         1 |     +1 |     1 |        1 |      |       |       |          |                 |
|  2 |    LOAD AS SELECT                    |                            |         |      |         1 |     +0 |     1 |        1 |      |       |  536K |          |                 |
|  3 |     INDEX RANGE SCAN                 | ITEM_ID__Z_LINE_ITEMS      |      10 |    3 |         1 |     +0 |     1 |    99035 |      |       |       |          |                 |
|  4 |    SORT AGGREGATE                    |                            |       1 |      |         1 |     +1 |     1 |        1 |      |       |       |          |                 |
|  5 |     PX COORDINATOR                   |                            |         |      |         1 |     +1 |     3 |        2 |      |       |       |          |                 |
|  6 |      PX SEND QC (RANDOM)             | :TQ10000                   |       1 |      |         1 |     +1 |     2 |        2 |      |       |       |          |                 |
|  7 |       SORT AGGREGATE                 |                            |       1 |      |         1 |     +1 |     2 |        2 |      |       |       |          |                 |
|  8 |        NESTED LOOPS                  |                            |       9 |    8 |         1 |     +1 |     2 |    90281 |      |       |       |          |                 |
|  9 |         NESTED LOOPS                 |                            |      10 |    8 |         1 |     +1 |     2 |    99035 |      |       |       |          |                 |
| 10 |          NESTED LOOPS                |                            |      10 |    5 |         1 |     +1 |     2 |    99035 |      |       |       |          |                 |
| 11 |           VIEW                       |                            |      10 |    2 |         1 |     +1 |     2 |    99035 |      |       |       |          |                 |
| 12 |            PX BLOCK ITERATOR         |                            |      10 |    2 |         1 |     +1 |     2 |    99035 |      |       |       |          |                 |
| 13 |             TABLE ACCESS FULL        | SYS_TEMP_0FD9D69D7_AB10D34 |      10 |    2 |         1 |     +1 |    25 |    99035 |   27 |   2MB |       |          |                 |
| 14 |           TABLE ACCESS BY USER ROWID | Z_LINE_ITEMS               |       1 |    1 |         1 |     +1 | 99035 |    99035 |      |       |       |          |                 |
| 15 |          INDEX UNIQUE SCAN           | ID__Z_ORDERS               |       1 |      |         1 |     +1 | 99035 |    99035 |      |       |       |          |                 |
| 16 |         TABLE ACCESS BY INDEX ROWID  | Z_ORDERS                   |       1 |    1 |         1 |     +1 | 99035 |    90281 |      |       |       |          |                 |
=======================================================================================================================================================================================