Using Subtypes

as published in Oracle Professional March 2003

When working batches of data, most of the time we try to create a main loop based on a cursor that retrieves as much data as we need with one fetch. Often times this means that we have to include a lot of tables in our cursor, which affects the overall performance. A solution would be to query data when we need it, and cache it until we need that same data again. In this article, Patrick Barel discusses how the use of subtypes can prove helpful in building the package when caching data in a collection.
SUPPOSE I want to fetch all the records from the EMP table and only fetch the DEPT records when I need them. If I’ve fetched a DEPT record, I store it in a collection, for example, a PL/SQL table, for easy access the next time I need it. Normally I’d create a record type to hold the data I want to cache and then create a collection based on this record type.
Review the following code snippet:

-- record type to hold the cached data
TYPE dept_rt IS RECORD(
  dname dept.dname%TYPE
);
-- table type to hold the cached data
TYPE dept_tt IS TABLE OF dept_rt
  INDEX BY BINARY_INTEGER;
-- package variable to hold the cached data
pv_dept dept_tt;

First, I create a record type with the fields I want to cache. Second, I create a collection (PL/SQL table) based on this record type. Third, I create a global variable to store the data. I fill up this collection based on my needs, using something like the function shown in Listing 1.

Listing 1. Private function to retrieve data from the collection or the database.

FUNCTION ppf_get_dept(
  pi_deptno                      IN      dept.deptno%TYPE )
  RETURN dept_rt
IS
  CURSOR c_dept(
    p_deptno                               dept.deptno%TYPE )
  IS
    SELECT dept.dname
      FROM dept
     WHERE dept.deptno = p_deptno;

  lv_returnvalue                           dept_rt;
BEGIN
  BEGIN
    lv_returnvalue   := pv_dept( pi_deptno );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
--== data is not yet in our collection ==--
--== go back to the database, ==--
--== and retrieve the needed data ==--
      IF c_dept%ISOPEN THEN
        CLOSE c_dept;
      END IF;

      OPEN c_dept( p_deptno => pi_deptno );
      FETCH c_dept INTO lv_returnvalue;

      IF c_dept%FOUND THEN
--== if the record is found, ==--
--== also add it to our collection ==--
        pv_dept( pi_deptno )   := lv_returnvalue;
      ELSE
--== raise some error for not finding the data ==--
        raise_application_error( -20001, 'Department not found' );
      END IF; -- c_dept%found

      IF c_dept%ISOPEN THEN
        CLOSE c_dept;
      END IF;
  END;

--== return the record, either from the ==--
--== collection or from the database ==--
  RETURN lv_returnvalue;
END ppf_get_dept;

This seems like a nice solution. But what if I want to cache more data from the table? Then I’d have to make changes to both the record definition and the cursor used in the function, as shown here:

TYPE dept_rt IS RECORD(
  dname                                    dept.dname%TYPE
, loc                                      dept.loc%TYPE --<-- added
);
...
CURSOR c_dept(
  p_deptno                               dept.deptno%TYPE )
IS
  SELECT dept.dname
       , dept.loc --<-- added
    FROM dept
   WHERE dept.deptno = p_deptno;

That’s not too much of a problem in the demo package, because you can easily eyeball the code to figure out where to make the changes, but if the definitions are far apart from each other, it would be easy to forget either one of the changes. The compiler will find that there’s an error, but it probably won’t pinpoint exactly what’s wrong.

Using Subtypes

The solution to this “problem” is to create a global cursor to retrieve the data from the database. Then create a type based on the %ROWTYPE of the cursor. To do this, I need to use the SUBTYPE declaration. Review the following code:

CURSOR c_dept(
  p_deptno                               dept.deptno%TYPE )
IS
  SELECT dept.dname
    FROM dept
   WHERE dept.deptno = p_deptno;

-- record type to hold the cached data
SUBTYPE dept_rt IS c_dept%ROWTYPE;

-- table type to hold the cached data
TYPE dept_tt IS TABLE OF dept_rt
  INDEX BY BINARY_INTEGER;

-- package variable to hold the cached data
pv_dept                                  dept_tt;

The code for the private function will be the same, except for the cursor declaration, since this is moved to the global section of the package body. Now, if I want to add another field to our collection, all I have to do is modify the cursor definition, as shown here:

CURSOR c_dept(
  p_deptno                               dept.deptno%TYPE )
IS
  SELECT dept.dname
       , dept.loc -- added
    FROM dept
   WHERE dept.deptno = p_deptno;

This way I make it easier for myself (and, of course, for others) to maintain the package.
Note that the scripts for the database packages using subtype are named subtype_demo.spk, subtype_demo2.spk, subtype_demo3.spk, and subtype_demo4.spk.

Subtype performance

If you were to time the difference between a normal cursor using a join between the EMP and DEPT tables and the version where the data from DEPT is cached into a collection, you’d find that the “cache” version is actually slower. There are, however, cases where this can come in quite handy. For example, say I’m handling two tables—one with subscriptions to a magazine and the other with relations of the subscribers. The subscription table consists of about 100,000 records, and the relations table consists of about 400,000 records. For the 100,000 subscriptions, we need about 50,000 different records from the relation table.
When I simply join the two tables, it takes quite some time to fetch all the records—something like 45 seconds. When I create a version using the caching mechanism, it only takes 15 seconds to fetch the same data, but most of the relation data is returned from my collection variable.

Other uses of subtype

Subtypes can also be used to create types based on the base types, but with precision. For example, if I want to write data to the screen, I can use DBMS_OUTPUT.PUT_LINE.
This procedure accepts character strings with a maximum length of 255. To be sure I don’t have to remember the maximum size it accepts, I can create a subtype output:

SUBTYPE output IS VARCHAR2(255);

Then, when writing my code I can create a variable based on this subtype and send that to DBMS_OUTPUT.PUT_LINE:

DECLARE
  SUBTYPE output IS VARCHAR2( 255 );

  lv_output                                output;
BEGIN
  lv_output   := 'This is subtype variable';
  DBMS_OUTPUT.put_line( lv_output );
END;

Another use of subtype could be that I want to make sure that a variable representing an amount of money will always have a scale of 20 and a precision of 2. Then I can create a money subtype:

SUBTYPE money IS NUMBER(20,2);

These are very trivial examples, but it can make the code more self-documenting. Consider the two functions shown in Listing 2.

Listing 2. The subtype_test package.

CREATE OR REPLACE PACKAGE subtype_test
IS
  SUBTYPE money IS NUMBER( 20, 2 );

  FUNCTION ADD(
    pi_amount                      IN      NUMBER
  , pi_add                         IN      NUMBER )
    RETURN NUMBER;

  FUNCTION add2(
    pi_amount                      IN      money
  , pi_add                         IN      money )
    RETURN money;
END subtype_test;
/
CREATE OR REPLACE PACKAGE BODY subtype_test
IS
  FUNCTION ADD(
    pi_amount                      IN      NUMBER
  , pi_add                         IN      NUMBER )
    RETURN NUMBER
  IS
  BEGIN
    RETURN( pi_amount + pi_add );
  END;

  FUNCTION add2(
    pi_amount                      IN      money
  , pi_add                         IN      money )
    RETURN money
  IS
  BEGIN
    RETURN( pi_amount + pi_add );
  END;
END subtype_test;
/

These functions perform exactly the same tasks; the only difference is their input and return type. When I call function add, I can send input of any number and it will return a value with the maximum precision. When I call function add2, I can send the same input parameters, but it will return a value with the precision specified in the subtype.

SET SERVEROUTPUT ON
DECLARE
  lv_test                                  NUMBER;
BEGIN
  lv_test   := subtype_test.ADD( 10.125, 2.2542 );
  DBMS_OUTPUT.put_line( 'number : ' || TO_CHAR( lv_test ));
  lv_test   := subtype_test.add2( 10.125, 2.2542 );
  DBMS_OUTPUT.put_line( 'money : ' || TO_CHAR( lv_test ));
END;
/

Here’s the output:

number : 12.3792
money : 12.38

The second function doesn’t need to have the variable rounded to two decimals to be an amount of money, since it’s implied in the subtype. Also, the second function tells me, just by looking at the specification, that it expects money input and that it produces money output.

The Oracle way...

Oracle uses a lot of subtypes in PL/SQL. If you take a look at the SYS.STANDARD package specification, you’ll find:

type NUMBER is NUMBER_BASE;

This is the base number type. From there on Oracle subtypes a lot of different numeric types like:

subtype FLOAT is NUMBER;
subtype REAL is FLOAT;
subtype "DOUBLE PRECISION" is FLOAT;
-- other number subtypes
subtype INTEGER is NUMBER(38,0);
subtype INT is INTEGER;
subtype SMALLINT is NUMBER(38,0);
subtype DECIMAL is NUMBER(38,0);
subtype NUMERIC is DECIMAL;
subtype DEC is DECIMAL;

As you can see, Oracle has one base number type (number_base) and subtypes all the other data types we know (and maybe even the ones we don’t know) from that base type. It does the same with character types, date types, and so on. Check out this package if you want to know more about the way Oracle works and what kind of data types you can use in your PL/SQL code. In this package you can also find the implementation of a lot of functions we use every day, like nvl and to_number, among others. For example, did you know the implementation of the USER function? (Yes, it’s a function!)

FUNCTION USER
  RETURN VARCHAR2
IS
  c                                        VARCHAR2( 255 );
BEGIN
  SELECT USER
    INTO c
    FROM SYS.DUAL;

  RETURN c;
END;

Limitations

Subtypes can be based on cursor%rowtype, table%rowtype, table.field%type, scalars, or inside a program based on a strong ref cursor. If you can publicly define the subtypes, you can use them anywhere in your programs, as long as you prefix the package name along with it.

Conclusion

Caching data can create a major increase in performance, because you won’t have to switch back and forth to the database. Using subtypes makes our packages easier to maintain and more transparent, but you should always check which is faster: just joining tables or caching the data from one table into a collection. If the version using the collection is just a little bit faster, then I suggest you go for that solution, since data tends to grow and you’ll find that the difference in performance will be bigger when using more data. Using subtypes can make the code more selfdocumenting and easier to maintain. If the precision for a variable changes, you just have to change it in one place.

Patrick Barel works for VDA informatiebeheersing bv in The Netherlands. He specializes in PL/SQL code for the applications built by VDA. He’s also one of the founders of www.oracledeveloper.nl. pbarel[at]vda.nl or patrick[at]bar-solutions.com.