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 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 on Windows.
Instead of the expected results, he received the following error:
ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], [1], [0], [], []

If you’ve had an ORA-600 error before, then you’ll know that they can be particularly difficult to diagnose and fix.
Unlike the normal ORA codes generated during query parsing which contain information about the location and nature of the problem, an ORA-600 error is internal to the database code. When the error occurs, information is written to the alert log and a separate trace file, but for troubleshooting you will probably need access to the knowledge-base content on My Oracle Support available only to those with support contacts.
Although some ORA-600 errors can be caused by underlying file issues such as corruption or lack of swap space, others may indicate software defects for which the only resolution is to install a service release or hotfix.

Here is the query that caused the error:

Using the schema from the previous post, these are the results it should produce:

After trying a couple of things, I discovered that a small change to the query resulted in it running successfully on both platforms without encountering the error, while still returning the same results.

When creating the sales table, I had given it a foreign key constraint to enforce referential integrity on the product_id column. Because of this, using an outer join to the products table in this query was unnecessary; I should have used an inner join.
It was only when I encountered the ORA-600 error that I took another look at the query, realised this, and so by chance found the solution to the error.

Changing from an outer to an inner join here also results in a better execution plan being chosen:

Execution Plan - Partitioned Join and Left Join
Execution Plan for the original query with the left outer join
Execution Plan - Partitioned Join and Inner Join
Execution Plan for the revised query with the inner join

Leave a Reply