Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Database 12c Release 2 available for download

Keith Laker
Senior Principal Product Manager

Database 12c Release 2 available for download 

Yes, it’s the moment the world has been waiting for: the latest generation of the world’s most popular database, Oracle Database 12c Release 2 (12.2) is now available everywhere - in the Cloud and on-premises. You can download this latest version from the database home page on OTN - click on the Downloads tab.

So What’s New in 12.2 for Data Warehousing?

This latest release provides some incredible new features for data warehouse and big data. If you attended last year’s OpenWorld event in San Francisco then you probably already know all about the new features that we have added to 12.2 - checkout my blog post from last year for a comprehensive review of #oow16:

Blog: The complete review of data warehousing and big data content from Oracle OpenWorld 2016

If you missed OpenWorld and if you are a data warehouse architect, developer or DBA then here are the main feature highlights 12.2 with links to additional content from OpenWorld and my personal data warehouse blog: 


General Database Enhancements

1) Partitioning

Partitioning: External Tables
Partitioned external tables provides both the functionality to map partitioned Hive tables into the Oracle Database ecosystem as well as providing declarative partitioning on top of any Hadoop Distributed File System (HDFS) based data store.

Partitioning: Auto-List Partitioning
The database automatically creates a separate (new) partition for every distinct partition key value of the table.

Auto-list partitioning removes the management burden from the DBAs to manually maintain a list of partitioned tables for a large number of distinct key values that require individual partitions. It also automatically copes with the unplanned partition key values without the need of a DEFAULT partition.

Partitioning: Read-Only Partitions
Partitions and sub-partitions can be individually set to a read-only state. This then disables DML operations on these read-only partitions and sub-partitions. This is an extension to the existing read-only table functionality. Read-only partitions and subpartitions enable fine-grained control over DML activity. 

Partitioning: Multi-Column List Partitioning
List partitioning functionality is expanded to enable multiple partition key columns. Using multiple columns to define the partitioning criteria for list partitioned tables enables new classes of applications to benefit from partitioning.

For more information about partitioning see:

#OOW16 - Oracle Partitioning: Hidden Old Gems and Great New Tricks, by Hermann Baer, Senior Director Product Management

Partitioning home page on OTN

2) Parallel Execution

Parallel Query Services on RAC Read-Only Nodes
Oracle parallel query services on Oracle RAC read-only nodes represents a scalable parallel data processing architecture. The architecture allows for the distribution of a high number of processing engines dedicated to parallel execution of queries.

For more information about parallel execution see: 

#OOW16 - The Best Way to Tune Your Parallel Statements: Real-Time SQL Monitoring by Yasin Baskan, Senior Principal Product Manager

Parallel Execution home page on OTN


Schema Enhancements

Dimensional In-Database analysis with Analytic Views

Analytic views provide a business intelligence layer over a star schema, making it easy to extend the data set with hierarchies, levels, aggregate data, and calculated measures. Analytic views promote consistency across applications. By defining aggregation and calculation rules centrally in the database, the risk of inconsistent results in different reporting tools is reduced or eliminated.

The analytic view feature includes the new DDL statements, such as CREATE ATTRIBUTE DIMENSION, CREATE HIERARCHY and CREATE ANALYTIC VIEW, new calculated measure expression syntax, and new data dictionary views.These analytic views allow data warehouse and BI  developers to extend the star schema with time series and other calculations eliminating the need to define calculations within the application. Calculations can be defined in the analytic view and can be selected by including the measure name in the SQL select list.

For more information about Analytic Views see:

#OOW16 - Analytic Views: A New Type of Database View for Simple, Powerful Analytics by Bud Endress, Director, Product Management

SQL Enhancements

Cursor-Duration Temporary Tables Cached in Memory
Complex queries often process the same SQL fragment (query block) multiple times to answer a question. The results of these queries are stored internally, as cursor-duration temporary tables, to avoid the multiple processing of the same query fragment. With this new functionality, these temporary tables can reside completely in memory avoiding the need to write them to disk. Performance gains are the result of the reduction in I/O resource consumption.

Enhancing CAST Function With Error Handling
The existing CAST function is enhanced to return a user-specified value in the case of a conversion error instead of raising an error. This new functionality provides more robust and simplified code development.

The new function, VALIDATE_CONVERSION, determines whether a given input value can be converted to the requested data type. The VALIDATE_CONVERSION function provides more robust and simplified code development.

Enhancing LISTAGG Functionality
LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality is added for managing situations where the length of the concatenated string is too long. Developers can now control the process for managing overflowing LISTAGG aggregates. This increases the productivity and flexibility of this aggregation function.

Approximate Query Processing
This release extends the area of approximate query processing by adding approximate percentile aggregation. With this feature, the processing of large volumes of data is significantly faster than the exact aggregation. This is especially true for data sets that have a large number of distinct values with a negligible deviation from the exact result.

Approximate query aggregation is a common requirement in today's data analysis. It optimizes the processing time and resource consumption by orders of magnitude while providing almost exact results. Approximate query aggregation can be used to speed up existing processing.

Parallel Recursive WITH Enhancements
Oracle Database supports recursive queries through the use of a proprietary CONNECT BY clause, and an ANSI compliant resursive WITH clause. The parallel recursive WITH clause enables this type of query to run in parallel mode. These types of queries are typical with graph data found in social graphs, such as Twitter graphs or call records and commonly used in transportation networks (for example, for flight paths, roadways, and so on).

Recursive WITH ensures the efficient computation of the shortest path from a single source node to single or multiple destination nodes in a graph. Bi-directional searching is used to ensure the efficient computation of the shortest path from a single source node to single or multiple destination nodes in a graph. A bi-directional search starts from both source and destination nodes, and then advancing the search in both directions.

For more information about the new data warehouse SQL enhancements see:


In addition to the above features, we have made a lot of enhancements and added new features to the Optimizer and there is a comprehensive review by Nigel Bayliss, senior principle product manager, available on the optimizer blog. Obviously, the above is my take on what you need to know about for 12.2 and it’s not meant to be an exhaustive list of all the data warehouse and big data features. For the complete list of all the new features in 12.2 please refer to the New Features Guide in the database documentation set.

I would really like to thank my amazing development team for all their hard work on the above list of data warehouse features and the all the time they have spent proof-reading and fact-checking my blog posts on these new features. 

Enjoy using this great new release and checkout all the 12.2 tutorials and scripts on livesql!



Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.