# 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](../course-plan.md) · Appendix entry: [ASSOCIATIVE ARRAY](../appendix.md#associative-array) · Related: [COLLECTION METHODS](collection-methods.md), [NOCOPY](nocopy.md), [BULK COLLECT](bulk-collect.md), [RESULT_CACHE](result_cache.md)

---

## 1. What It Is and When

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.

## 2. Syntax

```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).

## 3. Examples on the Course Schema

### 3a. Integer-keyed lookup — the Chapter 4 pattern

```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;
```

### 3b. String-keyed — code → description

```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.

### 3c. A whole row per key — cache the record

```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;
```

### 3d. Loading with BULK COLLECT — dense first, then re-key

`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.

### 3e. Two-level map — orders per customer per month

```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.)

### 3f. Using it in SQL — package-declared type (12c+)

```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.

## 4. What Breaks at Scale

- **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](result_cache.md), 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 `DELETE`d 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](nocopy.md).

## 5. Pitfalls

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](collection-methods.md)).
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.

## 6. Related

- [COLLECTION METHODS](collection-methods.md) — `EXISTS`, `FIRST`/`NEXT`, `DELETE`.
- [NOCOPY](nocopy.md) — passing large arrays without copying.
- [BULK COLLECT](bulk-collect.md) — loading.
- [RESULT_CACHE](result_cache.md) — the shared-across-sessions alternative for small tables.