B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I'll explain later why this is the case, but let's first learn how to detect "skew" What is skew? Skew is a term from statistics when a normal distribution is not symmetric. The example given … Continue reading Calculate Percentiles to Learn About Data Set Skew in SQL
Tag: Oracle
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
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
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
Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
One of Oracle 12c's coolest features was the introduction of the SQL standard OFFSET .. FETCH clause, as we can now write things like: SELECT * FROM film ORDER BY film_id FETCH FIRST 1 ROW ONLY This is querying the Sakila database. Most other databases had this clause (or a non-standard version of it) for … Continue reading Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC
When inserting records into SQL databases, we often want to fetch back generated IDs and possibly other trigger, sequence, or default generated values. Let's assume we have the following table: -- DB2 CREATE TABLE x ( i INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, j VARCHAR(50), k DATE DEFAULT CURRENT_DATE ); -- PostgreSQL CREATE TABLE … Continue reading How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC
The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating
Something that has been said many times, but needs constant repeating until every developer is aware of the importance of this is the performance difference between row-by-row updating and bulk updating. If you cannot guess which one will be much faster, remember that row-by-row kinda rhymes with slow-by-slow (hint hint). Disclaimer: This article will discuss … Continue reading The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating
The Cost of JDBC Server Roundtrips
Or: Move That Loop into the Server Already! This article will illustrate the significance of something that I always thought to be common sense, but I keep seeing people getting this (very) wrong in their productive systems. Chances are, in fact, that most applications out there suffer from this performance problem - and the fix … Continue reading The Cost of JDBC Server Roundtrips
How to Fetch Oracle DBMS_OUTPUT from JDBC
When working with Oracle stored procedures, it is not uncommon to have debug log information available from DBMS_OUTPUT commands. For instance, if we have a procedure like this: CREATE TABLE my_table (i INT); CREATE OR REPLACE PROCEDURE my_procedure (i1 INT, i2 INT) IS BEGIN INSERT INTO my_table SELECT i1 FROM dual UNION ALL SELECT i2 … Continue reading How to Fetch Oracle DBMS_OUTPUT from JDBC
JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage
The SQL language has one great advantage over procedural, object oriented, and "ordinary" functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute. How does … Continue reading JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage