I've recently discovered a rather significant performance issue on a productive Oracle 11g customer database. And I'm sure you have this issue too, which is why I'm documenting it here. This is a simplified representation of the setup at the customer site: ID PAYMENT_DATE TEXT ---------- ------------ ----------------------------------- 33803 21.05.16 DcTNBOrkQIgMtbietUWOsSFNMIqGLlDw... 29505 09.03.16 VIuPaOAQqzCMlFBYPQtvqUSbWYPDndJD... 10738 … Continue reading Why You Should (Sometimes) Avoid Expressions in SQL Predicates
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
When to Choose SQL and When to Choose NoSQL
Some people make architecture decisions purely based on the loudest consultant: https://twitter.com/gareth/status/778575385600688129 For most others, however, decisions are not as simple as this. For instance: When should we start evaluating NoSQL storage systems as an alternative to RDBMS? RDBMS as the hammer for all nails This question obviously assumes that you're starting out with an … Continue reading When to Choose SQL and When to Choose NoSQL
A Hidden jOOQ Gem: Foreach Loop Over ResultQuery
A recent question on Stack Overflow about jOOQ caught my attention. The question essentially asked: Why do both of these loops work? // With fetch() for (MyTableRecord rec : ctx .selectFrom(MY_TABLE) .orderBy(MY_TABLE.COLUMN) .fetch() // fetch() here ) { doThingsWithRecord(rec); } // Without fetch() for (MyTableRecord rec : ctx .selectFrom(MY_TABLE) .orderBy(MY_TABLE.COLUMN) // No fetch() here ) … Continue reading A Hidden jOOQ Gem: Foreach Loop Over ResultQuery