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
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.
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.
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.]