June 25, 2018
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • February 5, 2004
  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »
Conditional Table Lookup

A common (but from a design perspective, hideous) occurrence in databases is a single column representing several different attributes, each of which requires lookup to a different reference code table. Such systems are often the result of migration of a file-based database to a relational model. For example, a specification to print salary options might read:

List all persons in the staff table plus their salary calculated as follows:
  • A part-time worker salary is the FIXED_PRICE from the part_time_package table
  • A contractor salary is the HRS *HRLY_RATE from the contract_package table
  • A permanent salary is the ANNUAL_SAL +BONUS from the perm_package table

Within the staff table, the class_type column indicates whether a staff member is a part-time, contract, or permanent employee. Let's set up the required tables to investigate the options for a solution. First we will re-create the emp and dept tables with the sample data that comes with the database software distribution.

SQL> @$ORACLE_HOME/sqlplus/demo/demobld.sql
Building demonstration tables.Please wait.
Demonstration table build is complete.

Then create some additional tables required for the example.

SQL> create table STAFF (
  2  staffno number,
  3  name varchar2(30),
  4  class_type number,
  5  class_id number);

Table created.

SQL> create table part_time_package (
  2  id number,
  3  fixed_price number );

Table created.

SQL> create table contract_package (
  2  id number,
  3  hrly_rate number,
  4  hrs number );

Table created.

SQL> create table perm_package (
  2  id number,
  3  annual_sal number,
  4  bonus number );

Table created.

We will then seed these tables with sample data, once again using the src table to give us an arbitrary number of rows.

SQL> insert into part_time_package
  2  select rownum,rownum*130
  3  from SRC where rownum <6;

5 rows created.

SQL> insert into contract_package
  2  select rownum,rownum*10,rownum*30
  3  from SRC where rownum <6;

5 rows created.

SQL> insert into perm_package
  2  select rownum,rownum*10000,rownum*500
  3  from SRC where rownum <6;

5 rows created.

SQL> insert into staff
  2  select empno,ename,mod(rownum,3)+1,mod(rownum,5)+1 from emp;

14 rows created.

At first glance, a PL/SQL solution seems called for because the determination of which table to look up is driven by the data in another. A PL/SQL solution is presented as follows, the comments within it describe how we look up various tables dependent on the value of the staff member's CLASS_TYPE.

SQL> create or replace
  2  procedure SHOW_SAL is
  3    v_sal number;
  4  begin
  5    for i in (select *from staff )loop
  6    --
  7    --Part time employee so we need to look up
  8    --the PART_TIME_PACKAGE table
  9     --
 10       if i.class_type =1 then
 11         select fixed_price
 12         into   v_sal
 13         from   part_time_package
 14         where  id =i.class_id;
 15     --
 16     --For contractors,we need to look up
 17     --the CONTRACT_PACKAGE table
 18     --
 19        elsif i.class_type =2 then
 20          select hrs *hrly_rate
 21          into   v_sal
 22          from   contract_package
 23          where  id =i.class_id;
 24     --
 25     --For permanent employees,we need to look up
 26     --the PERM_PACKAGE table
 27     --
 28        elsif i.class_type =3 then --permanent
 29          select annual_sal +bonus
 30          into   v_sal
 31          from   perm_package
 32          where  id =i.class_id;
 33        end if;
 34      dbms_output.put_line(rpad(i.name,20)||lpad(v_sal,10));
 35     end loop;
 36   end;
 37 /

Procedure created.

Using this procedure, we can now display the salary options for each staff member, as outlined in the module specification.

SQL> exec show_sal;
ALLEN                    1200
WARD                    31500
MARTIN                    520
BLAKE                    7500
CLARK                   10500
SCOTT                     260
KING                     2700
TURNER                  42000
ADAMS                     650
JAMES                     300
FORD                    21000
MILLER                    390

PL/SQL procedure successfully completed.

A static SQL solution can be built even for this problem—in fact, it quite closely resembles the PL/SQL code and uses the CASE statement and the scalar subquery functionality available in 8i onward.

SQL> select s.name,
  2    case class_type
  3    when 1 then (
  4       select fixed_price
  5       from   part_time_package
  6       where  id =s.class_id )
  7    when 2 then (
  8       select hrs *hrly_rate
  9       from   contract_package
 10       where  id =s.class_id )
 11    when 3 then (
 12       select annual_sal +bonus
 13       from   perm_package
 14       where  id =s.class_id )
 15    end sal
 16 from staff s;

NAME                                  SAL
-------------------------------     -----
ALLEN                                1200
WARD                                31500
MARTIN                                520
BLAKE                                7500
CLARK                               10500
SCOTT                                 260
KING                                 2700
TURNER                              42000
ADAMS                                 650
JAMES                                 300
FORD                                21000
MILLER                                390
New Oracle 10g Features

The importance of staying up to date with the features available is no more apparent than in the many extensions to SQL that are available in version 10g. This raises the bar even higher for what can be achieved with pure SQL. Note in particular the new MODEL clause functionality that yields a new suite of facilities to probe and manipulate a SQL result set.

As a very basic example of what can be achieved with the new MODEL functionality, first we will create some sample data for a fictitious scenario in which we measure the probability that a lab animal produces a favorable result in a range of different experiments.

SQLSQL> create table SAMPLES (
  2     lab_id          varchar2(10),
  3     animal          varchar2(10),
  4     experiment_id   number,
  5     probability     number );

Table created.

SQLSQL> set feedback off
SQLSQL> insert into SAMPLES values ('OXFORD','RATS',1,0.993);
SQLSQL> insert into SAMPLES values ('OXFORD','RATS',2,0.93);
SQLSQL> insert into SAMPLES values ('OXFORD','RATS',3,0.91);
SQLSQL> insert into SAMPLES values ('OXFORD','MICE',1,0.91);
SQLSQL> insert into SAMPLES values ('OXFORD','MICE',2,0.99);
SQLSQL> insert into SAMPLES values ('OXFORD','MICE',3,0.90);
SQL> insert into SAMPLES values ('HARVARD','RATS',1,0.993);
SQL> insert into SAMPLES values ('HARVARD','RATS',2,0.93);
SQL> insert into SAMPLES values ('HARVARD','RATS',3,0.91);
SQL> insert into SAMPLES values ('HARVARD','MICE',1,0.91);
SQL> insert into SAMPLES values ('HARVARD','MICE',2,0.99);
SQL> insert into SAMPLES values ('HARVARD','MICE',3,0.90);
SQL> set feedback on

What is the probability that a given animal returns a favorable result across all tests? We need the product of each probability by lab and animal for experiments 1, 2, and 3. With Oracle 10g, this answer can be discovered with the MODEL clause.

SQL> SELECT lab_id,animal,experiment_id,s
  2  FROM samples
  4    DIMENSION BY (animal,experiment_id)
  5    MEASURES (probability s)IGNORE nav
  6    (s ['MICE',-1 ]=s ['MICE',1 ]*s ['MICE',2 ]*s ['MICE',3 ],
  7     s ['RATS',-1 ]=s ['RATS',1 ]*s ['RATS',2 ]*s ['RATS',3 ]);

------   --------   -------------   ---------
OXFORD   RATS                   1       .993
OXFORD   RATS                   2        .93
OXFORD   RATS                   3        .91
OXFORD   MICE                   1        .91
OXFORD   MICE                   2        .99
OXFORD   MICE                   3         .9
HARVARD  RATS                   1       .993
HARVARD  RATS                   2        .93
HARVARD  RATS                   3        .91
HARVARD  MICE                   1        .91
HARVARD  MICE                   2        .99
HARVARD  MICE                   3         .9
OXFORD   RATS                  -1   .8403759
OXFORD   MICE                  -1     .81081
HARVARD  RATS                  -1   .8403759
HARVARD  MICE                  -1     .81081

16 rows selected.

The rows with an experiment ID of .1 show the result required. Although a full discussion of the MODEL facilities in Oracle 10g is outside the scope of this book (and the example demonstrates only a tiny fraction of the flexibility offered by this new functionality), it further reduces the number of areas where PL/SQL is adopted because a SQL solution cannot be found.

Is a PL/SQL Solution Ever Justified?

If a SQL solution exists, is there ever a reason for using a slower PL/SQL equivalent? There is possibly one circumstance when this may be the case. Although processing a large DML operation is going to be more efficient with SQL than its PL/SQL equivalent, it also is an all-or-nothing process. If that SQL operation fails due to an unforeseeable error, the time taken to roll back the change may be unacceptable. (The reason for the emphasis on "unforeseeable" will be revealed shortly). If the DML operation is restartable, that is, it can logically be broken into discrete chunks of work, using PL/SQL can provide some insulation by turning an all-or-nothing operation into a smaller set of piecemeal tasks. Even in these cases, more often than not, the underlying SQL can also be broken up into similar small units of work thus obviating the need for PL/SQL. Possibly what is springing to most readers minds at this point is the common practice of using PL/SQL to avoid consuming excessive undo by issuing regular commits within a processing loop, but this is most definitely not a justification for using PL/SQL over SQL. Failing due to insufficient undo (or temporary) space is not an unforeseeable error and is catered to by either using the resumable transaction features available in version 9, or even better, configuring the appropriate amount of undo/temporary space for the task.

To summarize, if you are responsible for developing PL/SQL programs, always take the time to check out the latest SQL features in your Oracle version before leaping into PL/SQL code. A SQL solution will typically be faster and simpler to understand and maintain than a PL/SQL alternative. Remember (just like the PL/SQL manual says) it is an extension to SQL, not an alternative.


What we've tried to stress throughout this chapter is that building PL/SQL applications that are efficient is not difficult to do. This is indeed one of the great beauties of PL/SQL—the coding actions that are typically simplest are usually also the optimal way to code your applications.

It is only when you try to fight against these native features that PL/SQL goes bad. The number of occasions that you need to take a convoluted or complex approach to delivering functionality within PL/SQL is far lower than you might think, especially if you stay informed and up to date on the PL/SQL features that become available with each new release of the database.

Whichever PL/SQL feature you are taking advantage of, it is vital that you test and re-test the efficiency of anything you build. Being able to prove the performance and scalability of a PL/SQL program is just as important as the performance and scalability itself. There are many programming environments around that make it very difficult to track where efficiency problems are, and developers are forced to build their own sophisticated instrumentation facilities. With the simple timing and tracing facilities we have taken advantage of within this chapter, however, and the more advanced debugging APIs that are used by various third party PL/SQL development tools, there is no excuse for not taking to time to ensure that your PL/SQL code is as efficient as possible.


1. I would like to acknowledge OakTable member, Jonathan Lewis, for providing the motivation and background for this example.

2. For details about how to use the tracing facilities and the trace formatter tool Tkprof, consult Chapter 10 of the Performance Tuning Guide and Reference in the standard Oracle documentation set.

3. We've retained the use of the src table in upcoming sections for those readers using version 8 (who will not be able to take advantage of pipeline functions).

This sample chapter is from:
Mastering Oracle PL/SQL: Practical Solutions
Authors: Christoper Beck, Joel Kallman, Chaim Katz, David C. Knox, and Connor McDonald
ISBN: 1-59059-217-4
Published by: Apress

Reprinted with permission

Page 19 of 19

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.


We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date