Ein griffiges Beispiel für Lateral Inline Views

Auf den Lateral Join, der in 12c eingeführt wurde, habe ich hier schon vor geraumer Zeit hingewiesen - aber was damals fehlte, war ein griffiges Beispiel dafür, was man damit eigentlich anstellen kann. Das hat nun Oren Nakdimon in Teil 7 seiner überaus lesenswerten Serie Write Less with More ergänzt. In seinem Beispiel wird eine Projekt-Tabelle mit einer last_days_to_show_in_reports Spalte vorgestellt, in der für die einzelnen Einträge unterschiedliche Zeiträume angegeben sind (als Anzahl von Tagen). (more...)

Did it really fix it 1: OFE & nasty FIRST_ROWS plan on

Plan degradations on upgrade are normal.

This one’s no different.

On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.

First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.

What does FIRST_ROWS mean?
From 11g doco:

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

If any sort of (more...)

Pragma UDF – Some Current Limitations

There are currently some limitations to when pragma UDF will speed up your calls to PL/SQL functions.

In my post introducing the new pragma UDF feature of Oracle 12c I explained how it can be used to reduce the impact of context switching when you call a PL/SQL function from SQL.

In my example I showed how running a SQL-only SELECT statement that formatted a name for display over 100,000 records took 0.03 seconds (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist

If the inputs to the CBO change, the plan can change. For example, changes to bind variables can cause the plan to change when it is hard parsed. Therefore one should never be surprised when plans change.(read more)

SQL: Calculate the past time between two dates in percent

Just a simple example how easy APEX can handle this kind of problem.

We have today the 06.11.2015 (11-06-2015) and we have two date values 01.11.2015 and 30.11.2015.
Now I want to know how much time has past in percent since the beginning (01.11.2015):
Result: 17 %
Mehr »

SQL Tuning: Thinking in Sets / How and When to be Bushy

Below is a SQL statement from a performance problem I was looking at the other day.

This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.

You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)

Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL

A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.

We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.


Custom CSV Export as ZIP file

At the beginning of this year I wrote an article about "Custom CSV Export in APEX".

Now I want to extend the solution by an optional ZIP export.

You can not export several files at the same time with standard APEX features. For that you have to create a ZIP file including all the files you want to download.

I made an example application "Multi CSV Download as ZIP file" where you see a (more...)

SQL Probleme und das Oracle SQL Forum

Viele meiner Kollegen (um die 90 %) nutzen bei schwierigen SQL Problemen nicht das SQL Forum, sondern versuchen sich lieber an inperformanten PL/SQL Code.

Die Gründe sind leicht erklärt: Zeitmangel!

Der kurzfristige Vorteil kann im Nachhinein aber sehr teuer / zeitaufwendig werden. Nämlich dann, wenn die schnelle Lösung mit erhöhten Datenmengen versagt und statt ursprünglich wenigen Sekunden plötzlich Minuten an Zeit verbraucht.

Hierbei möchte ich auf die goldene Regel von Tom Kyte referenzieren:
     1. (more...)

Where do my trace files go? V$DIAG_INFO

Where do oracle trace files go? I don’t know why this piece of info will not stick in my head, I seem to have to look it up 3 or 4 times a year.

If only I had an easy way to find out. There is a very easy way to find out – and that piece of info won’t stay in my head either. So this really is a blog post just for stupid, (more...)

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Indentity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:

SQL> create table t
2 (id number generated as identity
3 ,name varchar2(35)
4 );

Table created.


SQL Problem Solved: Merge History Tables

Yesterday I published a SQL problem (that I I had solved – or at least, thought I had solved!) and invited readers to submit their own solutions.

SPOILER ALERT: if you haven’t had a chance to have a go yourself, go back, don’t read the comments (yet), but see if you can solve it yourself.

I was very pleased to see two excellent submissions that were both correct as far as I could (more...)

SQL Problem: Merge record history tables

A colleague asked me if I could help solve a problem in SQL, and I was grateful because I love solving problems in SQL! So we sat down, built a test suite, and worked together to solve it.

I will first present the problem and invite you to have a go at solving it for yourself; tomorrow I will post the solution we came up with. I think our solution is reasonably simple and elegant, (more...)

SQL and PL/SQL icons and stickers

Over the past couple of weeks I've been preparing my slides and presentations for Oracle Open World (2015).

One thing that occurred to me was that there was no icon or image to represent Oracle SQL and PL/SQL. I needed something that I could include in my presentations to represent these.

After a bit of Tweeting it turns out that there is no (official) icons or images for Oracle SQL and Oracle PL/SQL.

So I (more...)


I think I’ve found an (admittedly obscure) bug with PL/SQL and/or SQL.

Have a look and see if you also think this is odd – or have I missed the totally obvious?

(This is all on

Without going into the details (we would be here for hours if I did) I’m looking into the overhead of context switching between PL/SQL and SQL. It it fairly common knowledge that when you call (more...)

Analytische Funktionen (Teil 3): Gruppen mit zufälliger Auswahl der Datenbasis

Das folgende Beispiel bezieht sich auf die Datenbasis meines ersten Post zu "Analytischen Funktionen".
Es geht darum, dass für das Jahr 2014 drei Gruppen (mit einem Hash-Wert je Gruppe) gebildet werden müssen.
Gruppe 1:
- 3 zufällig ausgewählte Bundesländer die mit B anfangen
Gruppe 2:
- 3 zufällig ausgewählte Bundesländer die mit S anfangen
Gruppe 3:
- 3 zufällig ausgewählte Bundesländer die NICHT mit B und S anfangen

Hier das dafür notwendige SQL:

Fundamentals of SQL Writeback in Dodeca

One of the features of Dodeca is read-write functionality to SQL databases.  We often get questions as to how to write data back to a relational database, so I thought I would post a quick blog entry for our customers to reference.

This example will use a simple table structure in SQL Server though the concepts are the same when using Oracle, DB2, and most other relational databases.  The example will use a simple Dodeca (more...)

Analytische Funktionen (Teil 2): LISTAGG mit eindeutiger Liste

Die LISTAGG Funktion dient der Generierung von zusammenkonkatenierten Strings auf Basis einer Spalte. Wenn in der Spalte ein Wert mehrfach vorkommt, dann wird die Liste ebenfalls mit doppelten Werten generiert.

Im folgenden demonstriere ich ein Beispiel um dieses Problem zu lösen.

Beispieldaten - Land und Ort:


Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.

  lv_input  VARCHAR2(100 (more...)

Create MySQL Index

Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT, UPDATE, REPLACE INTO, and DELETE statements. Indexes are also called fast access paths.

In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve (more...)