How to use Oracle's APPEND hint with JOOQ-rendered INSERT statements?

Question

I have a JOOQ query like the following:

dsl.insertInto(AUTHOR_ARCHIVE)
   .select(selectFrom(AUTHOR).where(AUTHOR.DECEASED.isTrue()));

I would like to leverage Oracle's /*+ APPEND */ hint to improve performance.

However, JOOQ's documentation on oracle hints does not include an example for INSERT statements. How do I inject the append hint in the above query with JOOQ 3.8?

Answer 1

Hints for DML are not currently supported by jOOQ, but are on the roadmap: https://github.com/jOOQ/jOOQ/issues/2654

In the meantime, your options are:

Resorting to plain SQL

e.g. via DSLContext.execute(String). Typically, you'd be extracting the SQL from the jOOQ Query via Query.getSQL() and Query.getBindValues(). For example:

Query query = ctx.insertInto(...).values(...);
ctx.execute(
    query.getSQL().replace("insert into", "insert /*+APPEND*/ into"), 
    query.getBindValues().toArray()
);

Implement DML hint support yourself

Via an ExecuteListener which patches the generated SQL on the renderEnd() event whenever some flag is set to true. You could put flags in Configuration.data(), for instance:

public class MyListener extends DefaultExecuteListener {
    @Override
    public void renderEnd(ExecuteContext ctx) {
        if (ctx.data().containsKey("insert hint")) {
            ctx.sql(ctx.sql().replace(
                "insert into", 
                "insert " + ctx.data().get("insert hint") + " into"
            ));
        }
    }
}

And then:

Configuration withHint = usualConfiguration.derive();
withHint.data("insert hint", "/*+APPEND*/");

DSL.using(withHint)
   .insertInto(...).values(...).execute();