Using the right data type for some calculation sounds like some obvious advice. There are many blogs about using temporal data types for temporal data, instead of strings. An obvious reason is data integrity and correctness. We don't gain much in storing dates as 2019-09-10 in one record, and as Nov 10, 2019 in the … Continue reading Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER
Tag: aggregation
How to Group By “Nothing” in SQL
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by "nothing" in SQL. E.g. when querying the Sakila database: SELECT count(*) FROM film GROUP BY () This will yield: count | ------| 1000 | What's the point, you're asking? Can't we just … Continue reading How to Group By “Nothing” in SQL
Correlated Subqueries are Evil and Slow. Or are They?
A common myth in SQL is the idea that correlated subqueries are evil and slow. For example, this query here: SELECT first_name, last_name, (SELECT count(*) FROM film_actor fa WHERE fa.actor_id = a.actor_id) FROM actor a It "forces" the database engine to run a nested loop of the form (in pseudo code): for (Actor a : … Continue reading Correlated Subqueries are Evil and Slow. Or are They?
How SQL GROUP BY Should Have Been Designed – Like Neo4j’s Implicit GROUP BY
In the recent past, we've explained the syntactic implications of the SQL GROUP BY clause. If you haven't already, you should read our article "Do You Really Understand SQL’s GROUP BY and HAVING clauses?". In essence, adding a GROUP BY clause to your query transforms your query on very implicit levels. The following reminder summarises … Continue reading How SQL GROUP BY Should Have Been Designed – Like Neo4j’s Implicit GROUP BY
How to Translate SQL GROUP BY and Aggregations to Java 8
I couldn't resist. I have read this question by Hugo Prudente on Stack Overflow. And I knew there had to be a better way than what the JDK has to offer. The question reads: I'm looking for a lambda to refine the data already retrieved. I have a raw resultset, if the user do not … Continue reading How to Translate SQL GROUP BY and Aggregations to Java 8
MongoDB “Lightning Fast Aggregation” Challenged with Oracle
What does "Scale" even mean in the context of databases? When talking about scaling, people have jumped to the vendor-induced conclusion that: SQL doesn't scaleNoSQL scales It is very obvious that NoSQL vendors make such claims. It has also been interesting that many NoSQL consumers made such claims, even if they probably confused SQL in … Continue reading MongoDB “Lightning Fast Aggregation” Challenged with Oracle