Skip to content

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.

Posted 09 January 2012 † Lasse Jenssen § Oracle § PL/SQL ‡ Comments (0) °

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.

Posted 09 January 2012 † Lasse Jenssen § Oracle ‡ Comments (0) °

DBMS_XPLAN.DISPLAY

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 […]

Posted 02 January 2012 † Lasse Jenssen § Oracle § Performance ‡ Comments (0) °

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 […]

Posted 20 November 2011 † Lasse Jenssen § Oracle ‡ Comments (0) °

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 […]

Posted 19 November 2011 † Lasse Jenssen § Oracle ‡ Comments (0) °

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 […]

Posted 22 September 2011 † Lasse Jenssen § Oracle ‡ Comments (0) °

SQL-statements with TABLE SCAN and INDEX FAST FULL SCAN

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 […]

Posted 22 May 2011 † Lasse Jenssen § Oracle § Performance ‡ Comments (0) °

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 […]

Posted 21 May 2011 † Lasse Jenssen § Oracle § Performance ‡ Comments (0) °