Oracle 1Z0-183: Sparse Collection, Missing Shipment

Sparse Collection, Missing Shipment

A warehouse procedure loads 10,000 order ids into a nested table using BULK COLLECT INTO l_ids, then calls a cleanup routine that removes cancelled orders with l_ids.DELETE(i) - roughly 30 elements per run - and finally iterates the survivors with FOR i IN 1 .. l_ids.COUNT LOOP ship_api.send(l_ids(i)); END LOOP;. Last night the run raised ORA-01403 (no data found) during the shipping loop. The operator's quick fix changed the bound to l_ids.LAST, and the job failed even earlier, on the very first cancelled order at position 47. Meanwhile the business reports a shipment that was never sent, and that order was never cancelled. No changes were made to ship_api or to the loader query.

1. Explain what COUNT and LAST each mean for this collection, and why both loop bounds fail on the same data.

Read the worked answer

After DELETE, the collection is sparse: elements were removed from the middle, so indexes are not contiguous.

COUNT = number of elements that currently exist (9,970), not the highest index.
LAST = the highest index that exists (still 10,000, assuming the tail was untouched).
FIRST/NEXT walk only the surviving indexes.

Both bounds are wrong, in opposite directions:

FOR i IN 1 .. COUNT iterates 1..9,970. It crashes at the first gap (the deleted index 47, since 47 ≤ 9,970) - ORA-01403 - and, if it somehow survived, it would never reach indexes 9,971..10,000, which is the missing shipment the business is chasing.
FOR i IN 1 .. LAST iterates to 10,000 but hits the same gap at 47 immediately, which is why it failed "even earlier" in the sense of being no more correct.

Neither bound says anything about which indexes exist. That is the whole defect.

2. Give the correct iterations and explain when you would choose each, plus the FORALL equivalent.

Read the worked answer

Three correct options:

1. FIRST/NEXT - always safe, any collection type:

v_idx := l_ids.FIRST;
WHILE v_idx IS NOT NULL LOOP
  ship_api.send(l_ids(v_idx));
  v_idx := l_ids.NEXT(v_idx);
END LOOP;

Works for nested tables, varrays and associative arrays, sparse or dense. Per-call overhead makes it the slowest for 10,000 rows.

2. INDICES OF - nested tables and varrays only:

FOR i IN INDICES OF l_ids LOOP
  ship_api.send(l_ids(i));
END LOOP;

Concise and sparse-safe. It does not compile for an associative array indexed by VARCHAR2, so it is not a universal replacement.

3. Bulk DML:

FORALL i IN INDICES OF l_ids
  ship_api.send(l_ids(i));   -- only if send() is a row-level DML/PLSQL call, not a network call

Volatile/remote calls inside FORALL do not get the batching benefit; here FORALL mainly buys fewer context switches if send writes a shipping row.

I would take option 1 for correctness first, then measure; if the shipping call is a remote API, batching is not the lever anyway.

3. Rather than fixing the loops, a colleague proposes deleting from the end backwards, or indexing the collection by `order_id`. Assess both.

Read the worked answer

Deleting from the end backwards makes the collection dense only if every delete is to the highest remaining index, which is not the case - cancelled orders are scattered, and DELETE(47) leaves a hole whether or not you process in reverse. It hides the defect for some runs and reappears with the next cancellation pattern. It also adds an ordering dependency to cleanup code that should not have one. Reject.

Indexing by order_id is a genuine improvement, for a different reason: with an associative array indexed by the key you care about, lookups become l_orders(order_id) - no positional assumption exists, so a missing key can be tested with EXISTS instead of crashing. Caveats:

INDICES OF and positional FORALL no longer apply; iterate with FIRST/NEXT or use FORALL i IN INDICES OF only where the type allows it.
DELETE of a key still makes it sparse; sparseness is the thing to design for, not to avoid.
Memory and hashing cost over 10,000 rows is negligible at this size.

Best combination: keep the nested table from BULK COLLECT for the fetch, but either mark cancelled rows in a flag column instead of deleting, or iterate with FIRST/NEXT. The flag approach keeps all 10,000 indexes dense, which is the cheapest fix to reason about during an incident.