A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges

I'm frequently telling developers to put window functions almost everywhere, because they're so awesome! One feature that I rarely see in the wild (even if it is extremely useful for reporting) is called "logical windowing" in Oracle, and it's most useful when used with INTERVAL ranges. Let's see what we may want to do. I … Continue reading A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges

Don’t Even use COUNT(*) For Primary Key Existence Checks

In a recent blog post, I've advocated against the use of COUNT(*) in SQL, when a simple EXISTS() would suffice. This is important stuff. I keep tuning productive queries where a customer runs a COUNT(*) query like so: SELECT count(*) INTO v_any_wahlbergs FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = 'WAHLBERG' ... … Continue reading Don’t Even use COUNT(*) For Primary Key Existence Checks

How to Quickly Rename all Primary Keys in Oracle

Are you working with someone else's schema and they haven't declared nice names for all their constraints? Unfortunately, it is all too easy to create a table like this: CREATE TABLE order1 ( order_id NUMBER(18) NOT NULL PRIMARY KEY ); Or like this: CREATE TABLE order2 ( order_id NUMBER(18) NOT NULL, PRIMARY KEY (order_id) ); … Continue reading How to Quickly Rename all Primary Keys in Oracle

Be Careful When Emulating Parameterised Views with SYS_CONTEXT in Oracle

Everyone who writes complex SQL (or dare I say, move business logic into the database?) wishes for parameterised views from time to time. This hypothetical syntax would be terrific: CREATE VIEW v_categories_per_actor( p_actor_id NUMBER ) AS SELECT DISTINCT c.name FROM category c JOIN film_category fc USING (category_id) JOIN film_actor fa USING (film_id) WHERE fa.actor_id = … Continue reading Be Careful When Emulating Parameterised Views with SYS_CONTEXT in Oracle

jOOQ Tuesdays: Daniel Dietrich Explains the Benefits of Object-Functional Programming

Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. I'm very excited to … Continue reading jOOQ Tuesdays: Daniel Dietrich Explains the Benefits of Object-Functional Programming

Does Your Database Really Use Your Index?

Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database - as with each write to the table, the index needs to be updated as well in the same transaction. … Continue reading Does Your Database Really Use Your Index?

Why You Should Design Your Database to Optimise for Statistics

In my SQL Masterclass, I frequently remind participants of the fact how important statistics are for a modern cost based optimiser. For instance, if you consider the fact that in an average E-Banking system's bookings table, you will probably have a transaction amount histogram like the following: In other words, most of your transactions are … Continue reading Why You Should Design Your Database to Optimise for Statistics