How to Fetch Multiple Oracle Execution Plans in One Nice Query

When looking at execution plans in Oracle, we'll have to do several steps to be able to call the DBMS_XPLAN package functions. In fact, we have to find out the SQL_ID for a given statement first, and only then we can get its plan. I've blogged about this previously, here. However, thanks to lateral unnesting, … Continue reading How to Fetch Multiple Oracle Execution Plans in One Nice Query

SQL IN Predicate: With IN List or With Array? Which is Faster?

Hah! Got nerd-sniped again: https://stackoverflow.com/questions/43099226/how-to-make-jooq-to-use-arrays-in-the-in-clause/43102102 A jOOQ user was wondering why jOOQ would generate an IN list for a predicate like this: Java COLUMN.in(1, 2, 3, 4) SQL COLUMN in (?, ?, ?, ?) ... when in fact there could have been the following predicate being generated, instead: COLUMN = any(?::int[]) In the second case, … Continue reading SQL IN Predicate: With IN List or With Array? Which is Faster?

How to Benchmark Alternative SQL Queries to Find the Fastest Query

Tuning SQL isn't always easy, and it takes a lot of practice to recognise how any given query can be optimised. One of the most important slides of my SQL training is the one summarising "how to be fast": Some of these bullets were already covered on this blog. For instance avoiding needless, mandatory work, … Continue reading How to Benchmark Alternative SQL Queries to Find the Fastest Query

The Open-Closed Principle is Often Not What You Think it Is

jOOQ is a library that loves making everything internal final and package private. We have tons of classes like these: final class Concat extends AbstractFunction<String> { // ... } The class implements the semantics of SQL string concatenation. Clearly, you shouldn't need to tamper with it (or even know about it), because it is "protected" … Continue reading The Open-Closed Principle is Often Not What You Think it Is

Creating Tables Dum and Dee in PostgreSQL

I was nerd-sniped: https://twitter.com/benjiweber/status/842725600280428545 So tables dee and dum are two theoretical tables in SQL, and they can be characterised as such: [Dee] is the relation that has no attributes and a single tuple. It plays the role of True. [Dum] is the relation that has no attributes and no tuples. It plays the role … Continue reading Creating Tables Dum and Dee in PostgreSQL

A Nice API Design Gem: Strategy Pattern With Lambdas

With Java 8 lambdas being available to us as a programming tool, there is a "new" and elegant way of constructing objects. I put "new" in quotes, because it's not new. It used to be called the strategy pattern, but as I've written on this blog before, many GoF patterns will no longer be implemented … Continue reading A Nice API Design Gem: Strategy Pattern With Lambdas

Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys

There are many many opinions out there regarding the old surrogate key vs. natural key debate. Most of the times, surrogate keys (e.g. sequence generated IDs) win because they're much easier to design: They're easy to keep consistent across a schema (e.g. every table has an ID column, and that's always the primary key) They're … Continue reading Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys

Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work”

Probably the most impactful thing you could learn about when writing efficient SQL is indexing. A very close runner-up, however, is the fact that a lot of SQL clients demand tons of "unnecessary, mandatory work" from the database. Repeat this after me: Unnecessary, Mandatory Work What is "unnecessary, mandatory work"? It's two things (duh): Unnecessary … Continue reading Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work”

Impress Your Coworkers With the Incredible NATURAL FULL OUTER JOIN!

There are already only very few real-world use-cases for FULL [ OUTER ] JOIN, but maybe, you have run into this beast in the past. But when was the last time you've seen a NATURAL JOIN? Right. A quick reminder from our article about JOINs: FULL JOIN A FULL JOIN is a type of OUTER … Continue reading Impress Your Coworkers With the Incredible NATURAL FULL OUTER JOIN!