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.

VALIDATE_CONVERSION function

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.

Summary

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.