CODEWITHSUNDEEP
sqloracle-databaseparallel-processingoracle-sqldeveloper
user11518995
user11518995

Reputation:

Why Does Running A Simple Query In Parallel Make It Faster?

If we look at a simple query like this one:

SELECT * FROM CUSTOMER2;

We can tell by looking at it that it simply does one thing, retrieves everything from CUSTOMER2.

Now my question is, why is it that when we run it like this:

SELECT/*+ PARALLEL(CUSTOMER2, 8) */ * FROM CUSTOMER2;

The cost of it (according to execution plan) goes from 581 to 81? Since its only one task, isn't it just performed on the same thread anyway?

I can understand if there were two full table scans needing to be done as you can run those two in parallel threads so they execute at the same time. But in our case, there is only one full table scan.

So how does running it in parallel make it faster when there is nothing to run it "in parallel" with?

Lastly, when I altered my personal cluster and the one table to run in parallel when anything is performed on it I did not see any change in cost like I did with the small statement.

This is my personal one:

SELECT AVG(s.sellprice), s.qty, s.custid   
FROM CUSTOMER_saracl c, sale_saracl s 
WHERE c.custid = s.custid  
GROUP BY (s.qty,  s.custid)  
HAVING AVG(s.sellprice) >  
  (SELECT MIN(AVG(price))    
   FROM  product_saracl    
   WHERE pname  
   LIKE 'FA%'   
GROUP BY price);

Why would that be?

Thank you for any help, I just today learnt about parallel execution so go easy on me haha!

Upvotes: 4

Views: 1813

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One very important point about relational databases is that tables represent unordered sets. That means that the pages that are scanned for a table can be scanned in any order.

Oracle actually takes advantage of this for parallel scans of a single table. There is additional overhead to bring the results back together, which is why the estimated cost is 81 and not 73 (581 / 8).

I think this documentation has good examples that explain this. Some are quite close to your query.

Note that parallelism does not just apply to reading tables. In fact, it is more commonly associated with other operations, such as joins, aggregation, and sorting.

Upvotes: 1

Related Questions