EXPLAIN EXTENDED

This is the 4^th of 5 articles covering implementation of NOT IN predicate in several RDBMS'es:

• NOT IN in MySQL
• NOT IN in MySQL (using DISTINCT)
• NOT IN in SQL Server
• NOT IN in Oracle
• NOT IN in PostgreSQL

Let's see how Oracle processes this predicate.

Let's create the sample tables:
Read the rest of this entry »

MySQL has a nice aggregate function called GROUP_CONCAT, which concatenates all strings in a group in given order, separating them with a given separator.

In one of the previous articles, I wrote about emulating this function in PostgreSQL.

Now, we'll try to emulate this function in Oracle 10g.

There are numerous solutions using Oracle's hierarchical function SYS_CONNECT_BY_PATH, but they're bad in the following ways:

• Not all queries can be rewritten using CONNECT BY
• Even if they can, CONNECT BY performs poorly on some conditions

Instead, we will emulate this function using Oracle's MODEL clause.
Read the rest of this entry »