Auto-Creation of Indexes in RDBMS

[…] generally speaking, I’m also surprised to see that in 2013 we’re creating our indexes manually.

Interesting thought! Has this thought ever occurred to you?

How this comment came about

Hackernews is very predictable. Our latest pro-SQL marketing campaign for jOOQ got quite a bit of traction as expected. It is easy to trigger love and hate for NoSQL databases with a little bit of humour, such as with Mark Madsen’s “history of databases in no-tation”.

A much more interesting and more serious blog post is Doug Turnbull’s “Codd’s Relational Vision – Has NoSQL Come Full Circle?”, which we are going to blog about soon, in a separate post. We’ve also put the latter on Hackernews and on Reddit, both of which generated tremendous traction for the subject. Comparing the current state of “NoSQL” with pre-Codd, pre-relational, pre-SQL times is clever and matches Michael Stonebraker’s and Joseph M. Hellerstein’s observations in “What Goes Around Comes Around”.

NoSQL is a movement that emerged out of necessity, when SQL databases did not evolve fast enough to keep up with what keen observers and Gartner now call “Webscale”, a new buzzword to name old things. But as history has shown, the old elephants can be taught new tricks, and SQL databases will eventually catch up.

Auto-creation of indexes in RDBMS

In the middle of the above Hackernews discussion, MehdiEG made this interesting observation about creating indexes manually being tedious. Indeed, why do we have to maintain all of our indexes manually? While platforms like Use-The-Index-Luke.com profit from teaching people how to do proper indexing, I wonder if a highly sophisticated database couldn’t gather statistics about a productive system and then generate suggestions for index additions / removals. Even more so, if the database is “absolutely sure”, it could also create/drop or at least activate/deactivate relevant indexes.

What does “absolutely sure” mean?

The Oracle database for instance, is already quite good at gathering relevant statistics and giving DBA hints about potentially effective new indexes, as it can simulate execution plans in case indexes were added. Some more information can be seen in this Stack Overflow question.

But wouldn’t it be great if Oracle (or SQL Server, DB2, any other database) had an auto-index-creation feature? On a productive system, the database could gather statistics for the longest-running queries, analyse their execution plans, simulate alternative execution plans in case potentially useful indexes were added to improve SELECT statements, or removed to improve INSERT, UPDATE, DELETE, MERGE statements. This wouldn’t be a simple task, as all available (or at least the 100 most executed) execution plans would have to be re-calculated to see how the newly added or removed index would impact the productive system.

There are a couple of things to note here:

  1. Fine-tuning indexing is easiest on a productive system. If you’re tuning your development environment, you will get most of the cases right. But only the productive system will show all those weird edge-cases that you simply cannot foresee
  2. Analysing the productive system is hard and is usually performed by the devops team or the DBA team. They’re often not the same people as the ones who developed the application / database. Since they often cannot access the DML or DDL of the application, it’s always good if they have some automatic tuning features such as the existing cost-based optimiser
  3. Blindly adding indexes without measuring is bad practice. If you know that a table is mostly-read-only, then you’re mostly-on-the-safe-side. But what happens if a table is often bulk updated? If a batch job creates large transactions with long UNDO / REDO logs? Each unnecessary index will only slow down the batch job, increasing the risk of race conditions, rollbacks or even deadlocks.

Automatic index creation or deletion could greatly improve the productive experience with commercial databases that already have many very useful tuning features. Let us hope that Oracle, IBM, Microsoft will hear us and build such a feature into their future databases!

SQL is the new NoNoSQL

You have probably been wondering about what SQL is. Some also refer to it as NoNoSQL. See a comprehensive comparison about SQL vs. NoSQL on our new website here:

http://www.nosql-vs-sql.com

Free Open Source with Commercial Support

Have you ever thought about “Free” Open Source with commercial support vs. commercial software? We have and we found that the following is true:

Free Open Source Software with commercial support

… is the best business model for companies selling services for very complex OSS software where customers might lack skilled personnel. This includes things like

The above systems can cause terrible pain to your productive environments when they crash. You want to have extremely skilled fire fighters ready when your system goes down. Some of the above systems have been said to be deliberately complex in order to be able to pursue this particular business model.

Commercial software or dual-licensed software

… is the best business model for companies selling very simple, easy to use software where customers do not rely on skilled personnel in the event of a crash. This is particularly true for

The above software are so easy to handle and to maintain, their vendors would hardly make any money with support. Granted, support is always included in the license fees. That’s just part of the service quality.

jOOQ Newsletter December 13, 2013

subscribe to the newsletter here

A jOOQ Runtime Only Distribution

Several of our customers have made us aware of the fact that they don’t really need the jOOQ code generator, only the SQL builder / query DSL API and possibly the SQL execution functionality. In the next weeks, we’ll be working towards a new “jOOQ Runtime Only Distribution” that will ship at a lower price. Concretely, we’ll be giving those customers a 25% discount on the regular distributions for any of jOOQ Express, jOOQ Professional, and jOOQ Enterprise.

Obviously, we recommend to use jOOQ’s code generator to profit from the full feature scope that we offer.

License Amendments

We have updated our commercial license to improve your legal relationship with Data Geekery. Essentially, these things have been amended:

  • Definitions: We have added the missing definition of what is a “Minor Defect”
  • 6.2 Distribution Right: The distribution right section now grants you a perpetual license to distribute the software, instead of a timely limited one. This will allow you to continue to distribute, embed and use jOOQ in your End-user Application even if you terminate your Developer Workstation License Agreement with us. You will, however, still need to license jOOQ in order to maintain your End-user Application.
  • 7.1.1 Remedial Services: The remedial services section now formally defines how Minor Defects are remedied

As this updated license grants new rights to our customers, it shall be in effect immediately also for existing jOOQ 3.2 customers.

Upcoming Events

We have recently presented jOOQ at the Java2Days conference in Sofia, Bulgaria as well as at the Java User Group Berlin-Brandenburg. Both talks have enjoyed a high attendance with lots of SQL developers challenging us with interesting questions. Of course, jOOQ responds to most questions already, as Manuel Bernhardt, another speaker has observed:

If you want us to talk about jOOQ or SQL near you, do not hesitate to contact us.

Here is an overview of other, upcoming events:

Stay informed about 2014 events on www.jooq.org/news. If you’re looking for the slides, they’re available for free under a CC BY-SA 3.0 license on SlideShare.

Using jOOQ with Hibernate

While jOOQ can be seen as a popular alternative to Hibernate, there is no stopping you from using both frameworks in the same application. While Hibernate heavily improves every day CRUD operations, jOOQ heavily improves writing SQL in Java. The two goals can be orthogonal and thus it may make perfect sense to combine the two.

Vlad Mihalcea, an enthusiastic jOOQ user, is writing up a series of blog posts on his blog about how to use jOOQ with Hibernate:

Gavin King himself chimed in on Google+ and confirmed once again that it was never his intention for Hibernate to be used for everything.

If you want to share your own experience of your jOOQ / Spring, jOOQ / Hibernate, jOOQ / Anything integration, let us know!

SQL Zone – “Lightning Fast SQL with Proper Indexing”

Understanding SQL and the history of SQL is of the essence when you want to get the best out of your relational database. This has recently been nicely explained by Markus Winand at the Oredev conference 2013. Luckily, his presentation has been recorded for free review.

More about the history of SQL can be seen in this interesting article about Codd’s Relational Vision – Has NoSQL Come Full Circle? It proves what we have been evangelising for a while, ourselves. NoSQL is not a new technology. It is more of a return to pre-Codd times when people did not have a powerful relational model to abstract their storage implementation away from their application. This article claims that Codd’s greatest achievement was the fact that he surpassed the deficiencies of early databases:

  • Access dependencies
  • Order dependencies
  • Index dependencies

Read the full article for a very interesting historic insight into the relational model, and why NoSQL might not be a good answer for most general problems.

How to Integrate jOOQ with Hibernate

While jOOQ can be an alternative to Hibernate, it doesn’t have to entirely replace Hibernate. Many users have reported positive experiences when combining jOOQ with Hibernate, letting Hibernate do the tedious CRUD work, and jOOQ the complex querying and reporting through its sophisticated, yet intuitive query DSL.

Vlad Mihalcea who has been blogging interesting stuff about SQL and transaction models recently has now published a very nice tutorial on how to use JPA-annotated entities as a grounds for source code generation in jOOQ, through the following steps:

  • Write the annotated entities
  • Generate HSQLDB DDL from those entities
  • Execute the DDL in an HSQLDB instance
  • Run the jOOQ code generator to reverse-engineer the schema

With the above, he’s ready to query entities through JPA/Hibernate or jOOQ in no time.

Read the full blog post here:
http://vladmihalcea.wordpress.com/2013/12/06/jooq-facts-from-jpa-annotations-to-jooq-table-mappings/

When Source Code Comments Indicate Trouble

Developers have their own, cynical kind of humour. Consider, for instance, Geek and Poke’s view on how to insult a developer.

But there’s a better humour than posting stuff on a website. There are source code comments. Because you can giggle now, write your witty remark, and wait for your coworkers to discover your sense of humour only 3-4 years later (when you’re long gone, working in another project, department, or company). Isn’t that the ultimate way to express cynicism?

For instance, I once encountered this fat piece of Javadoc in a previous employer’s legacy source code:

/**
 * NEVER EVER TOUCH THIS METHOD OR EVERYTHING
 * WILL EXPLODE!!!!!
 */

But the bug was in that method! A monster with around 500 lines and 80 characters of indentation! So I went and first fixed the Javadoc:

/**
 * NEVER EVER TOUCH THIS METHOD OR EVERYTHING
 * WILL EXPLODE!!!!!
 * ^^^^^^^^^^^^^^^^^
 * Chicken!
 */

If you have a couple of spare minutes (or hours) to read up other witty and cynical comments, dive into this awesome Stack Overflow question featuring the top 549 pieces of comment in code. An extract by Steve Weet.

I once came up with what I thought was an elegant solution to a particularly sticky problem, in retrospect it was a bit of a mind-bender and made some heavy use of macro programmimg. Years later I found this comment from a maintenance programmer

/*
The Total Perspective Vortex derives its picture of
the whole Universe on the principle of extrapolated
matter analyses.

To explain - since every piece of matter in the
Universe is in some way affected by every other
piece of matter in the Universe, it is in theory
possible to extrapolate the whole of creation -
every sun, every planet, their orbits, their
composition and their economic and social history
from, say, one small Macro.

The man who invented the Total Perspective Vortex
did so basically in order to annoy the IT
department.

Steve Weet - for that was his name - was a dreamer,
a thinker, a speculative philosopher or, as some
would have it, a slacker.

And they would nag him incessantly about the
utterly inordinate amount of time he spent staring
out into space, or mulling over the mechanics of
Chelsea FC, or doing spectrographic analyses of
macros.

"Have some sense of proportion!" they would say,
sometimes as often as thirty-eight times in a
single day.

And so he built the Total Perspective Vortex - just
to show them.

And into one end he plugged the whole of reality as
extrapolated from one macro, and into the other
end he plugged the IT department: so that when he
turned it on they saw in one instant the whole
infinity of creation and theirselves in relation to
it.

To Steve Weet's horror, the shock completely
annihilated their brains; but to his satisfaction
he realized that he had proved conclusively that if
life is going to exist in a Universe of this size,
then the one thing it cannot afford to have is a
sense of proportion.
*/

Now go on and read the whole thing!

Top 10 Ceylon Language Features I Wish We Had In Java

What does one do when Hibernate is “finished” and feature complete and one needs new challenges? Right. One creates a new JVM language called Ceylon.

On November 12, 2013, Ceylon 1.0.0 was finally released and we congratulate the whole team at Red Hat for their achievements in what looks like a very promising new JVM language. While it will be a slight challenge for Ceylon to compete with Scala, there are lots of very interesting features that distinguish it.

In fact, this language has so many interesting features, it’ll be hard to write up a blog post about the 10 most interesting ones. Which ones to choose? On Google Plus, I’ve had a short chat with Gavin King who also brought us Hibernate, Ross Tate who is also involved with JetBrains’ Kotlin, and Lukas Rytz who was a PhD student and committer for EPFL’s Scala and now works at Google Dart. I wanted those language Uberdesigners to help me find the 10 most thrilling language features that they have and we Java developers don’t. Now I have 20 interesting ones. I’ll certainly write a follow-up post to this one.

I have observed Gavin King and the other guys to be very enthusiastic and knowledgeable. I’ve already had this impression before when I first heard about Ceylon from Stéphane Épardaud at the JUGS in Berne, Switzerland in February 2013, another one of RedHat’s passionate engineers (see his presentation’s slides here).

Anyway, enough of the who’s who. Here’s our personal Top 10 List of Ceylon Language Features I Wish We Had In Java:

1. Modules

In Java, Jigsaw has been postponed about 34 times and we’re only now closing in on Java 8 GA! Yes, we have OSGi and Maven, and both work very well to manage dependencies at runtime (OSGi) or at compile-time (Maven). But compare this black magic Maven/OSGi configuration using Apache Felix

<plugin>
  <groupId>org.apache.felix</groupId>
  <artifactId>maven-bundle-plugin</artifactId>
  <version>2.1.0</version>
  <extensions>true</extensions>
  <executions>
    <execution>
      <id>bundle-manifest</id>
      <phase>process-classes</phase>
      <goals>
        <goal>manifest</goal>
      </goals>
    </execution>
  </executions>
  <configuration>
    <supportedProjectTypes>
      <supportedProjectType>
        jar
      </supportedProjectType>
    </supportedProjectTypes>
    <instructions>
      <Bundle-SymbolicName>
        org.jooq
      </Bundle-SymbolicName>
      <Export-Package>*</Export-Package>
      <Import-Package>
        javax.persistence;resolution:=optional,
        org.apache.log4j;resolution:=optional,
        *
      </Import-Package>
      <_versionpolicy>
        [$(version;==;$(@)),$(version;+;$(@)))
      </_versionpolicy>
    </instructions>
  </configuration>
</plugin>

… with this one by Ceylon:

"The second best ever ORM solution!"
license "http://www.gnu.org/licenses/lgpl.html"
module org.hibernate "3.0.0.beta" {
    import ceylon.collection "1.0.0";
    import java.base "7";
    shared import java.jdbc "7";
}

Finally, things can be controlled on a jar-level, including visibility of packages. With only few lines of code. Please, Java, integrate Ceylon’s powerful module support.

It may be worth mentioning that Fantom is another language with integrated module support. See JodaTime’s Stephen Colebourne’s talk at Devoxx 2011: “Is Fantom Light Years Ahead of Scala?”. Stephen has also brought us ElSql, a new external SQL DSL for Java templating.

2. Sequences

This is the first time I’ve seen this kind of first class support for sequences in a typesafe language. Not only does Ceylon ship with all sorts of collection literals, it also knows types for these constructs. Concretely, you can declare an Iterable as such:

{String+} words = { "hello", "world" };

Notice the notation of the literal. It is of type {String+}, meaning that it contains at least one element. The type is assignment-compatible with {String*}, which represents a possibly empty sequence. Very interesting.

This goes on by supporting array literals as such:

String[] operators = [ "+", "-", "*", "/" ];
String? plus = operators[0];
String[] multiplicative = operators[2..3];

… or tuple literals:

[Float,Float,String] point = [0.0, 0.0, "origin"];

Notice also the range literal 2..3 which allows for extracting sub-arrays from the original array. So much sequence goodness in Ceylon!

Notice also the question mark in String?, which is Ceylon’s way of declaring …

3. Nullable types

While Scala knows the Option type and Haskell knows the Maybe type and Java 8 tries to compete by adding the new, unenforceable Optional type, Ceylon has a very simple notion of something that is nullable. If there’s a question mark behind a type, it’s nullable. Otherwise, it’s not null. Always.

In order to convert a nullable type into a not nullable type, you have to explicitly check:

void hello() {
    String? name = process.arguments.first;
    String greeting;
    if (exists name) {
        greeting = "Hello, ``name``!";
    }
    else {
        greeting = "Hello, World!";
    }
    print(greeting);
}

Notice the exists operator. It defines a new scope within which the name variable is known to be not null, i.e. it is promoted from String? to String. This locally scoped type promotion is commonly referred to as flow-sensitive typing, which has already been observed in the Whiley language, according to Lukas Rytz.

If you omit the exists check, you’d get a compilation error on that string interpolation there. There are also other useful constructs to perform ad-hoc type conversions:

String greeting = "Hello, " + (name else "World");

The else clause acts like a SQL COALESCE() function and can even be chained. Read more about Ceylon’s nullable goodness.

4. Defaulted parameters

OMG, how I wish we had that in Java. Every time we overload methods, we think, why not just support defaulted parameters like PL/SQL, for instance??

void hello(String name="World") {
    print("Hello, ``name``!");
}

I cannot think of a single good reason why languages wouldn’t have named and defaultable parameters like PL/SQL:

-- One of the parameters is optional
CREATE PROCEDURE MY_PROCEDURE (
  P1 IN NUMBER,
  P2 IN VARCHAR2 := 'ABC',
  P3 IN VARCHAR2
);

-- Calling the procedure
MY_PROCEDURE(
  P1 => 1,
  P3 => 'XYZ'
);

So this is one way to circumvent method overloading in most common cases. Method overloading is still tedious when we want to deal with alternative, incompatible types. But not in Ceylon, as Ceylon knows …

5. Union types

OK, this is a bit esoteric. The creators of Ceylon really really wanted to get rid of method overloading, partially because Ceylon also compiles to JavaScript, and JavaScript does not know function overloading. In fact, it is not possible to overload methods in Ceylon at all. To be able to interoperate with Java, however, union types needed to be introduced. A union type String|Integer can be either a String or an Integer. There’s method overloading right there!

void printType(String|Integer|Float val) { ... }
 
printType("hello");
printType(69);
printType(-1.0);

In order to “untangle” the union type, you can again take advantage of flow-sensitive typing for the val parameter by performing type-checks similar to Java’s instanceof

void printType(String|Integer|Float val) {
    switch (val)
    case (is String) { print("String: ``val``"); }
    case (is Integer) { print("Integer: ``val``"); }
    case (is Float) { print("Float: ``val``"); }
}

Within that scope, val is known to the compiler to be of type String, for example. This goes on to allowing crazy stuff like enumerated types where a type can be one or another thing, simultaneously:

abstract class Point()
        of Polar | Cartesian {
    // ...
}

Note that this is very different from multiple inheritance where such a Point would be both Polar and Cartesian. But that’s not all. Ceylon also has …

6. Intersection types

Now, as you may have guessed, that’s the exact inverse of a union type, and this is actually also supported by Java’s generics. In Java, you can write:

class X<E extends Serializable & Comparable<E>> {}

In the above example, X accepts only type parameters that are both Serializable and Comparable. This is much crazier in Ceylon where you can assign values to a locally declared intersection type. And that’s not it! In our chat, Gavin has pointed out this incredible language feature to me, where union / intersection types can interact with flow-sensitive typing to form the following (due for Ceylon 1.2):

value x = X();
//x has type X
if (something) {
    x = Y();
    //x has type Y
}
//x has type X|Y

Makes sense, right? So I asked him, if I will be able to intersect that type again with Z and Gavin said, yes! The following can be done:

value x = X();
//x has type X
if (something) {
    x = Y();
    //x has type Y
}
//x has type X|Y
if (is Z x) {
    //x has type <X|Y>&Z
}

And this goes on, because type intersections also interact with generics in a very interesting way. Under certain circumstances, X<A>&X<B> can be the same as X<A&B>. In other words, intersections (and unions) are distributive with generics, just like additions are with multiplications (in an informal understanding of “just like”). If you’re willing to delve into the language spec for this, see §3.7.2 Principal instantiation inheritance.

Now, union and intersection types can get quite nasty und hard to reuse. This is why Ceylon has …

7. Type aliases

Is there any other programming language that ever thought of this awesome feature?? This is so useful, even if you’re not supporting union and/or intersection types. Think about Java’s generics. With the advent of generics, people started writing stuff like:

Map<String, List<Map<Integer, String>>> map = // ...

Two things can be said:

  • Generics are extremely useful to the Java libraries
  • Generics become extremely verbose when doing the above

Here’s where type aliases come into play. Check out this example:

interface People => Set<Person>;

The point here is that even if some verbose types are reused very often, you don’t often want to create an explicit subtype for the above. In other words, you don’t want to abuse subtype polymorphism as a shortcut to “simplify” generic polymorphism.

Think of aliases as an expandable macro, which is mutually assignment-compatible. In other words, you can write:

People?      p1 = null;
Set<Person>? p2 = p1;
People?      p3 = p2;

So as the term “alias” suggests, you’re not creating a new type. You’re just giving a complex type a simpler name. But even better than type aliasing is …

8. Type inference

Many other languages have this and so does Java to a certain extent, at least as far as generics are involved. Java 8 goes one step further in allowing type inference with generics. But Java is far away from what languages like Scala or Ceylon can do with local variables:

interface Foo {}
interface Bar {}
object foobar satisfies Foo&Bar {}
//inferred type Basic&Foo&Bar
value fb = foobar; 
//inferred type {Basic&Foo&Bar+}
value fbs = { foobar, foobar };

So, this example shows a lot of features combined, including type constraints, sequence types, union types. With such a rich type system it is very important to support this level of type inference where a value keyword indicates that you don’t want to (or you cannot) explicitly declare a type. This, I’d really love to see in Java 9!

Read more about Ceylon’s awesome type inference capabilities.

9. Declaration-site variance

Now, this feature might be a bit harder to understand, as Java’s generics are already quite difficult to understand. I’ve recently read a very interesting paper by Ross Tate, Alan Leung and Sorin Lerner about the challenges brought to Java generics through wildcards: Taming Wildcards in Java’s Type System. Generics are still a very active research topic neither researchers nor language designers completely agree on whether use-site variance (as in Java) or declaration-site variance (as in C#, Scala, or Ceylon) is really better for mainstream programmers. Older languages talking about variance are Eiffel and OCaml.

Microsoft has introduced declaration-site variance in C#. I’ll cite the example from Wikipedia, which is very easy to understand. In C#, the IEnumerator interface has a covariant generic type parameter:

interface IEnumerator<out T>
{
    T Current { get; }
    bool MoveNext();
}

This simply means that the following will work:

IEnumerator<Cat> cats = ...
IEnumerator<Animal> animals = cats;

This is quite different from Java’s use-site variance, where the above wouldn’t compile, but the following would:

Iterator<Cat> cats = ...
Iterator<? extends Animal> animals = cats;

The main reason for declaration-site covariance is the simple fact that verbosity is greatly reduced at the use-site. Wildcards are a major pain to Java developers and they lead to numerous Stack Overflow questions as this one, which is about locally scoped wild-cards:

// Given this interface:
public interface X<E> {
    E get();
    E set(E e);
}

// This does not compile:
public void foo(X<?> x) {
    x.set(x.get());
}

As can be seen in the Ceylon language tour, Ceylon generics support declaration-site variance, just like C# and Scala. It will be interesting to see how these things evolve, as both types of variance support have their pros and cons, while at the same time, Ross Tate advocates mixed-site variance, which would really be a great addition for the Java language!

Now this was a bit complex, so let’s have a look at a simpler, yet awesome feature to round things up …

10. Functions and methods

One of the main things outlined by Stéphane Épardaud was the fact that the Ceylon language is a very regular language. This is particularly apparent when considering how Ceylon treats functions (and methods, which are type member functions). I can put a function everywhere. Consider this example:

Integer f1() => 1;
class C() {
    shared Integer f2() {
        Integer f3() => 2;
        return f3();
    }
}

print(f1());
print(C().f2());

In the above example,

  • f1() is a package-level function (much like a “global” static function in Java)
  • f2() is a regular method on the C class
  • f3() is a local function within the f2() method

With Java 8’s support for lambda expressions, these things get a bit better, but isn’t it awesome to be able to declare functions anywhere, in almost the same syntax?

Conclusion: Play around with Ceylon

That’s it for now. We might be publishing a follow-up article about the more esoteric language features in Ceylon, some time soon. In any case, you can download this interesting JVM language for free with first-class IDE support in Eclipse. You can also visit the Ceylon documentation website and have their website compile Ceylon code into JavaScript for execution in your browser.

Visit the Community and interact with the language designers from RedHat and Serli, and when you’re done, share this post on our jOOQ blog and help the JCP recognise that this wonderful language has a couple of very interesting features to put on the Java 9 or 10 roadmap!