More Cool 12.2 Features

If you’ve read my post about 12.2 new features and liked it, you will like this one as well. I’ve recently looked at presentations from OOW16, and found one by Keith Laker from Oracle. Keith, as principal PM for analytic SQL, talked about DWH features in 12.2, and they are great.

I’d like to thanks Keith for uploading the presentation. I will review some of the feature from the presentation, and will only mention the them without a deep explanation. There is a link to the original presentation below.

LISTAGG improvements

Listagg is a built-in function that takes a column and aggregates it to a single string. In 12.2, the syntax was extended to handle overflow errors by truncating the output to match a specific size. There is also an option to display the number of truncated characters.


We all ran into cases where we try to convert one datatype to another (with TO_NUMBER, TO_DATE, etc.) and got conversion errors. Oracle 12.2 introduces the new function VALIDATE_CONVERSION. This function is a row function and for every value it returns 0 if there was a conversion error, and 1 if the conversion succeeded. Using this function makes it easy to find all the values that will result in conversion error. This function can also be used as a predicate to eliminate the conversion problems.

Another really cool thing is the ability to return a default value when a conversion error occurs. So the query will not fail, but return this default value for all values that could not be converted.

Temp tables for specific cursors

In complex queries, the same sub-query might be executed many times (query in a “with” clause for example). In 12.2, the optimizer can use a temporary table for a specific cursor. It will create a table for the life of the cursor only, it will then load data into this table and use it as many times as needed, instead of running the base query several times.

Band join

Sometimes we need to use a non-equi join (a join that is not based on equality of the columns). An good example is when joining according to a date range, one table has a date column and we would like to join it with a table that contains min_date and max_date columns to get the correct time window. In this case the join will be something like a.curr_date between b.min_date and b.max_date.

Before 12.2, Oracle had to use sort-merge or hash joins. In Oracle 12.2, a hash algorithm was introduced to support these kind of joins and greatly improve performance.


As I read more about 12.2 I like it more. These features make our lives much easier with new syntax and abilities and make the optimizer more sophisticated and efficient and

If you find these features interesting I encourage you to read the presentation itself, as it contains code samples and more explanations about them. You can find the presentation here.


7 thoughts on “More Cool 12.2 Features

  1. Regarding 12.2 allowing temp tables for specific WITH clauses:

    This was possible in 11.2 via the MATERIALIZE hint.

    Is there something different in 12.2 that allows this?


    • Hi,
      Thanks for your comment, the MATERIALIZE hint does something similar indeed (and actually, Oracle can do that even without the hint). I have to admit that I’m not sure, but I think the difference is that in 12.2 the temp tables are cursor-only. Before that it seems to be regular temp tables (even though they were created and deleted automatically), so I guess the mechanism behind the temp table is different and probably more efficient (because it doesn’t involve the dictionary).
      I’ll try to verify that and if I have a better answer I’ll post it here.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s