I like weird, yet concise language constructs and API usages https://twitter.com/nipafx/status/1055451667079008256 Yes. I am guilty. Evil? Don't know. But guilty. I heavily use and abuse the java.lang.Boolean type to implement three valued logic in Java: Boolean.TRUE means true (duh) Boolean.FALSE means false null can mean anything like "unknown" or "uninitialised", etc. I know - a … Continue reading Imperative Loop or Functional Stream Pipeline? Beware of the Performance Impact!
Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector
All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP() VAR_SAMP() ARRAY_AGG() STRING_AGG() But what if you want to roll your own? Java 8 Stream Collector When using Java 8 streams, we can easily roll our own aggregate function (i.e. a … Continue reading Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector
How to Use SQL UPDATE .. RETURNING to Run DML More Efficiently
At a customer site, I recently refactored a "slow-by-slow" PL/SQL loop and turned that into an efficient set based UPDATE statement saving many lines of code and running much faster. In this blog post, I will show how that can be done. The blog post will focus on Oracle and UPDATE, but rest assured, this … Continue reading How to Use SQL UPDATE .. RETURNING to Run DML More Efficiently
How to Write a Multiplication Aggregate Function in SQL
Everyone knows the SQL SUM() aggregate function (and many people also know its window function variant). When querying the Sakila database, we can get the daily revenue (using PostgreSQL syntax): WITH p AS ( SELECT CAST (payment_date AS DATE) AS date, amount FROM payment ) SELECT date, SUM (amount) AS daily_revenue, SUM (SUM (amount)) OVER … Continue reading How to Write a Multiplication Aggregate Function in SQL
How to Reduce Syntactic Overhead Using the SQL WINDOW Clause
SQL is a verbose language, and one of the most verbose features are window functions. In a stack overflow question that I've encountered recently, someone asked to calculate the difference between the first and the last value in a time series for any given day: Input volume tstamp --------------------------- 29011 2012-12-28 09:00:00 28701 2012-12-28 10:00:00 … Continue reading How to Reduce Syntactic Overhead Using the SQL WINDOW Clause
Beware of Hidden PL/SQL to SQL Context Switches
I recently stumbled upon a curious query on a customer's productive Oracle database: SELECT USER FROM SYS.DUAL Two things caught my attention: The query was executed many billions of times per month, accounting for about 0.3% of that system's load. That's 0.3% for something extremely silly! I don't think that customer would ever qualify the … Continue reading Beware of Hidden PL/SQL to SQL Context Switches
Find the Next Non-NULL Row in a Series With SQL
I've stumbled across this fun SQL question on reddit, recently. The question was looking at a time series of data points where some events happened. For each event, we have the start time and the end time timestamp start end ----------------------------------- 2018-09-03 07:00:00 1 null 2018-09-03 08:00:00 null null 2018-09-03 09:00:00 null null 2018-09-03 10:00:00 … Continue reading Find the Next Non-NULL Row in a Series With SQL
A Frequent Question: Does jOOQ Have a First Level Cache?
One of the more frequent questions people have when switching from JPA to jOOQ is how to migrate from using JPA's first level cache? https://twitter.com/dmitrygusev/status/1034208767406043136 There are two important things to notice here: jOOQ is mainly used for what JPA folks call "projections" If you're using only JPA in your application, you may have gotten … Continue reading A Frequent Question: Does jOOQ Have a First Level Cache?
How to Write Multiset Conditions With Oracle VARRAY Types
Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: -- Nested tables CREATE TYPE t1 AS TABLE OF VARCHAR2(10); / -- Varrays CREATE TYPE … Continue reading How to Write Multiset Conditions With Oracle VARRAY Types
How SQL DISTINCT and ORDER BY are Related
One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly understand: SELECT DISTINCT length FROM film This returns results in an arbitrary order, because the database can (and might apply … Continue reading How SQL DISTINCT and ORDER BY are Related