Tag Archives: Partitioned Outer Join

ORA-600 Error in a Partitioned Join

In a previous post, I wrote about partitioned outer joins in Oracle.
The simple schema and queries in that post are similar to those I used when first learning about this feature. At the time, I came upon an unexpected error which caused some Oracle instances to throw an ORA-600 error from a very simple query.

The original test environment I had been using was Oracle Database 11g Enterprise Edition 11.2.0.1.0 running on Solaris.
I had created a simple schema to experiment with, and written a couple of queries which returned results designed to illustrate the effect of a partitioned join as compared with other types of joins.
Once I had them doing what I wanted, I decided to discuss them with a colleague, who tried running the on his local instance of Oracle Express 11.2.0.2.0 on Windows.
Instead of the expected results, he received the following error:
ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], [1], [0], [], []

Continue reading ORA-600 Error in a Partitioned Join

Partitioned Outer Joins in Oracle

From 10g onwards Oracle has had a type of join called a Partitioned Outer Join, providing a convenient syntax which can be used to fill gaps in sparse data.
The documentation describes the result of a partitioned outer join as “a UNION of the outer joins of each of the groups in the logically partitioned table with the table on the other side of the join.”

To demonstrate what this means in practice, I will run through a simple example.

We will start with two very simple tables, products and sales, which look like this:
Continue reading Partitioned Outer Joins in Oracle