The Index You’ve Added is Useless. Why?

Recently, at the office:

Bob: I’ve looked into that slow query you’ve told me about yesterday, Alice. I’ve added the indexes you wanted. Everything should be fine now

Alice: Thanks Bob. I’ll quickly check … Nope Bob, still slow, it didn’t seem to work

Bob: You’re right Alice! It looks like Oracle isn’t picking up the index, for your query even if I add an /*+INDEX(...)*/ hint. I don’t know what went wrong!?

And so, the story continues. Alice is frustrated because her feature doesn’t ship on time, Bob is frustrated because he thinks that Oracle doesn’t work right.

True story!

Bob Forgot about Oracle and NULL

Poor Bob forgot (or didn’t know) that Oracle doesn’t put NULL values in “ordinary” indexes. Think about it this way:

  id            NUMBER(38)   NOT NULL PRIMARY KEY,
  first_name    VARCHAR2(50) NOT NULL,
  last_name     VARCHAR2(50) NOT NULL,
  date_of_birth DATE             NULL

CREATE INDEX i_person_dob ON person(date_of_birth);

Now, Bob thinks that his index solves all problems, because he verified if the index worked using the following query:

FROM   person
WHERE  date_of_birth > DATE '1980-01-01';

(of course, you generally shouldn’t SELECT *)

And the execution plan looked alright:

| Id  | Operation                   | Name         |
|   0 | SELECT STATEMENT            |              |
|*  2 |   INDEX RANGE SCAN          | I_PERSON_DOB |

This is because Bob’s predicate doesn’t rely on NULL being part of the I_PERSON_DOB index. Unfortunately, Alice’s query looked more like this (simplified version):

FROM   dual
WHERE  DATE '1980-01-01' NOT IN (
  SELECT date_of_birth FROM person

So, essentially, Alice’s query checked if anyone had their date of birth at a given date. Her execution plan looked like this:

| Id  | Operation          | Name   |
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|   2 |   FAST DUAL        |        |

As you can see, her query made a TABLE ACCESS FULL operation, bypassing the index. Why? It’s simple:

Even if our DATE '1980-01-01' value is or is not in the index, we’ll still have to check the whole table to see whether a single NULL value is contained in the date_of_birth column. Because, if there was a NULL value, the NOT IN predicate in Alice’s query would never yield TRUE or FALSE, but NULL.

Alice can solve this issue with NOT EXISTS

Alice can solve it easily herself, by replacing NOT IN through NOT EXISTS, a predicate that doesn’t suffer from SQL’s peculiar three-valued boolean logic.

FROM   dual
  FROM   person
  WHERE  date_of_birth = DATE '1980-01-01'

This new query now again yields an optimal plan:

| Id  | Operation         | Name         |
|   0 | SELECT STATEMENT  |              |
|*  1 |  FILTER           |              |
|   2 |   FAST DUAL       |              |

But the problem still exists, because what can happen, will happen, and Alice will have to remember this issue for every single query she writes.

Bob should just set the column to NOT NULL

The best solution, however is to simply set the column to NOT NULL:

MODIFY date_of_birth DATE NOT NULL;

With this constraint, the NOT IN query is exactly equivalent to the NOT EXISTS query, and Bob and Alice can be friends again.

Takeaway: How to find “bad” columns?

It’s easy. The following useful query lists all indexes that have at least one nullable column in them.

    LPAD(i.column_position,  2) || ': ' || 
    RPAD(i.column_name    , 30) || ' '  ||
    DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'), 
    ', '
  ) WITHIN GROUP (ORDER BY i.column_position) 
    AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) = 
  ((i.table_name, i.column_name))
  FROM user_tab_cols t
  WHERE (t.table_name, t.column_name, t.nullable) = 
       ((i.table_name, i.column_name, 'Y'       ))
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;

When run against Bob and Alice’s schema, the above query yields:

TABLE_NAME | INDEX_NAME   | NULLABLE columns in indexes

Use this query on your own schema now, and go through the results, carefully evaluating if you really need to keep that column nullable. In 50% of the cases, you don’t. By adding a NOT NULL constraint, you can tremendously speed up your application!

Java 8 Friday: Language Design is Subtle

At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

Language Design is Subtle

It’s been a busy week for us. We have just migrated the jOOQ integration tests to Java 8 for two reasons:

  • We want to be sure that client code compiles with Java 8
  • We started to get bored of writing the same old loops over and over again

The trigger was a loop where we needed to transform a SQLDialect[] into another SQLDialect[] calling .family() on each array element. Consider:

Java 7

SQLDialect[] families = 
    new SQLDialect[dialects.length];
for (int i = 0; i < families.length; i++)
    families[i] = dialects[i].family();

Java 8

SQLDialect[] families = 
      .map(d ->

OK, it turns out that the two solutions are equally verbose, even if the latter feels a bit more elegant. :-)

And this gets us straight into the next topic:


For backwards-compatibility reasons, arrays and the pre-existing Collections API have not been retrofitted to accommodate all the useful methods that Streams now have. In other words, an array doesn’t have a map() method, just as much as List doesn’t have such a method. Streams and Collections/arrays are orthogonal worlds. We can transform them into each other, but they don’t have a unified API.

This is fine in everyday work. We’ll get used to the Streams API and we’ll love it, no doubt. But because of Java being extremely serious about backwards compatibility, we will have to think about one or two things more deeply.

Recently, we have published a post about The Dark Side of Java 8. It was a bit of a rant, although a mild one in our opinion (and it was about time to place some criticism, after all the praise we’ve been giving Java 8 in our series, before ;-) ). First off, that post triggered a reaction by Edwin Dalorzo from our friends at Informatech. (Edwin has written this awesome post comparing LINQ and Java 8 Streams, before). The criticism in our article evolved around three main aspects:

  • Overloading getting more complicated (see also this compiler bug)
  • Limited support for method modifiers on default methods
  • Primitive type “API overloads” for streams and functional interfaces

A response by Brian Goetz

I then got a personal mail from no one less than Brian Goetz himself (!), who pointed out a couple of things to me that I had not yet thought about in this way:

I still think you’re focusing on the wrong thing. Its not really the syntax you don’t like; its the model — you don’t want “default methods”, you want traits, and the syntax is merely a reminder that you didn’t get the feature you wanted. (But you’d be even more confused about “why can’t they be final” if we dropped the “default” keyword!) But that’s blaming the messenger (where here, the keyword is the messenger.)

Its fair to say “this isn’t the model I wanted”. There were many possible paths in the forest, and it may well be the road not taken was equally good or better.

This is also what Edwin had concluded. Default methods were a necessary means to tackle all the new API needed to make Java 8 useful. If Iterator, Iterable, List, Collection, and all the other pre-existing interfaces had to be adapted to accommodate lambdas and Streams API interaction, the expert group would have needed to break an incredible amount of API. Conversely, without adding these additional utility methods (see the awesome new Map methods, for instance!), Java 8 would have been only half as good.

And that’s it.

Even if maybe, some more class building tools might have been useful, they were not in the center of focus for the expert group who already had a lot to do to get things right. The center of focus was to provide a means for API evolution. Or in Brian Goetz’s own words:

Reaching out to the community

It’s great that Brian Goetz reaches out to the community to help us get the right picture about Java 8. Instead of explaining rationales about expert group decisions in private messages, he then asked me to publicly re-ask my questions again on Stack Overflow (or lambda-dev), such that he can then publicly answer them. For increased publicity and greater community benefit, I chose Stack Overflow. Here are:

The amount of traction these two questions got in no time shows how important these things are to the community, so don’t miss reading through them!

“Uncool”? Maybe. But very stable!

Java may not have the “cool” aura that node.js has. You may think about JavaScript-the-language whatever you want (as long as it contains swear words), but from a platform marketing perspective, Java is being challenged for the first time in a long time – and being “uncool” and backwards-compatible doesn’t help keeping developers interested.

But let’s think long-term, instead of going with trends. Having such a great professional platform like the Java language, the JVM, the JDK, JEE, and much more, is invaluable. Because at the end of the day, the “uncool” backwards-compatibility can also be awesome. As mentioned initially, we have upgraded our integration tests to Java 8. Not a single compilation error, not a single bug. Using Eclipse’s BETA support for Java 8, I could easily transform anonymous classes into lambdas and write awesome things like these upcoming jOOQ 3.4 nested transactions (API not final yet):

ctx.transaction(c1 -> {
       .values(3, "Doe")

    // Implicit savepoint here
    try {
        DSL.using(c1).transaction(c2 -> {
               .set(AUTHOR.FIRST_NAME, "John")

            // Rollback to savepoint
            throw new MyRuntimeException("No");

    catch (MyRuntimeException ignore) {}

    return 42;

So at the end of the day, Java is great. Java 8 is a tremendous improvement over previous versions, and with great people in the expert groups (and reaching out to the community on social media), I trust that Java 9 will be even better. In particular, I’m looking forward to learning about how these two projects evolve:

Although, again, I am really curious how they will pull these two improvements off from a backwards-compatibility perspective, and what caveats we’ll have to understand, afterwards. ;-)

Anyway, let’s hope the expert groups will continue to provide public feedback on Stack Overflow. Stay tuned for more awesome Java 8 content on this blog.

How to Implement Sort Indirection in SQL

I’ve recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order.

They wrote

FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',

They got


They wanted


Very often, according to your business rules, sorting orders are not “natural”, as in numeric sorting or in alpha-numeric sorting. Some business rule probably specified that GE526OTACCD3ANPH-149839 needs to appear last in a list. Or the user might have re-arranged product names in their screen with drag and drop, producing new sort order.

We could discuss, of course, if such sorting should be performed in the UI layer or not, but let’s assume that the business case or the performance requirements or the general architecture needed for this sorting to be done in the database. How to do it? Through…

Sort Indirection

In fact, you don’t want to sort by the product name, but by a pre-defined enumeration of such names. In other words, you want a function like this:

CE367FAACDHCANPH-151556 -> 1
CE367FAACEX9ANPH-153877 -> 2
NI564FAACJSFANPH-162605 -> 3
GE526OTACCD3ANPH-149839 -> 4

With plain SQL, there are many ways to do the above. Here are two of them (also seen in my Stack Overflow answer):

By using a CASE expression

You can tell the database the explicit sort indirection easily, using a CASE expression in your ORDER BY clause:

FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
  CASE WHEN name = 'CE367FAACDHCANPH-151556' THEN 1
       WHEN name = 'CE367FAACEX9ANPH-153877' THEN 2
       WHEN name = 'NI564FAACJSFANPH-162605' THEN 3
       WHEN name = 'GE526OTACCD3ANPH-149839' THEN 4

Note that I’ve used the CASE WHEN predicate THEN value END syntax, because this is implemented in all SQL dialects. Alternatively (if you’re not using Apache Derby), you could also save some characters when typing, writing:

            WHEN 'CE367FAACEX9ANPH-153877' THEN 2
            WHEN 'NI564FAACJSFANPH-162605' THEN 3
            WHEN 'GE526OTACCD3ANPH-149839' THEN 4

Of course, this requires repeating the same values in the predicate and in the sort indirection. This is why, in some cases, you might be more lucky …


In the following example, the predicate and the sort indirection are taken care of in a simple derived table that is INNER JOIN‘ed to the original query:

FROM product
  VALUES('CE367FAACDHCANPH-151556', 1),
        ('CE367FAACEX9ANPH-153877', 2),
        ('NI564FAACJSFANPH-162605', 3),
        ('GE526OTACCD3ANPH-149839', 4)
) AS sort (name, sort)
ON =
ORDER BY sort.sort

The above example is using PostgreSQL syntax, but you might be able to implement the same in a different way in your database.

Using jOOQ’s sort indirection API

Sort indirection is a bit tedious to write out, which is why jOOQ has a special syntax for this kind of use-case, which is also documented in the manual. Any of the following statements perform the same as the above query:

// jOOQ generates 1, 2, 3, 4 as values in the
// generated CASE expression

// You can choose your own indirection values to
// be generated in the CASE expression
      new HashMap<String, Integer>() {{
        put("CE367FAACDHCANPH-151556", 2);
        put("CE367FAACEX9ANPH-153877", 3);
        put("NI564FAACJSFANPH-162605", 5);
        put("GE526OTACCD3ANPH-149839", 8);


Sort indirection is a nice trick to have up your sleeves every now and then. Never forget that you can put almost arbitrary column expressions in your SQL statement’s ORDER BY clause. Use them!

Three-State Booleans in Java

Every now and then, I miss SQL’s three-valued BOOLEAN semantics in Java. In SQL, we have:

  • TRUE
  • UNKNOWN (also known as NULL)

Every now and then, I find myself in a situation where I wish I could also express this UNKNOWN or UNINITIALISED semantics in Java, when plain true and false aren’t enough.

Implementing a ResultSetIterator

For instance, when implementing a ResultSetIterator for jOOλ, a simple library modelling SQL streams for Java 8:, Unchecked.function(r ->
    new SQLGoodies.Schema(

In order to implement a Java 8 Stream, we need to construct an Iterator, which we can then pass to the new Spliterators.spliteratorUnknownSize() method:
  Spliterators.spliteratorUnknownSize(iterator, 0), 

Another example for this can be seen here on Stack Overflow.

When implementing the Iterator interface, we must implement hasNext() and next(). Note that with Java 8, remove() now has a default implementation, so we don’t need to implement it any longer.

While most of the time, a call to next() is preceded by a call to hasNext() exactly once, nothing in the Iterator contract requires this. It is perfectly fine to write:

if (it.hasNext()) {
    // Some stuff

    // Double-check again to be sure
    if (it.hasNext() && it.hasNext()) {

        // Yes, we're paranoid
        if (it.hasNext())

How to translate the Iterator calls to backing calls on the JDBC ResultSet? We need to call

We could make the following translation:

  • Iterator.hasNext() == !ResultSet.isLast()
  • ==

But that translation is:

  • Expensive
  • Not dealing correctly with empty ResultSets
  • Not implemented in all JDBC drivers (Support for the isLast method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY)

So, we’ll have to maintain a flag, internally, that tells us:

  • If we had already called
  • What the result of that call was

Instead of creating a second variable, why not just use a three-valued java.lang.Boolean. Here’s a possible implementation from jOOλ:

class ResultSetIterator<T> implements Iterator<T> {

    final Supplier<? extends ResultSet>  supplier;
    final Function<ResultSet, T>         rowFunction;
    final Consumer<? super SQLException> translator;

     * Whether the underlying {@link ResultSet} has
     * a next row. This boolean has three states:
     * <ul>
     * <li>null:  it's not known whether there 
     *            is a next row</li>
     * <li>true:  there is a next row, and it
     *            has been pre-fetched</li>
     * <li>false: there aren't any next rows</li>
     * </ul>
    Boolean hasNext;
    ResultSet rs;

        Supplier<? extends ResultSet> supplier, 
        Function<ResultSet, T> rowFunction, 
        Consumer<? super SQLException> translator
    ) {
        this.supplier = supplier;
        this.rowFunction = rowFunction;
        this.translator = translator;

    private ResultSet rs() {
        return (rs == null) 
             ? (rs = supplier.get()) 
             :  rs;

    public boolean hasNext() {
        try {
            if (hasNext == null) {
                hasNext = rs().next();

            return hasNext;
        catch (SQLException e) {
            throw new IllegalStateException(e);

    public T next() {
        try {
            if (hasNext == null) {

            return rowFunction.apply(rs());
        catch (SQLException e) {
            throw new IllegalStateException(e);
        finally {
            hasNext = null;

As you can see, the hasNext() method locally caches the hasNext three-valued boolean state only if it was null before. This means that calling hasNext() several times will have no effect until you call next(), which resets the hasNext cached state.

Both hasNext() and next() advance the ResultSet cursor if needed.


Some of you may argue that this doesn’t help readability. They’d introduce a new variable like:

boolean hasNext;
boolean hasHasNextBeenCalled;

The trouble with this is the fact that you’re still implementing three-valued boolean state, but distributed to two variables, which are very hard to name in a way that is truly more readable than the actual java.lang.Boolean solution. Besides, there are actually four state values for two boolean variables, so there is a slight increase in the risk of bugs.

Every rule has its exception. Using null for the above semantics is a very good exception to the null-is-bad histeria that has been going on ever since the introduction of Option / Optional

In other words: Which approach is best? There’s no TRUE or FALSE answer, only UNKNOWN ;-)

Be careful with this

However, as we’ve discussed in a previous blog post, you should avoid returning null from API methods if possible. In this case, using null explicitly as a means to model state is fine because this model is encapsulated in our ResultSetIterator. But try to avoid leaking such state to the outside of your API.

Java 8 Friday: Let’s Deprecate Those Legacy Libs

At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

For the last two Fridays, we’ve been off for our Easter break, but now we’re back with another fun article:

Let’s Deprecate Those Legacy Libs

d8938bef47ea2f62ed0543dd9e35a483Apart from Lambdas and extension methods, the JDK has also been enhanced with a lot of new library code, e.g. the Streams API and much more. This means that we can critically review our stacks and – to the great joy of Doctor Deprecator – throw out all the garbage that we no longer need.

Here are a couple of them, just to name a few:

LINQ-style libraries

There are lots of libraries that try to emulate LINQ (i.e. the LINQ-to-Collections part). We’ve already made our point before, because we now have the awesome Java 8 Streams API. 5 years from today, no Java developer will be missing LINQ any longer, and we’ll all be Streams-masters with Oracle Certified Streams Developer certifications hanging up our walls.

Don’t get me wrong. This isn’t about LINQ or Streams being better. They’re pretty much the same. But since we now have Streams in the JDK, why worry about LINQ? Besides, the SQLesque syntax for collection querying was misleading anyway. SQL itself is much more than Streams will ever be (or needs to be).

So let’s list a couple of LINQesque APIs, which we’ll no longer need:


This was a fun attempt at emulating closures in Java through arcane and nasty tricks like ThreadLocal. Consider the following code snippet (taken from here):

// This lets you "close over" the
// System.out.println method
Closure println = closure(); { 

// in order to use it like so:
println.each("one", "two", "three");

Nice idea, although that semi-colon after closure(); and before that pseudo-closure-implementation block, which is not really a closure body… all of that seems quite quirky ;-)

Now, we’ll write:

Consumer<String> println = System.out::println;

Stream.of("one", "two", "three").forEach(println);

No magic here, just plain Java 8.

Let’s hear it one last time for Mario Fusco and Lambdaj.


Apparently, this is still being developed actively… Why? Do note that the roadmap also has a LINQ-to-SQL implementation in it, including:

Parser support. Either modify a Java parser (e.g. OpenJDK), or write a pre-processor. Generate Java code that includes expression trees.

Yes, we’d like to have such a parser for jOOQ as well. It would allow us to truly embed SQL in Java, similar to SQLJ, but typesafe. But if we have the Streams API, why not implement something like Streams-to-SQL?

We cannot say farewell to Julian Hyde‘s Linq4j just yet, as he’s still continuing work. But we believe that he’s investing in the wrong corner.


This is a library with a fun name, and it allows for doing things like…

from(animals).where("name", eq("Lion"))
             .and("age", eq(2))

from(animals).where("name", eq("Dog"))
             .or("age", eq(5))

But why do it this way, when you can write:
       .filter(a ->"Lion")
                 && a.age == 2)
       .filter(a ->"Dog")
                 || a.age == 5)

Let’s hear it for Wagner Andrade. And then off to the bin

Half of Guava

Guava has been pretty much a dump for all sorts of logic that should have been in the JDK in the first place. Take for instance. It is used for string-joining:

Joiner joiner = Joiner.on("; ").skipNulls();
. . .
return joiner.join("Harry", null, "Ron", "Hermione");

No need, any more. We can now write:

Stream.of("Harry", null, "Ron", "Hermione")
      .filter(s -> s != null)
      .collect(joining("; "));

Note also that the skipNulls flag and all sorts of other nice-to-have utilities are no longer necessary as the Streams API along with lambda expressions allows you to decouple the joining task from the filtering task very nicely.

Convinced? No?

What about:

And then, there’s the whole set of Functional stuff that can be thrown to the bin as well:

Of course, once you’ve settled on using Guava throughout your application, you won’t remove its usage quickly. But on the other hand, let’s hope that parts of Guava will be deprecated soon, in favour of an integration with Java 8.


Now, this one is a no-brainer, as the popular JodaTime library got standardised into the java.time packages. This is great news.

Let’s hear it for “Joda” Stephen Colebourne and his great work for the JSR-310.

Apache commons-io

The java.nio packages got even better with new methods that nicely integrate with the Streams API (or not). One of the main reasons why anyone would have ever used Apache Commons IO was the fact that it is horribly tedious to read files prior to Java 7 / 8. I mean, who would’ve enjoyed this piece of code (from here):

try (RandomAccessFile file = 
     new RandomAccessFile(filePath, "r")) {
    byte[] bytes = new byte[size];;
    return new String(bytes); // encoding?? ouch!

Over this one?

List<String> lines = FileUtils.readLines(file);

But forget the latter. You can now use the new methods in java.nio.file.Files, e.g.

List<String> lines = Files.readAllLines(path);

No need for third-party libraries any longer!


Throw it all out, for there is JEP 154 deprecating serialisation. Well, it wasn’t accepted, but we could’ve surely removed about 10% of our legacy codebase.

A variety of concurrency APIs and helpers

With JEP 155, there had been a variety of improvements to concurrent APIs, e.g. to ConcurrentHashMaps (we’ve blogged about it before), but also the awesome LongAdders, about which you can read a nice article over at the Takipi blog.

Haven’t I seen a whole package over at Guava, recently? Probably not needed anymore.

JEP 154 (Serialisation) wasn’t real

It was an April Fools’ joke, of course…

Base64 encoders

How could this take so long?? In 2003, we’ve had RFC 3548, specifying Base16, Base32, and Base64 data encodings, which was in fact based upon base 64 encoding specified in RFC 1521, from 1993, or RFC 2045 from 1996, and if we’re willing to dig further into the past, I’m sure we’ll find earlier references to this simple idea of encoding binary data in text form.

Now, in 2014, we finally have JEP 135 as a part of the JavaSE8, and thus (you wouldn’t believe it): java.util.Base64.

Off to the trash can with all of these libraries!

… gee, it seems like everyone and their dog worked around this limitation, prior to the JDK 8…


Provide your suggestions in the comments! We’re curious to hear your thoughts (with examples!)


As any Java major release, there is a lot of new stuff that we have to learn, and that allows us to remove third-party libraries. This is great, because many good concepts have been consolidated into the JDK, available on every JVM without external dependencies.

Disclaimer: Not everything in this article was meant seriously. Many people have created great pieces of work in the past. They have been very useful, even if they are somewhat deprecated now. Keep innovating, guys! :-)

Want to delve more into the many new things Java 8 offers? Go have a look over at the Baeldung blog, where this excellent list of Java 8 resources is featured:

Java 8

… and stay tuned for our next Java 8 Friday blog post, next week!