What’s Faster? COUNT(*) or COUNT(1)?

One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there's really no reason at all why one should be faster than the other. But is the myth justified? Let's measure! How does COUNT(...) work? But … Continue reading What’s Faster? COUNT(*) or COUNT(1)?

Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER

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

How to Fetch All Current Identity Values in Oracle

Oracle 12c has introduced the useful SQL standard IDENTITY feature, which is essentially just syntax sugar for binding a sequence to a column default. We can use it like this: create table t1 (col1 number generated always as identity); create table t2 (col2 number generated always as identity); insert into t1 values (default); insert into … Continue reading How to Fetch All Current Identity Values in Oracle

Using IGNORE NULLS With SQL Window Functions to Fill Gaps

I found a very interesting SQL question on Twitter recently: https://twitter.com/vikkiarul/status/1120669222672261120 Rephrasing the question: We have a set of sparse data points: +------------+-------+ | VALUE_DATE | VALUE | +------------+-------+ | 2019-01-01 | 100 | | 2019-01-02 | 120 | | 2019-01-05 | 125 | | 2019-01-06 | 128 | | 2019-01-10 | 130 | +------------+-------+ … Continue reading Using IGNORE NULLS With SQL Window Functions to Fill Gaps

Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL

One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function. The PL/SQL language already has support for boolean types. We can write: CREATE OR REPLACE FUNCTION … Continue reading Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL

Calculate Percentiles to Learn About Data Set Skew in SQL

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

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