ProgrammingImplicit path joins may now skip unnecessary tables in...

Implicit path joins may now skip unnecessary tables in the join tree

-


One of jOOQ’s key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises – unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn’t part of jOOQ’s feature set, so far.

This changes, to some extent, with jOOQ 3.19, and #14992, for implicit path joins only. So far, when you write:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(ACTOR)
   .fetch();

The resulting join tree of this query may look similar to this:

FROM
  actor
    LEFT JOIN film_actor ON actor.actor_id = film_actor.actor_id
    LEFT JOIN film ON film_actor.film_id = film.film_id
    LEFT JOIN film_category ON film.film_id = film_category.film_id
    LEFT JOIN category ON film_category.category_id = category.category_id

But, the FILM table isn’t really needed in this particular query, because no columns from it are being projected, and the presence of primary / foreign keys guarantees equivalence if we just skip the table in the join tree:

FROM
  actor
    LEFT JOIN film_actor ON actor.actor_id = film_actor.actor_id
    LEFT JOIN film_category ON film_actor.film_id = film_category.film_id
    LEFT JOIN category ON film_category.category_id = category.category_id

As soon as any column from the FILM table is projected (or referenced, in general), then the table re-appears in the join tree. E.g. for this query:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(ACTOR)
   // This means we have to add the FILM table again to the join tree:
   .where(ACTOR.film().TITLE.like("A%"))
   .fetch();

In many RDBMS, this doesn’t really matter, because the RDBMS may do the same optimisation, but in some, there’s a big difference. This is a great optimisation in particular because with implicit path joins, jOOQ users can’t really hand-write these optimisations as they’re not authoring the join tree in the FROM clause themselves.

Why implement this only in jOOQ 3.19

Before jOOQ 3.19, there was no support for to-many path joins, and particularly, not for many-to-many path joins, which skip the relationship table. But now, users can write:

// This
ACTOR.film().category().NAME

// Is short (and equivalent) for this:
ACTOR.filmActor().film().filmCategory().category().NAME

Note that the above examples assume that the new Settings.renderImplicitJoinToManyType flag is set to LEFT_JOIN. By default, implicit to-many joins aren’t supported because of their weird semantics in terms of query cardinalities as explained in this blog post. By default, such paths have to be declared explicitly in the FROM clause:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(
       ACTOR,
       ACTOR.film(),
       ACTOR.film().category())
   .fetch();

Or, just:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(
       ACTOR,
       ACTOR.film().category())
   .fetch();



Source link

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest news

Transform Customer Feedback into Actionable Insights with CrewAI and Streamlit | by Alan Jones | Dec, 2024

AI for BIBuild an AI-powered app to analyze unstructured feedback, generate insightful reports, and create interactive visualizationsNew AI...

Talking about time like a human.

Jotting down some notes,...

Manage Amazon SageMaker JumpStart foundation model access with private hubs

Amazon SageMaker JumpStart is a machine learning (ML) hub offering...

Take your dog for a walk

The following contains spoilers for “Empire of Death.”“Empire of Death” is the typical Russell T. Davies series finale:...

Must read

You might also likeRELATED
Recommended to you