Deep Dive — ASSOCIATIVE ARRAY

A hash map in session memory. Load a lookup once, ask it 500 million times, never touch the SQL engine for it again.

Used in
Chapter 4 — In-Memory Lookup
Appendix entry
ASSOCIATIVE ARRAY
Related
COLLECTION METHODS NOCOPY BULK COLLECT RESULT_CACHE

The associative array (INDEX BY table) is PL/SQL's key → value structure: keyed by PLS_INTEGER or VARCHAR2, sparse, unbounded, no initialization, no EXTEND. Access by key is O(1) for integer keys and O(log n)-ish for string keys. It lives in the session's PGA.

Use it as the cache for a lookup table that a loop would otherwise query per row. Load it once from SQL, then EXISTS(key) / arr(key) in the loop. Not for: persisting data (it cannot be stored in a column), passing to SQL (only with a package-declared type, 12c+), or datasets that do not fit comfortably in one session's memory.

The other two collection types — nested table and varray — are for SQL interop and column storage; for in-memory lookups the associative array wins.

SQL 
TYPE <t> IS TABLE OF <element_type> INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2(<n>) | <col>%TYPE };
<var> <t>;                     -- ready to use, empty

<var>(<key>) := <value>;       -- insert or overwrite
<value> := <var>(<key>);       -- NO_DATA_FOUND if the key is absent
IF <var>.EXISTS(<key>) ...     -- safe test

<element_type> can be a scalar, a record (%ROWTYPE or user-defined), or another collection (two-level maps).

SQL 
DECLARE
  TYPE t_tier_map IS TABLE OF customers.tier%TYPE INDEX BY PLS_INTEGER;
  l_tier t_tier_map;
BEGIN
  FOR r IN (SELECT customer_id, tier FROM customers WHERE tier IN ('VIP', 'GOLD')) LOOP
    l_tier(r.customer_id) := r.tier;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(l_tier.COUNT || ' customers cached');

  FOR r IN (SELECT order_id, customer_id, amount FROM orders WHERE status = 'NEW') LOOP
    IF l_tier.EXISTS(r.customer_id) THEN
      apply_discount(r.order_id, r.amount, l_tier(r.customer_id));
    END IF;
  END LOOP;
END;

SQL 
DECLARE
  TYPE t_desc_map IS TABLE OF status_codes.description%TYPE INDEX BY status_codes.code%TYPE;
  l_desc t_desc_map;
BEGIN
  FOR r IN (SELECT code, description FROM status_codes) LOOP
    l_desc(r.code) := r.description;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(l_desc('NEW'));      -- 'Awaiting processing'
END;

String keys are compared and ordered by NLS_SORT/NLS_COMP; with BINARY (the default) that is byte order and exact match. INDEX BY VARCHAR2(4000) is the usual declaration when the key width is unknown.

SQL 
DECLARE
  TYPE t_cust_map IS TABLE OF customers%ROWTYPE INDEX BY PLS_INTEGER;
  l_cust t_cust_map;
  r_cust customers%ROWTYPE;
BEGIN
  FOR r IN (SELECT * FROM customers WHERE tier = 'VIP') LOOP
    l_cust(r.customer_id) := r;
  END LOOP;
  r_cust := l_cust(:id);
  DBMS_OUTPUT.PUT_LINE(r_cust.status || ' / ' || r_cust.last_order_date);
END;

BULK COLLECT always fills indexes 1..n. If you want the real key as the subscript, re-key in a second pass:

SQL 
DECLARE
  TYPE t_row IS RECORD (customer_id customers.customer_id%TYPE, tier customers.tier%TYPE);
  TYPE t_rows IS TABLE OF t_row INDEX BY PLS_INTEGER;
  l_dense t_rows;        -- 1..n
  l_byid  t_rows;        -- customer_id → row
BEGIN
  SELECT customer_id, tier BULK COLLECT INTO l_dense FROM customers WHERE tier = 'VIP';
  FOR i IN 1 .. l_dense.COUNT LOOP
    l_byid(l_dense(i).customer_id) := l_dense(i);
  END LOOP;
  l_dense.DELETE;        -- release the temporary copy
END;

The cursor-FOR-loop load in 3a does the same in one pass (100-row prefetch) — simpler and just as fast for a lookup-sized table.

SQL 
DECLARE
  TYPE t_month_totals IS TABLE OF NUMBER INDEX BY VARCHAR2(7);      -- 'YYYY-MM' → amount
  TYPE t_cust_months  IS TABLE OF t_month_totals INDEX BY PLS_INTEGER;
  l_totals t_cust_months;
  l_key    VARCHAR2(7);
BEGIN
  FOR r IN (SELECT customer_id, TO_CHAR(order_date, 'YYYY-MM') AS ym, amount
            FROM   orders WHERE order_date >= DATE '2026-01-01') LOOP
    IF NOT l_totals.EXISTS(r.customer_id) OR NOT l_totals(r.customer_id).EXISTS(r.ym) THEN
      l_totals(r.customer_id)(r.ym) := 0;
    END IF;
    l_totals(r.customer_id)(r.ym) := l_totals(r.customer_id)(r.ym) + r.amount;
  END LOOP;
END;

(This particular aggregation is a GROUP BY in SQL — the example shows the structure, not a recommendation.)

SQL 
CREATE OR REPLACE PACKAGE id_types AS
  TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
END;
/
DECLARE
  l_ids id_types.t_ids;
  l_cnt PLS_INTEGER;
BEGIN
  l_ids(1) := 1001; l_ids(2) := 1002; l_ids(3) := 1003;
  SELECT COUNT(*) INTO l_cnt
  FROM   orders
  WHERE  customer_id IN (SELECT COLUMN_VALUE FROM TABLE(l_ids));      -- bind the whole array
END;

An IN-list from a collection instead of string concatenation — one cursor regardless of list size.

  • Memory per element is roughly the key + the value + 30–60 bytes of overhead. A VARCHAR2(10) value keyed by PLS_INTEGER: ~80 bytes → 5M customers ≈ 400 MB per session. Load only the subset you need (WHERE tier = 'VIP'), or only the columns you need.
  • Per session. 8 workers each load their own copy: 8 × 400 MB. PGA_AGGREGATE_LIMIT ends the job with ORA-04036. If every session needs the same big map, that is a job for a join or RESULT_CACHE, not for PGA.
  • Package-level arrays live as long as the session. With connection pooling, "the session" is forever. A package array loaded on first use and never DELETEd is a slow leak across the pool. Clear it at the end of the job.
  • Copy on pass. Passing the array to a procedure as IN OUT copies it (twice). IN does not; IN OUT NOCOPY usually does not — NOCOPY.

  1. arr(key) on a missing key → NO_DATA_FOUND. Test with EXISTS first, or catch it deliberately.
  2. FOR i IN 1 .. arr.COUNT on a key-based array — the subscripts are customer ids, not 1..n. Iterate with FIRST/NEXT (COLLECTION METHODS).
  3. BULK COLLECT INTO a keyed array — the keys become 1..n; the previous contents are gone.
  4. INDEX BY VARCHAR2 with case/whitespace variations in the key — 'New ' ≠ 'NEW'. Normalize keys on both sides.
  5. NLS_COMP = LINGUISTIC sessions — string keys are compared linguistically; two keys that differ only in accent may collide. Rare, and nasty to debug.
  6. Declaring the type in an anonymous block and expecting to use it in SQL — needs a package spec (3f).
  7. Assigning a whole array to another (a := b) copies it — fine for small, expensive for 5M.