Category Archives: SQL

10g SQL: MEMBER OF statement … but watch out

In 10g Oracle introduced the MEMBER OFF statement. The following article shows how using this feature handling binding in-list may introduce performance degradiation. The article also show an example where the feature increase performance. Once again: There is very few universal rules regarding SQL tuning.

SQL: SAMPLE statement

I just read an article using the SAMPLE statement. I couldn’t really recall this SQL syntax, and immediately thought this had to me a new feature. After googling I realized that this was allready available in 8i. I don’t have a 8i version available, but did a small test in 9i.


In Oracle 9i the DBMS_XPLAN package was introduced. One of the most used functions in this package has been the DISPLAY function. From version 10g this package was extended to include two other related functions: the DISPLAY_CURSOR and the DISPLAY_AWR functions. In this note I´ll take a look at the function first introduced in 9i […]

XMLAGG: Solution to the ORA-01489 when using SYS_CONNECT_BY_PRIOR or LISTAGG

To concatinate text strings from different rows I earlier used the SYS_CONNECT_BY_PRIOR or the LISTAGG functions. The problem with these functions is that they return a varchar2, which in the SQL engine is restricted to 4000 characters. Below I´ll present an alternative approach that I recently discovered. First I show how and why it fails […]

VARCHAR2 (byte vs char)

Some days ago I got a question about the difference between defining a variable as varchar2(10 byte) and varchar2(10 char). I had to admitt that I should have known this, but I didn´t. I couldn´t even tell what the default setting was when using varchar2(10). Actually this turned out to be the opposite of what […]

Loading CLOB into rows and columns in a table

Warning: This is not the method I’d recommend for loading data into a oracle table from a file). This is not a method Oracle has made for this purpose. Oracle has an utility named SQLLoader that is written to serv this purpose, and I´ll definitly recommend using this instead. That said – I did get […]


From time to time it can be smart checking your database for unwanted table scans or index fast full scans. An table scan or index full scan is not necessarily evil, but can sometimes be an indication that you are not hitting an optimal execution plan. This other day I read a note from Jonathan […]

Explain Plan: Filter vs Access predikater

Veldig mange som jobber med SQL tuning og eksikveringsplaner (explain plan) kjenner ikke disse predikat begrepene. Jeg må innrømme at jeg har tunet SQL-er i mange år uten å ta hensyn til betydningen av disse predikatene. Dette blir som å kjøre (race)bil uten å vite at man har et 5 gir. På et kurs med […]