Can PL/SQL be Faster than SQL?

Comments 0

Share to social media

PL/SQL and SQL both are incredibly powerful languages that allow us to do some pretty amazing things with data. If you’ve been around the Oracle community for some time you’ve probably realized, either through your own experience or by reading articles, if you are able to accomplish a task using a single SQL statement then that is preferred over using procedural PL/SQL code that would run slower. I fully endorse this approach and both use and reiterate it daily to my teams. Here are but a few of the benefits of this brand of thinking:

  1. It forces developers to think in “sets”. The Oracle database works incredibly efficiently with sets of data and learning how to harness that power will benefit developers and customers alike.
  2. It promotes an exploration and understanding of the huge wealth of SQL functions available in Oracle.
  3. It allows developers to get creative with their solutions while at the same time writing elegant and simple code that is easily understood and maintained.

So, should we always use SQL over PL/SQL if the SQL language permits us to do so? As you can probably guess, there are situations where using PL/SQL can be the better option. Such as in life, in technology the only guarantee is that there are no guarantees.

The following is an example of a real-world scenario that I have encountered a few times already in my career and expect to see a few times more before I hang up the keyboard and mouse for a set of golf clubs.

Consider the following two tables:

Some important facts about these tables:

  • There is a primary key on the EMPLOYEE_ID column of the EMPLOYEES table
  • There are no keys or indexes on the NEW_SALARY_INFO table.
  • The NEW_SALARY_INFO table is in 3rd party vendor system that you cannot change as it would result in the loss of support from the vendor
  • The EMPLOYEES table and NEW_SALARY_INFO tables are “big”, having 1,000,000 rows and approximately 4,000,000 rows respectively
  • Not all EMPLOYEE_ID values in the EMPLOYEES table exist in the NEW_SALARY_INFO table

You have been tasked with updating the employee salaries in the EMPLOYEES table with the maximum salary for the same employee, based on EMPLOYEE_ID, in the NEW_SALARY_INFO table. If no record exists in the NEW_SALARY_INFO table for an employee, that employee’s data should remain the same.

At first glance, this seems relatively simple. We’ll just use an UPDATE statement to modify the rows in the EMPLOYEES table:

That should do it. Let’s have a look at the Explain Plan for this statement.

Hmm, that doesn’t look very promising at all. Look at the estimated cost and time. It makes sense though. There is no index on the NEW_SALARY_INFO table so the database will have to full scan a four million row table for each of the one million employee records to find the maximum salary.

Well, SQL is pretty awesome, so there must be another way to do this. Maybe we can update a view?

Again, let’s have a look at the Explain Plan prior to executing this statement.

Once again we’ve got a very high estimated cost and time, for the same reason as the previous update. Surely we are not out of SQL options yet? No, we’re not. Let’s try a MERGE statement.

But when I try to view the Explain Plan for the MERGE, I receive the following error

Curses! Foiled again! It turns out there is a VPD security policy on the EMPLOYEES table that prevents us from using the MERGE. So what now? All of our attempts at using SQL have been stymied.

PL/SQL to the rescue! By making use of bulk processing in PL/SQL we can perform our task rather quickly and with minimal code. The Oracle bulk collect tells the SQL engine to fetch multiple rows at once and store them in a collection defined by the developer. With the addition of the use of the FORALL statement, this greatly reduces the amount of context switching between PL/SQL and SQL by sending DML statements from PL/SQL to SQL in batches rather than one at a time, resulting in dramatic performance improvements over row-by-row processing.

The first thing we’ll need to do is write a SQL query that returns all of the data we need to update every row in the EMPLOYEES table that has a matching record(s) in the the NEW_SALARY_INFO table, keeping in mind that we need the maximum salary. This is rather straightforward.

That’s it. That gives us exactly what we are looking for. Now we can turn that query into a PL/SQL cursor, process the results in bulk, and perform the required update using a FORALL statement. Incidentally, the above query returns 999,000 rows.

In approximately 30 seconds we’ve updated nearly one million rows with data from a four million row table. I’d say that’s pretty good, if you asked me.

So why does this work so well? The first reason has already been noted, and that is the use of bulk operations and FORALL to reduce context switching between PL/SQL and SQL. The second is how the cursor query is executed. The optimizer no longer has to worry about performing an update as part of the query and is able to simply join the two tables in a very efficient manner. We’ve gone from a series of SQL update statements that would either have taken hours to complete or produced an Oracle error, to a small PL/SQL block that does exactly we need in a very reasonable amount of time.

To satisfy our curiosity however, let’s see how the PL/SQL block stacks up against the UPDATE and MERGE statements if we were allowed to add indexes to the NEW_SALARY_INFO table and if the EMPLOYEES table did not have a security policy against it.

Now let’s look at the respective plans of our previous UPDATE and MERGE statements.

Those look much better. The optimizer is making use of the new index when it should, and the MERGE statement is now permissible. The following table summarizes the average of 10 executions of each of the UPDATE statements, the MERGE statement, and the PL/SQL block. The results are rather interesting:

Update Update View Merge PL/SQL
Average time (seconds) over 10 executions 56.753 40.924 27.013 31.108

The PL/SQL block out-performed both of the updates and was nearly on par with the MERGE statement, even in an ideal world.

What does this teach us? I think it is a reminder that we should be both aware of and willing to explore all of our options as developers. PL/SQL has the ability to work very efficiently for you if you understand what is available within the language. It could be your best option.

About the author

Chris Hritzuk's contributions
Chris Hritzuk's latest contributions: