| Team Fly | |
SESSIONS
TRANSACTIONS
Parallel execution can be applied to tables, views, and materialized views. Assuming all necessary configurations have been made, there are several ways to invoke parallel execution. The first way is during table creation (including materialized views), using the parallel clause. If the table is being created using the results of a subquery, the loading of the table will be parallelized. In addition, by default, all queries that are executed against the table will be parallelized to the same extent. The next listing shows an example of specifying the parallel option for a table creation.
1 create table commission ( 2 sales_rep_id number, 3 prod_id number, 4 comm_date date, 5 comm_amt number(10,2)) 6 tablespace comm_ts pctfree 5 initrans 1 maxtrans 255 7 parallel;
The import line here is Line 7, specifying the parallel clause. This line could also have included an integer to specify the degree of parallelism—that is, the number of processes that are to be used to execute the parallel process. As the degree of parallelism is omitted in this example, the number of processes used will be calculated as number of CPUs x the value of the PARALLEL_THREADS_PER_CPU initialization parameter. The degree of parallelism for a table or materialized view can be changed using an alter statement.
Parallel processing can also be invoked when the parallel hint is used in a select statement. This hint will override any default parallel processing options specified during table creation. The following listing illustrates the use of the parallel hint. Line 1 contains the parallel hint, specifying the table to be parallelized (commission) and the degree of parallelism (4).
1 select /*+ parallel (commission, 4) */ 2 prod_id, sum(comm_amt), count(*) 3 from commission 4 group by prod_id;
In some cases, Oracle Database 10g will alter how, or if, parallel processing is executed. Examples of these include the following:
| Team Fly | |