Using Parallel Execution

https://docs.oracle.com/cd/B10501_01/server.920/a96520/tuningpe.htm

Types of Parallelism

The following types of parallelism are discussed in this section:

• Parallel Query
• Parallel DDL
• Parallel DML
• Parallel Execution of Functions
• Other Types of Parallelism

Example 21-4 Parallelizing INSERT ... SELECT

Add the new employees who were hired after the acquisition of ACME.

INSERT /*+ PARALLEL(EMP) */ INTO employees
SELECT /*+ PARALLEL(ACME_EMP) */ * 
FROM ACME_EMP;

Example 1 Parallelizing UPDATE and DELETE

Give a 10 percent salary raise to all clerks in Dallas.

UPDATE /*+ PARALLEL(EMP) */ employees
SET SAL=SAL * 1.1
  WHERE JOB='CLERK' AND DEPTNO IN
  (SELECT DEPTNO FROM DEPT WHERE LOCATION='DALLAS');

The PARALLEL hint is applied to the UPDATE operation as well as to the scan.

Example 2 Parallelizing UPDATE and DELETE

Remove all products in the grocery category because the grocery business line was recently spun off into a separate company.

DELETE /*+ PARALLEL(PRODUCTS) */ FROM PRODUCTS
WHERE PRODUCT_CATEGORY ='GROCERY';

Again, the parallelism is applied to the scan as well as UPDATE operation on table employees.

Incremental Data Loading in Parallel

Parallel DML combined with the updatable join views facility provides an efficient solution for refreshing the tables of a data warehouse system. To refresh tables is to update them with the differential data generated from the OLTP production system.

In the following example, assume that you want to refresh a table named customer that has columns c_key, c_name, and c_addr. The differential data contains either new rows or rows that have been updated since the last refresh of the data warehouse. In this example, the updated data is shipped from the production system to the data warehouse system by means of ASCII files. These files must be loaded into a temporary table, named diff_customer, before starting the refresh process. You can use SQL*Loader with both the parallel and direct options to efficiently perform this task. You can use the APPEND hint when loading in parallel as well.

Once diff_customer is loaded, the refresh process can be started. It can be performed in two phases or by merging in parallel, as demonstrated in the following:

• Updating the Table in Parallel
• Inserting the New Rows into the Table in Parallel
• Merging in Parallel

Updating the Table in Parallel

The following statement is a straightforward SQL implementation of the update using subqueries:

UPDATE customers
SET(c_name, c_addr) = 
  (SELECT c_name, c_addr
   FROM diff_customer
   WHERE diff_customer.c_key = customer.c_key)
   WHERE c_key IN(SELECT c_key FROM diff_customer);

Unfortunately, the two subqueries in this statement affect performance.

An alternative is to rewrite this query using updatable join views. To do this, you must first add a primary key constraint to the diff_customer table to ensure that the modified columns map to a key-preserved table:

CREATE UNIQUE INDEX diff_pkey_ind ON diff_customer(c_key)
  PARALLEL NOLOGGING;
ALTER TABLE diff_customer ADD PRIMARY KEY (c_key);

You can then update the customers table with the following SQL statement:

UPDATE /*+ PARALLEL(cust_joinview) */
(SELECT /*+ PARALLEL(customers) PARALLEL(diff_customer) */
CUSTOMER.c_name AS c_name
CUSTOMER.c_addr AS c_addr,
diff_customer.c_name AS c_newname, diff_customer.c_addr AS c_newaddr
   WHERE customers.c_key = diff_customer.c_key) cust_joinview
   SET c_name = c_newname, c_addr = c_newaddr;

The base scans feeding the join view cust_joinview are done in parallel. You can then parallelize the update to further improve performance, but only if the customer table is partitioned.

Inserting the New Rows into the Table in Parallel

The last phase of the refresh process consists of inserting the new rows from the diff_customer temporary table to the customer table. Unlike the update case, you cannot avoid having a subquery in the INSERT statement:

INSERT /*+PARALLEL(customers)*/ INTO customers
SELECT * FROM diff_customer
s);

However, you can guarantee that the subquery is transformed into an anti-hash join by using the HASH_AJ hint. Doing so enables you to use parallel INSERT to execute the preceding statement efficiently. Parallel INSERT is applicable even if the table is not partitioned.

Merging in Parallel

In Oracle9i, you combine the previous updates and inserts into one statement, commonly known as a merge. The following statement achieves the same result as all of the statements in "Updating the Table in Parallel" and "Inserting the New Rows into the Table in Parallel":

MERGE INTO customers USING diff_customer
ON (diff_customer.c_key = customer.c_key)
WHEN MATCHED THEN
  UPDATE SET (c_name, c_addr) = (SELECT c_name, c_addr 
  FROM diff_customer
  WHERE diff_customer.c_key = customers.c_key) 
WHEN NOT MATCHED THEN
  INSERT VALUES (diff_customer.c_key,diff_customer.c_data);

FIRST_ROWS(n) Hint

Starting with Oracle9i, a hint called FIRST_ROWS(n), where n is a positive integer was added. This hint enables the optimizer to use a new optimization mode to optimize the query to return n rows in the shortest amount of time. Oracle Corporation recommends that you use this new hint in place of the old FIRST_ROWS hint for online queries because the new optimization mode may improve the response time compared to the old optimization mode.

Use the FIRST_ROWS(n) hint in cases where you want the first n number of rows in the shortest possible time. For example, to obtain the first 10 rows in the shortest possible time, use the hint as follows:
SELECT /*+ FIRST_ROWS(10) */ article_id
FROM articles_tab
WHERE CONTAINS(article, 'Oracle')>0
ORDER BY pub_date DESC;