Skip to main content

Posts

Best Type of Collection for FORALL?

I recently received this question in my In Box:

Is FORALL faster with Associative Arrays or Nested Tables? Oracle 12.2 documentation says: "The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause." And a blog post claims Associative Arrays with "indices of" option is fastest in 10.2. Just wondering if you have noticed any differences and if so, how much faster Associative Arrays are in 12.2 than Nested Tables? Quick AnswerThere is no significant difference I can see in the performance based on different collection types (with the limited tests I have run).Don't trust performance tests run on very old versions of Oracle Database (e.g,, 10.2).Use the documentation as a starting, not ending, point of your exploration.Try it yourself! Writing and running the code will teach you more than reading the doc or my blog post. If you would like to read further, I will show you …
Recent posts

Make the Most of PL/SQL Bulk Processing

The bulk processing features of PL/SQL (BULK COLLECT and FORALL) are key tools for improving performance of programs that currently rely on row-by-row processing, an example of which is shown below.

Use this blog post to quickly get to some of the best resources on bulk processing - from articles to quizzes to workouts to tutorials.
LiveSQL Tutorial I offer a 19-module tutorial on all things bulk processing here. I complement the explanations with lots of code to run and explore, along with:
Fill in the Blanks: partially-written code that you need to finish up, that reinforces the content of that moduleExercises: You do all the coding to solve the stated requirement (be on the lookout for copy/paste opportunities from the module to speed things up). Oracle-BASE Content You can always depend on Tim Hall to offer comprehensive coverage of SQL and PL/SQL features, with straightforward, easy-to-run code snippets to drive the points home. You'll find his coverage of bulk processing her…

How to make sure your code FAILS to compile

Huh, what?

Make sure my code fails to compile?

Why would I want to do that.

Well, suppose that you had a compute-intensive procedure that ran every hour and benefited greatly from full PL/SQL compiler optimization (level set to 3, to take advantage of subprogram inlining and everything else it does).

Next, suppose that somehow as the procedure (newly enhanced, fully tested) was being deployed to production, the optimization level was mistakenly set to 0 or 1. This would cause severe performance problems.

So in that case, wouldn't it be nice if you could build a "circuit breaker" into that procedure so that the compiler says "No go" even if the code itself compiles just fine?

I think it would be nice - and you can accomplish precisely that with the error directive of the conditional compilation feature of PL/SQL.

First, here's the code that demonstrates precisely the scenario outlined above.
CREATE OR REPLACE PROCEDURE compute_intensive AUTHID DEFINER IS BE…

Use RETURNING Clause to Avoid Unnecessary SQL Statements

The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed, in order to obtain the values of the changed columns. So RETURNING helps avoid another roundtrip to the database, another context switch in a PL/SQL block.

The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form.

You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement.

Finally, you can also use RETURNING with EXECUTE IMMEDIATE (for dynamically constructed and executed SQL statements).

Run this LiveSQL script to see all of the statements shown below "in action."

First, I will create a table to use in my scripts:
CREATE TABLE parts ( part_number INTEGER , part_name VARCH…

Does the PL/SQL compiler remove code that is used?

Yes. No. Sort of.

 It's (not all that) complicated.

This question hit my Twitter feed yesterday:
When you enable all warnings, have you ever seen a "PLW-06006-- uncalled procedure removed" (lots of them), when they surely are called? Now that, I must admit, has to be a little bit concerning. You write code, you know it is going to, or should be, executed, and yet the PL/SQL compiler tells you it's been removed?

OK, OK, calm down. Everything is just fine.

Here's the explanation:
The optimizer performed an inlining optimization, so all the code for that procedure (or function) was moved to where it is invoked.The "original" nested or private subprogram that you wrote (and, don't worry, is still and always will be in the source code of your program unit) is, truth be told, never going to be called. So then the compiler removed it (did not include it in the compiled code - which is not PL/SQL code any longer). Let's take a look at some code, and what…

Setting and using your own conditional compilation flags

This post is the fourth in my series on conditional compilation. You will find links to the entire series at the bottom.

In this post, I explore how to set and use conditional compilation flags (also known as inquiry directives and referred to below as ccflags) used in $IF statements, and control which code will be included or excluded when compilation occurs.

In theory, you don't need ccflags at all. You could just create a package with static constants, like DBMS_DB_VERSION, and then reference those constants in $IF statements. That makes sense when many different compilation units (packages, procedures, triggers, functions, object types) need to be consistently controlled by the same settings. With the package approach, when you change a value for the constant, the dependent program units will be invalidated, and upon recompilation, will be compiled with the new values.

If, on the other hand, you want to add conditional compilation logic to a single unit, or a handful, then yo…

Writing code to support multiple versions of Oracle Database

3rd in a series on conditional compilation. See end of post for links to all posts in the series.

Do you write code that must run on more than one version of Oracle Database? This is almost always the case for suppliers of "off the shelf" applications. And when confronted with this reality, most developers choose between these two options:

Use only those features available in all versions ("lowest common denominator" or LCD programming).
or Maintain separate copies of the code for each supported version, so you can take advantage of new features in later versions of the database ("sure to create a mess" or SCAM programming).

And let's face it, both have some serious drawbacks.

The LCD approach ensures that your code will compile on all supported versions. But you will sacrifice the ability to take advantage of new features in the later versions. That can be a high price to pay.

The SCAM approach, well, "sure to create a mess" says it all. Wh…