List of operations that can be parallelized in Oracle

Oracle operations that can be parallelized / List of operations that can be parallelized in Oracle / Overview of operations that can be parallelized

Operations that can be parallelized in Oracle

1. Access methods – few access methods are full table scans, full index scans, partitioned index range scans.

Examples

Full table scans – the following SQL query scans the table for identifying the records which satisfy the given condition. Full table needs to be scanned in the case of select queries with conditions on non-key attributes, aggregate operations. SELECT * FROM Emp WHERE Ename = “Suresh”; Full index scans - all columns in the SELECT and WHERE clauses must exist in the index. In such case, full index scan is used. For example, the following SQL statement does not need to access the table rows, and needs to analyze the index alone, if we have an index on COLOR attribute. SELECT DISTINCT color, COUNT(*) FROM Product GROUP BY color; Partitioned index range scans - An index range scan is a common operation for accessing selective data. An index is a table where data are sorted.

2. Join methods – few join methods are nested loop joins, hash joins, and sort merge joins. These operations can be parallelized to increase the performance.

Nested loop joins - Nested loop joins are useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table. Hash joins - Hash joins are used for joining large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. Sort merge joins - Sort merge joins can be used to join rows from two independent sources. This join type can be preferred over hash join if the rows are already sorted.

3. DDL statements – few DDL statements are CREATE TABLE AS SELECT, CREATE INDEX, REBUILD INDEX, and REBUILD INDEX PARTITION

We can normally use parallel DDL where we use regular DDL. There are, however, some additional details to consider when designing your database. One important restriction is that parallel DDL cannot be used on tables with object or LOB columns. Parallel operations require accurate statistics to perform optimally.

4. DML statements – few are INSERT AS SELECT, updates, deletes, and MERGE operations

Parallel DML (parallel insert, update, merge, and delete) uses parallel execution mechanisms to speed up or scale up large DML operations against large database tables and indexes. You can also use INSERT ... SELECT statements to insert rows into multiple tables as part of a single DML statement. You can normally use parallel DML where you use regular DML. Example DML statement for inserting data: INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; [Note: The term parallel DML refers only to inserts, updates, upserts and deletes done in parallel.]

5. Parallel query - You can parallelize queries and sub-queries in SELECT statements, as well as the query portions of DDL statements and DML statements (INSERT, UPDATE, DELETE, and MERGE).

6. Other SQL operations that can be parallelized- GROUP BY, NOT IN, SELECT DISTINCT, UNION, UNION ALL, CUBE, and ROLLUP, as well as aggregate and table functions.

Source: Oracle Documentation