What is Oracle Parallel Query?

Oracle Parallel Query (formerly Oracle Parallel Query Option or PQO) allows one to break-up a given SQL statement so that its parts can run simultaneously on different processors in a multi-processor machine. Typical operations that can run in parallel are: full table scans, sorts, sub-queries, data loading etc.

he foundation of Oracle Real Application Clusters revolves around parallelism. The original name for RAC was OPS, for Oracle Parallel Server. With RAC, it is possible for an intra-parallel operation to utilize the processors across the nodes, using the second argument in the Oracle PARALLEL hint.  This gives an additional degree of parallelism while executing in parallel.

Oracle Parallel Query in a distributed environment:

In a distributed environment, pieces of a table may reside on many remote servers. You can access all of the remote rows in a single query, using inter-instance parallel execution in a distributed architecture where local customer tables are kept on each instance. Below example executes query from the north_carolina instance, accessing two remote instances in-parallel. The north_carolina instance drives the distributed parallel query and it is the north_carolina instance that must gather and sort the result set.

select customer_name, sum(purchase_amount) from sales union select customer_name, sum(purchase_amount) from sales@san_francisco union select customer_name, sum(purchase_amount) from sales@new_york group by customer_name;