Tuning Access to Lookup Tables

Overview

Querying a lookup table for values can be a very time consuming process. So, what is faster: loading a PL/SQL table into the memory and do a lookup on it or using a single select statement? This article shows how we can load a PL/SQL table and how to perform a lookup using a PL/SQL table. In addition, we show the single select solution.

We are simulating a typical data load, which includes reading some data, doing lookups, and loading into another table (target table).

Associative Arrays in PL/SQL

Associative array (new in Oracle9i Database Version 9.2.0) is the new name for index-by table (even earlier known as PL/SQL table). These have been available for some time, but before Version 9.2.0 the only possible declaration was...

type my_tab_t is table of number index by binary_integer;

Version 9.2.0 introduces new possibilities...

type my_tab_t is table of number index by pls_integer;
type my_tab_t is table of number index by varchar2(4000);
type my_tab_t is table of tab.value%TYPE index by tab.id%TYPE;

The size of the varchar2 may be anything up to the legal limit of 32767!
 A declaration other than with index by pls_integer or index by varchar2(n) still fails, thus...

PLS-00315: Implementation restriction: unsupported table index type

...so if, say, it is required to index by date, then a TO_CHAR conversion must be used.

The new ability for index by pls_integer removes the need to use the older binary_integer in any new coding exercise.

The new ability for index by varchar2 allows many new exciting coding possibilities, and is responsible for the name change for the feature to associative arrays, also in keeping with general terminology use when discussing 3GLs.

For example, index-by tables are often used to cache table values to give a performance boost in special situations, especially where very frequent lookup is called for. It has previously been necessary to code this explictly.

PL/SQL Table Lookup

That is the lookup table, implemented as an IOT - all values are in an index: a table of codes (id) for values. In a moment, you'll see how to load that into a PL/SQL index by table with which to perform lookups.

--
-- Oracle 8i has no Associative Arrays
--
CREATE TABLE dimension (
   id     PRIMARY KEY,
   value
)
ORGANIZATION INDEX
AS
SELECT object_id, object_name
  FROM all_objects
/
--
-- Oracle 9i / 10g has Associative Arrays
--
CREATE TABLE dimension (
   id     PRIMARY KEY,
   value
)
ORGANIZATION INDEX
AS
SELECT TO_CHAR(object_id), object_name
  FROM all_objects
/

Delete some rows, to get holes for NULL values.

DELETE FROM dimension WHERE id BETWEEN 3000 AND 5000;
COMMIT;

Here is the table of test data, the input data on which we want to do lookups:

CREATE TABLE facts
AS
   SELECT object_id, timestamp, status
   FROM bigtab
/

The Table bigtab contains 1'000'000 rows.

And finally the Target Table:

CREATE TABLE target (
   id             NUMBER,
   description    VARCHAR2(30))
/

We start with the PL/SQL lookup table approach. First, we have to load up the PL/SQL table type; Then, we iterate over the data to be processed, do the lookup, and insert it into the target table:

set timing on;

DECLARE
   -- Oracle 8i
   -- TYPE dimensionTableType IS TABLE OF
   -- dimension.value%TYPE INDEX BY
   -- BINARY_INTEGER;
   -- Oracle 9i / 10g (Associative Arrays)
   TYPE dimensionTableType IS TABLE OF
   dimension.value%TYPE INDEX BY
   dimension.id%TYPE;
   l_dim_tab dimensionTableType;
BEGIN
   --
   -- Load Values from Dimension Table
   -- into In-Memory PL/SQL Lookup Table
   --
   FOR i in (SELECT id,value FROM dimension)
   LOOP
      l_dim_tab(i.id) := i.value;
   END LOOP;
   --
   -- Lookup Value in Dimension Table for a given object_id
   -- and insert this value in target table
   --
   FOR x IN (SELECT object_id FROM facts)
   LOOP
      BEGIN
         INSERT INTO target (id, description)
            VALUES (x.object_id, l_dim_tab(x.object_id));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            INSERT INTO target (id, description)
               VALUES (x.object_id, NULL);
      END;
   END LOOP;
END;
/

PL/SQL procedure successfully completed.
Elapsed: 00:07:19.14

As you can see, it took a over 7 minutes on an Oracle 9.2.0.4 to do the lookup.
Can we do better? Definitely!

select count(*) from target;

  COUNT(*)
----------
   1000000

select count(*) from target where description is NULL;

  COUNT(*)
----------
    306792

Single Select Statement Lookup

truncate table target;

BEGIN
   INSERT INTO target
   SELECT f.object_id, d.value
   FROM facts f, dimension d
   WHERE f.object_id = d.id(+);
END;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:24.47

select count(*) from target;

  COUNT(*)
----------
   1000000

SQL> select count(*) from target where description is NULL;

  COUNT(*)
----------
    306792

This single insert does the same exact thing in a fraction of the time. Almost any time you can remove procedural code and do the same thing in a single SQL statement, you'll be better off. There's less code to type and maintain, and it's generally much faster, to boot. Note, the outer join (+), so NULL values are inserted as well.