ANOTHER SQL Post this week? I got nerd-sniped: https://twitter.com/rafaelcodes/status/870574075244158980 Oooooh, challenge accepted! So, let's assume we have a table T with columns (A, B, C) like this: WITH t(a, b, c) AS ( SELECT 'a', 'b', null FROM dual UNION ALL SELECT 'a', null, 'c' FROM dual UNION ALL SELECT 'a', 'b', 'c' FROM dual … Continue reading How to Use SQL INTERSECT to Work Around SQL’s NULL Logic
Tag: Oracle
How to Find Redundant Indexes in SQL
The following two indexes are redundant in most SQL databases: CREATE INDEX i_actor_1 ON actor (last_name); CREATE INDEX i_actor_2 ON actor (last_name, first_name); It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is … Continue reading How to Find Redundant Indexes in SQL
How to Execute a SQL Query Only if Another SQL Query has no Results
I stumbled upon an interesting question on Stack Overflow recently. A user wanted to query a table for a given predicate. If that predicate returns no rows, they wanted to run another query using a different predicate. Preferably in a single query. Challenge accepted! Canonical Idea: Use a Common Table Expression We're querying the Sakila … Continue reading How to Execute a SQL Query Only if Another SQL Query has no Results
The Difficulty of Tuning Queries Over a Database Link – Or How I Learned to Stop Worrying and Love the DUAL@LINK Table
A large-ish customer in banking (largest tables on that particular system: ~1 billion rows) once decided to separate the OLTP database from the "log database" in order to better use resources and prevent contention on some tables, as the append-only log database is used heavily for analytic querying of all sorts. That seems to make … Continue reading The Difficulty of Tuning Queries Over a Database Link – Or How I Learned to Stop Worrying and Love the DUAL@LINK Table
How to Fetch Multiple Oracle Execution Plans in One Nice Query
When looking at execution plans in Oracle, we'll have to do several steps to be able to call the DBMS_XPLAN package functions. In fact, we have to find out the SQL_ID for a given statement first, and only then we can get its plan. I've blogged about this previously, here. However, thanks to lateral unnesting, … Continue reading How to Fetch Multiple Oracle Execution Plans in One Nice Query
How to Benchmark Alternative SQL Queries to Find the Fastest Query
Tuning SQL isn't always easy, and it takes a lot of practice to recognise how any given query can be optimised. One of the most important slides of my SQL training is the one summarising "how to be fast": Some of these bullets were already covered on this blog. For instance avoiding needless, mandatory work, … Continue reading How to Benchmark Alternative SQL Queries to Find the Fastest Query
Impress Your Coworkers With the Incredible NATURAL FULL OUTER JOIN!
There are already only very few real-world use-cases for FULL [ OUTER ] JOIN, but maybe, you have run into this beast in the past. But when was the last time you've seen a NATURAL JOIN? Right. A quick reminder from our article about JOINs: FULL JOIN A FULL JOIN is a type of OUTER … Continue reading Impress Your Coworkers With the Incredible NATURAL FULL OUTER JOIN!
Beautiful SQL: Lateral Unnesting of Array Columns
Sometimes, SQL can just be so beautiful. One of the less mainstream features in SQL is the array type (or nested collections). In fact, it's so not mainstream that only 2 major databases actually support it: Oracle and PostgreSQL (and HSQLDB and H2 in the Java ecosystem). In PostgreSQL, you can write: CREATE TABLE blogs … Continue reading Beautiful SQL: Lateral Unnesting of Array Columns
How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ
Earlier this week, I've blogged about how to execute SQL batches with JDBC and jOOQ. This was useful for the MySQL, SQL Server, and Sybase users among you. Today, we'll discuss a slightly more difficult task, how to fetch Oracle 12c implicit cursors - which are essentially the same thing. What's an implicit cursor? Oracle … Continue reading How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ
How to Emulate Partial Indexes in Oracle
A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called "filtered index"). That's an index that contains only "parts" of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: CREATE INDEX i ON message WHERE … Continue reading How to Emulate Partial Indexes in Oracle