Saturday, September 15, 2018

CTE (with) in Oracle and Postgres

Major part of my career to the moment passed working with databases and mostly with Oracle and Postgres. Several years ago I was really surprised by the difference in handling CTE (common table expressions) in these databases. From that time I spread this knowledge and recreated a sample for it several times. The last one was this week and I decided to store it somewhere - this mostly dead blog seems like a good place ;-)
So here is a really simple case of using CTE on Oracle.

create table test_table(
  id int primary key,
  foo varchar2(10)

insert into test_table(id, foo)
select rownum, 'val'||rownum from dual
   connect by rownum<=100000;
explain plan for select * from (select * from test_table) sel where id=42;
select * from table(dbms_xplan.display);
--|   0 | SELECT STATEMENT            |               |     1 |    20 |     1   (0)| 00:00:01 |
--|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TABLE    |     1 |    20 |     1   (0)| 00:00:01 |
--|*  2 |   INDEX UNIQUE SCAN         | SYS_C00328137 |     1 |       |     1   (0)| 00:00:01 |

explain plan for with sel as (select * from test_table) select * from sel where id=42;
select * from table(dbms_xplan.display);
--|   0 | SELECT STATEMENT            |               |     1 |    20 |     1   (0)| 00:00:01 |
--|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TABLE    |     1 |    20 |     1   (0)| 00:00:01 |
--|*  2 |   INDEX UNIQUE SCAN         | SYS_C00328137 |     1 |       |     1   (0)| 00:00:01 |

You can see that from the resulting execution plan there is no difference between CTE and sub-select.
But here is similar sample on Postgres:

create table test_table(
  id serial primary key,
  foo text

insert into test_table(foo)
select 'val'||generate_series from generate_series(1, 100000);

explain analyze select * from (select * from test_table) as sel where id=42;
-- Index Scan using test_table_pkey on test_table  (cost=0.29..8.31 rows=1 width=12) (actual time=0.037..0.038 rows=1 loops=1)
-- Index Cond: (id = 42)
-- Planning time: 0.053 ms
-- Execution time: 0.048 ms

explain analyze with sel as (select * from test_table) select * from sel where id=42;
-- CTE Scan on sel  (cost=1541.00..3791.00 rows=500 width=36) (actual time=0.025..32.644 rows=1 loops=1)
-- Filter: (id = 42)
-- Rows Removed by Filter: 99999
-- CTE sel
-- ->  Seq Scan on test_table  (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.016..5.859 rows=100000 loops=1)
-- Planning time: 0.069 ms
-- Execution time: 35.305 ms
Difference is dramatic. Quoting Tom Lane (real Postgres expert) "CTEs act as optimization fences. This is a feature, not a bug. Use them when you want to isolate the evaluation of a subquery." But a case when CTE should be used in Postgres is a theme for another post (maybe, so you here in another 4 or 5 years :-))

Friday, August 14, 2015

Benchmarking an interview question

Author benchmarks a super-simple method with a dumb implementation from his recent job interview. Results of this benchmark contradict with assumptions. The process of benchmarking produces some public benefit.

Wednesday, March 5, 2014

Specifying the size for StringBuilder (jmh)

Several years ago I started doing some microbenchmarks. I don't think that I really progressed since than, but at least some errors became obvious. So I decided to take one of my old benchmarks and reimplement it using the JVM library. Here are the results that I got:

Benchmark                                    Mode   Samples         Mean   Mean error    Units
c.s.m.j.StringBuilderSize.expandingSize      avgt        15     7566.477      374.611    ns/op
c.s.m.j.StringBuilderSize.predefinedSize     avgt        15     5640.386      133.672    ns/op

And here is the code for it. (I have also posted some other jmh-based benchmarks in that repo).

Sunday, February 23, 2014

Freemarker loading taglibs from classpath

It is pretty common problem to try using various jsp taglibs from freemarker templates[1][2][3][4]. There is some support for this in freemarker, but this support is a bit ugly - it requires dancing with jar files like placing them in WEB-INF/lib folder and there is no easy way to use taglibs just from the classpath. At least - there was no such an easy way. After a day of debugging and investigation I figured the solution that works pretty good for me. The key is to override just the two methods in the ServletContext that is used by freemarker TagLibFactory. To do this I used standard dynamic proxy, but other solutions are possible two. After this taglibs can be referenced by their paths in the classpath - like:

<#assign security=JspTaglibs["/META-INF/security.tld"] />

for spring-security taglib instead of the usual

<#assign security=JspTaglibs[""] />

And the InvocationHandler that can be used to create the proxy for the ServletContext is here:

package com.sopovs.moradanen;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;

import javax.servlet.ServletContext;

public class ServletContextResourceHandler implements InvocationHandler {
    private final ServletContext target;

    private ServletContextResourceHandler(ServletContext target) { = target;

    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        if ("getResourceAsStream".equals(method.getName())) {
            Object result = method.invoke(target, args);
            if (result == null) {
                result = ServletContextResourceHandler.class.getResourceAsStream((String) args[0]);
            return result;
        } else if ("getResource".equals(method.getName())) {
            Object result = method.invoke(target, args);
            if (result == null) {
                result = ServletContextResourceHandler.class.getResource((String) args[0]);
            return result;

        return method.invoke(target, args);

And the complete solution can be found in my pet-project here.

Sunday, February 16, 2014

System.arraycopy versus simple copy in a loop

Programming in Java daily I rarely need to use arrays. But sometimes this happens and even more often I trap into reading libraries code that heavily use arrays. So proper working with them is the real question for me. And recently I stumbled upon a code that used plain loops for copying values from one array to the other. So I decided to check whether this is viable option when we have System.arraycopy method. I wrote a simple benchmark using excellent JMH library.

And so what I have got as a result:

Benchmark                        Mode   Samples         Mean   Mean error    Units
c.s.m.j.ArrayCopy.loopcopy       avgt        15        1.463        0.160    ms/op
c.s.m.j.ArrayCopy.systemcopy     avgt        15        1.457        0.112    ms/op
c.s.m.j.ArrayCopy.loopcopy     sample     10322        1.450        0.018    ms/op
c.s.m.j.ArrayCopy.systemcopy   sample     11071        1.351        0.013    ms/op
c.s.m.j.ArrayCopy.loopcopy         ss        15        1.653        0.925       ms
c.s.m.j.ArrayCopy.systemcopy       ss        15        1.657        0.788       ms

So there is no real difference in hand-written loop and using native method. But I really doubt that even with these results we should ever prefer hand-written loop instead of System.arraycopy native method. Here is a short list of ad-hock parameters that may come into play:

  • Different CPU. I used the most powerful current desktop CPU for running by benchmark. Your code may be run on very different CPU with very different result - maybe even on differnt architecture. Who knows - maybe in just several years the major part of java application servers will be run on ARM-servers.
  • Different hardware cache usage. It is no doubt that System.arraycopy is optimized for the proper usage of CPU caches and not interfering with the other code running on the same CPU concurrently. For the loop - you cannot be absolutely sure.
  • Different JVM implementation. With only three major JVM-implementations currently - OpenJDK (but again - I doubt that ARM-version can be called absolutely the same JVM in this specific context), IBM J9 and Dalvik (not a JVM actually, but your library that uses arrays can easily get to running on it) - you cannot be sure in the JIT already. But also there are other JVMs, like Azul Zing, Azul Zuzu, Excelsior JET and many others.
So, nonetheless that I was not able to observe any difference in running hand-written loop and native built-in arraycopy method - I prefer the latter.

Friday, December 27, 2013

Learning Vaadin 7 review

Some time ago I was proposed to review the second edition of the "Learning Vaadin 7" (On Amazon) book. Shame on me for holding it for so long. Previously I have used Vaadin 6 and was really impressed by its features and simplicity of doing real desktop-like web-applications. But at the same time every now and then I was frustrated by some of the methods and mostly by the methods returning plain Objects. Fortunately now these type-safety problems are solved with the Vaadin 7.
But what about the book? It seems as really good introduction to Vaadin for junior developers:

  • It has a really extensive and balanced introduction that defines Vaadin place in the Java and Web ecosystems. Actually, I think that it is worth reading on its own.
  • It proceeds with detailed instructions on the development environment setup with a dive into production setup.
  • It is really detailed with all the basic and not so basic concepts of building Vaadin applications clearly explained.
The drawbacks of the book, that I can name are:
  • Examples use ant+ivy as a build system that seems less widespread to me, but it may have its benefits since it leaves much less area for "build magic" and gives more control and understanding of the build. Also information about Maven is given in a separate chapter.
  • Book seems like a one time reading for me. It can not be used as reference - but again, it is clearly stated that this is not a goal. Also it will motivate readers to search for information as it should be done after the book is read.
As an overall conclusion, I would not buy such book for myself, but I will probably recommend it to newbie developers.

Monday, November 25, 2013

Java8 HashMap is not compatible with Java7... in a way...

One of the things that shine in Java (not the only one!) is its performance. And it constantly improving. One of the performance improving features of Java8 is an improvement to HashMap and many related standard hashing collections. Actually from the title of this JEP (JDK Enhancement Proposal) "Handle Frequent HashMap Collisions with Balanced Trees" it is clear that this enhancement uses Comparable and Comparator functionality. If class do not implement Comparable interface or not Comparable with each other classes are stored in the map - Comparator based on the hashcode is used. But if class implements Comparable - this implementation is used.

So based on all this here is the very simple question. What does this code output on the JVM version 7 and on the JVM version 8?