Bringing Oracle's Associative Arrays to MariaDB

One of the standout features of Oracle PL/SQL is the associative array — a versatile and efficient in-memory data structure that developers rely on for fast temporary lookups, streamlined batch processing, and dynamic report generation.

With the MariaDB 12.1 preview release, we’re excited to announce that associative arrays have landed in MariaDB as part of our growing set of Oracle compatibility features. This milestone, tracked under MDEV-34319, brings Oracle-style associative arrays into the MariaDB procedural language — complete with native type declarations, variable construction, and method support.

Let’s explore what’s included, what’s different, and how this feature was implemented from the ground up.

The majority of the code behind the associative array was implemented by Iqbal Hassan (Rakuten), while MariaDB plc engineers (e.g. Alexander Barkov) helped to integrate the new code into MariaDB data type sub-system. We’re very thankful to Rakuten for this great contribution!

What Are Associative Arrays?

In Oracle, associative arrays (called index-by tables) are sparse collections of elements indexed by keys, which can be integers or strings.

Here’s an example of how one would do it in Oracle:

DECLARE
  TYPE array_t IS TABLE OF VARCHAR2(64) INDEX BY PLS_INTEGER;
  array array_t;
BEGIN
  array(1) := 'Hello';
  array(2) := 'World';
  DBMS_OUTPUT.PUT_LINE(array(1));
END;

At companies like Rakuten, associative arrays are used extensively for batch processing and dynamic lookups, enabling readable, performant code — especially in data-heavy operations.

But until now, MariaDB had no such equivalent.

Introducing Associative Arrays in MariaDB 12.1

With MariaDB 12.1 (and sql_mode=oracle enabled), you can now:

Define associative array types in stored routines or anonymous blocks
Declare and construct associative array variables
Use a familiar Oracle-style method API to work with array contents
Leverage reference types (%TYPE, %ROWTYPE) as array element types

Type Definition

TYPE array_t IS TABLE OF element_type INDEX BY key_type;

Key things to know:

element_type can be any built-in MariaDB type, a RECORD, or even a %ROWTYPE from a table
key_type can be an integer or a VARCHAR

For example:

TYPE array_t IS TABLE OF INT INDEX BY VARCHAR(10);

Unlike Oracle, where string comparison is controlled via NLS_SORT or NLS_COMP, MariaDB uses the SQL standard way to define string comparison style with the explicit COLLATE clause:

TYPE array_t IS TABLE OF INT INDEX BY VARCHAR(10) COLLATE utf8mb4_uca1400_as_ci;

Variable Declaration and Constructor

DECLARE
  TYPE array_t IS TABLE OF VARCHAR2(64) INDEX BY INTEGER;
  array array_t := array_t(1 => 'One', 2 => 'Two');
BEGIN
  ...

The constructor currently only accepts literals as keys (expressions will be added in a later version).

Built-In Methods

MariaDB supports the full Oracle-style method set for associative arrays:

Method	Type	Description
`DELETE([key])`	Procedure	Removes a specific key or clears the array
`EXISTS(key)`	Function	Returns `TRUE` if the key exists
`COUNT()`	Function	Returns the number of elements
`FIRST()`	Function	Returns the first key
`LAST()`	Function	Returns the last key
`NEXT(key)`	Function	Returns the key after the given one
`PRIOR(key)`	Function	Returns the key before the given one

For example, to iterate through an array:

DECLARE
  ...
BEGIN
  ...
  key := array.FIRST();
  WHILE key IS NOT NULL DO
    SELECT key, array(key);
    key := array.NEXT(key);
  END WHILE;
END;

What’s Different from Oracle?

While the feature set is largely aligned with Oracle’s implementation, there are a few differences:

Only literals as keys in the constructor: When using constructors, keys must be literals — Oracle allows expressions
Collation control: Instead of NLS_SORT or NLS_COMP, MariaDB uses SQL-standard COLLATE clause
No nested associative arrays: Arrays of arrays are not yet supported

These differences are largely rooted in architectural constraints — and our goal has been to stay as close to Oracle semantics as possible while maintaining performance and predictability.

Under the Hood: How It Works

Associative arrays in MariaDB are backed by the red-black tree, using MariaDB’s native TREE implementation (my_tree.h). Elements are stored as packed buffers using the Binary_string class. Keys are stored in either raw byte form (for integers) or String objects (for string keys with collation).

This design allows efficient lookup, insertion, and traversal — all without introducing heavy runtime overhead.

Try It Out in MariaDB 12.1 Preview

The associative array feature is now available in the MariaDB 12.1 preview release. Just set sql_mode=oracle and dive in.

We’re excited to see how developers use this new capability — whether you’re porting existing Oracle code, optimizing in-memory processing, or simply exploring what’s possible with MariaDB.

Let us know what works, what surprises you, and what you’d like to see next.

MariaDB 12.1 release is coming soon — your feedback directly shapes its future. Try the preview, test it, and help us make 12.1 better.