Jooq resolves unnest / table to union all (Oracle)

Question

I'm using jOOQ to delete a variable number of rows from an Oracle database:

List<Integer> ids = Lists.newArrayList(1, 2, 3, 4);

db.deleteFrom(MESSAGE)
    .where(MESSAGE.ID.in(ids))
    .execute();

However, this means that a variable number of bind variables is used. This leads to the problem that Oracle always does a hard parse.

I have tried using the unnest or table function to create a statement with only one bind variable. Unfortunately, this does not seem to work. jOOQ creates statements with multiple bind variables and union all statements:

db.deleteFrom(MESSAGE)
    .where(MESSAGE.ID.in(
        select(field("*", Long.class))
            .from(table(ids))
    ))
    .execute();

LoggerListener       DEBUG - Executing query          : delete from "MESSAGE" where "MESSAGE"."ID" in (select * from ((select ? "COLUMN_VALUE" from dual) union all (select ? "COLUMN_VALUE" from dual) union all (select ? "COLUMN_VALUE" from dual) union all (select ? "COLUMN_VALUE" from dual)) "array_table")
LoggerListener       DEBUG - -> with bind values      : delete from "MESSAGE" where "MESSAGE"."ID" in (select * from ((select 1 "COLUMN_VALUE" from dual) union all (select 2 "COLUMN_VALUE" from dual) union all (select 3 "COLUMN_VALUE" from dual) union all (select 4 "COLUMN_VALUE" from dual)) "array_table")

The javadoc of the unnest function recommends using the table function for Oracle

Create a table from an array of values.

This is equivalent to the TABLE function for H2, or the UNNEST function in HSQLDB and Postgres

For Oracle, use table(ArrayRecord) instead, as Oracle knows only typed arrays

In all other dialects, unnesting of arrays is emulated using several UNION ALL connected subqueries.

Altough I use the table function, I still get the emulated UNION ALL statement.

Answer 1

TABLE operator specifics

Oracle's TABLE(collection) function doesn't accept arbitrary arrays, only SQL table types, such as:

CREATE TYPE t AS TABLE OF NUMBER

You could create such a type and use the code generator to create a TRecord, which you could pass to that TABLE operator:

from(table(new TRecord(ids)))

Unfortunately, such a type is required in Oracle. In its absence, the UNION ALL emulation seems to be the best way, but that doesn't solve your problem.

Note the TABLE operator has its own caveats, mainly that it produces poor cardinality estimates, so for small tables, it may not be the best choice. Though, do measure yourself! I've documented this here in a blog post

Avoiding cursor cache contention

jOOQ has a nice feature called IN list padding, which mitigates most of the dynamic SQL problems that arise from such hard parses. In short, it prevents arbitrary IN list sizes by repeating the last element to pad the list up to 2^n elements, getting you:

id IN (?)                      -- For 1 element
id IN (?, ?)                   -- For 2 elements
id IN (?, ?, ?, ?)             -- For 3-4 elements
id IN (?, ?, ?, ?, ?, ?, ?, ?) -- For 5-8 elements

It's a hack for when the TABLE operator is too complex. It works by reducing the number of possible predicates logarithmically, which might just be good enough.

Other alternatives

You can always also use:

• Temp tables, and batch insert the IDs in there
• Use an actual semi join: ID IN (SELECT original_id FROM original_table) (this is usually the best approach, if possible)