Sample Chapter: Efficient PL/SQL
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 3 SPREADSHEET PARTITION BY (lab_id) 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 ]); LAB_ID ANIMAL EXPERIMENT_ID S ------ -------- ------------- --------- 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.Conclusion
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.
Endnotes
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
This article was originally published on February 5, 2004