Composite Data Type Performance FAQS

1. What are composite data types in Oracle?

Composite data types store multiple values under one variable name.

Common types:

·        RECORD

·        Associative Array

·        Nested Table

·        VARRAY

Performance depends on:

·        Memory usage

·        SQL vs PL/SQL interaction

·        Context switching

·        Collection size

·        Storage type

2. Which composite type is fastest?

It depends on the use case:

Type	Performance Characteristics
RECORD	Very fast (in-memory only)
Associative Array	Very fast for key lookups
Nested Table	Good for bulk SQL operations
VARRAY	Fast for small, fixed-size collections

There is no universally fastest type — choose based on requirement.

3. Why are RECORD types fast?

·        Stored entirely in PL/SQL memory

·        No SQL interaction required

·        No indexing overhead

Best for:

·        Holding a single row

·        Passing grouped values between procedures

4. Why are Associative Arrays efficient?

·        Stored in PGA memory

·        O(1) key-based lookup (hash-like behavior)

·        No storage in database tables

·        Dynamic size

Best for:

·        Lookup tables

·        Caching data

·        Temporary mappings

Not usable directly in SQL statements.

5. How do Nested Tables perform?

·        Dynamic size

·        Can be used in SQL via TABLE() operator

·        Slight overhead when stored in database

·        Good support for BULK COLLECT and FORALL

Best for:

·        Large data processing

·        Multi-row operations

·        SQL + PL/SQL interaction

Performance improves significantly when used with:

·        BULK COLLECT

·        FORALL

6. How do VARRAYs perform?

·        Stored contiguously in memory

·        Maximum size fixed

·        Good for small collections

·        Faster access than nested tables for small datasets

Not ideal for:

·        Large or growing datasets

·        Frequent insert/delete operations

7. What impacts performance most?

1️⃣ Context Switching (SQL ↔ PL/SQL)

Row-by-row operations are slow.

Better approach:

·        Use BULK COLLECT

·        Use FORALL

2️⃣ Collection Size

·        Large collections consume more PGA memory

·        May cause memory pressure

3️⃣ Sparse vs Dense Structure

Structure	Performance Impact
Dense (VARRAY)	Faster iteration
Sparse (Associative Array)	Slight overhead for traversal

4️⃣ Database Storage

·        Nested Tables and VARRAYs stored in tables incur I/O

·        Associative Arrays do not (memory only)

8. Which type is best for bulk operations?

Best combination for performance:

·        Nested Table + BULK COLLECT + FORALL

Example pattern:

SELECT col BULK COLLECT INTO collection FROM table;

FORALL i IN collection.FIRST .. collection.LAST

   INSERT INTO target VALUES collection(i);

This reduces context switching and improves speed dramatically.

9. Common performance mistakes

·        Row-by-row processing instead of bulk

·        Using VARRAY for large datasets

·        Not freeing large collections

·        Storing large nested tables unnecessarily in DB

·        Excessive SQL inside loops

10. Memory considerations

Composite types are stored in:

·        PGA (Program Global Area) for PL/SQL collections

·        Disk when stored in tables (nested table / varray columns)

Large collections:

·        Increase PGA usage

·        May affect overall session performance

11. Performance comparison summary

Use Case	Best Type
Single row structure	RECORD
Fast key lookup	Associative Array
Bulk SQL operations	Nested Table
Small ordered list	VARRAY
In-memory caching	Associative Array

12. Best practices for performance

·        Prefer RECORD for grouped row data

·        Use Associative Arrays for lookup and caching

·        Use Nested Tables for bulk SQL processing

·        Use VARRAY only for small fixed-size collections

·        Avoid row-by-row SQL inside loops

·        Always use BULK COLLECT and FORALL for large data