EXPLAIN EXTENDED

How to create fast database queries

Archive for the ‘Oracle’ Category

Counting missing rows: Oracle

Comments enabled. I *really* need your comment

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

Let's see how Oracle processes this predicate.

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

Written by Quassnoi

April 21st, 2009 at 11:00 pm

Posted in Oracle

GROUP_CONCAT in Oracle 10g

with one comment

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 »

Written by Quassnoi

April 5th, 2009 at 11:00 pm

Posted in Oracle