Recently, I've encountered this sort of query all over the place at a customer site: DECLARE v_var NUMBER(10); BEGIN SELECT COUNT(*) INTO v_var FROM table1 JOIN table2 ON table1.t1_id = table2.t1_id JOIN table3 ON table2.t2_id = table3.t2_id ... WHERE some_predicate; IF (v_var = 0) THEN do_something ELSE do_something_else END IF; END; Unfortunately, COUNT(*) is often … Continue reading SQL Tip of the Day: Be Wary of SELECT COUNT(*)
Tag: sql
Join the No OFFSET Movement!
Markus Winand from Use The Index, Luke! did it again. He started an exciting battle against one the biggest flaws in the SQL language: We've blogged about this before. OFFSET pagination is terribly slow, once you reach higher page numbers. Besides, chances are, that your database doesn't even implement it correctly, yet (and your emulation … Continue reading Join the No OFFSET Movement!
Are You Using SQL PIVOT Yet? You Should!
Every once in a while, we run into these rare SQL issues where we'd like to do something that seems out of the ordinary. One of these things is pivoting rows to columns. A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table: +------+------------+----------------+-------------------+ | dnId | propNameId | … Continue reading Are You Using SQL PIVOT Yet? You Should!
Using Oracle AQ in Java Won’t Get Any Easier Than This
Starting with jOOQ 3.5 we've included an awesome new feature for those of you using the Oracle database: Native support for Oracle AQ! And your client code will be so easy to write, you'll be putting those AQs all over your database immediately. How does it work? jOOQ rationale The biggest reason why many of … Continue reading Using Oracle AQ in Java Won’t Get Any Easier Than This
jOOQ Tip of the Day: Reuse Bind Values
jOOQ implements your SQL statements as AST (Abstract Syntax Tree). This means that your SQL statement is modelled in a non-text form prior to serialising it as a textual SQL statement to your JDBC driver. One advantage of this is that you can freely manipulate this AST any way you want. This can be done … Continue reading jOOQ Tip of the Day: Reuse Bind Values
Top 10 Very Very VERY Important Topics to Discuss
Some things are just very very very VERY very important. Such as John Cleese. The same is true for Whitespace: Whitespace-Shedding Yes. 1080 Reddit Karma points (so urgently needed!) in only 23 hours. That's several orders of magnitudes better than any of our - what we wrongfully thought to be - very deep and interesting … Continue reading Top 10 Very Very VERY Important Topics to Discuss
Frightening Facts about MySQL
So you might've seen Destroy all Software's talk about JavaScript: Here's a similar talk (less funny more scary) about MySQL: https://www.youtube.com/watch?v=emgJtr9tIME
Logon Triggers: The Oracle Database Magic Bullet
Imagine you want to collect detailed usage statistics to tune your Oracle database, e.g. if you want to have A-Rows and A-Time values in your execution plans (by default, Oracle only reports E-Rows and E-Time with "E" for "Estimated". But usually, you will care more about the "A" for "Actual"). All you have to do … Continue reading Logon Triggers: The Oracle Database Magic Bullet
Java 8 Friday: More Functional Relational Transformation
In the past, we've been providing you with a new article every Friday about what's new in Java 8. It has been a very exciting blog series, but we would like to focus again more on our core content, which is Java and SQL. We will still be occasionally blogging about Java 8, but no … Continue reading Java 8 Friday: More Functional Relational Transformation
PostgreSQL’s Table-Valued Functions
Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write: CREATE OR REPLACE FUNCTION f_1 (v1 INTEGER, v2 OUT INTEGER) AS $$ BEGIN v2 := v1; END $$ LANGUAGE plpgsql; ... and believe it … Continue reading PostgreSQL’s Table-Valued Functions