Archive for September, 2007

The PIVOT operation is one frequently discussed when talking about more advanced SQL Queries. Pivoting is the process of switching rows and columns, and is for example an advanced feature in Excel. Before Oracle 11g, there were several approaches to pivoting, that were not work-arounds for the lack of the PIVOT operator in the Oracle SQL Language.

With Oracle 11g, this has changed. Now we do have a PIVOT and an UNPIVOT operator at our disposal. Let's first take a look at UNPIVOT. In Oracle, the UNPIVOT operation is the process of turning Columns to Rows. Put simply, by applying the UNPIVOT operator to a number of columns, every row is split into that same number of rows. Each of these rows has two new columns: one for the column that this row stems from – one of the columns the data set was UNPIVOTed by – and one with the value from the column. The original UNPIVOT-columns are no longer part of the newly created records.

Let's take a look at a simple example, using the well known EMP table. We decide to consider the SAL and COMM columns as two different types of INCOME_COMPONENT. We would like to present our employees with an INCOME_COMPONENT column – and two rows for each employee with two income components.

This UNPIVOT query would look like this:

select *
from   ( select ename, job, sal, comm
         from   emp
       )
       unpivot
       ( income_component_value
         for income_component_type in (sal, comm)
       )
/

Read the rest of this entry »

One of the interesting new pieces of functionality in Oracle 11g is offered by the DBMS_COMPARISON package. This package allows us to compare two tables – or two views on tables – and inform us on the differences in data. More specifically: the package can summarize if there any changes between the data sets and if so how many. It can also inform us in detail about the specific rows that are different between the two. Finally, DBMS_COMPARISON can converge the data sets, taking one of the two as the master and fully aligning the other one. Using DBMS_COMPARISON, we get on-demand replication for ordinary tables, within schemas, across schemas or even across databases.

In this article, I will demonstrate – on the 11.1.0.6 (Beta) release for Windows – how I can compare table EMP with a clone, whose data has been modified. We will then see how we can re-align the clone with the original (master) table. In this example, I work within a single schema. However, everything I do can be done across schemas and even across a database link, catering for custom data replication scenarios.

Here is our challenge: Read the rest of this entry »