It's out now - Database 12c Release 2 available for download

By -

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 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.

New SQL and PL/SQL Function VALIDATE_CONVERSION
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!

 

 

Technorati Tags: , , , ,

Comments

Post a Comment

Popular posts from this blog

My query just got faster - brief introduction to 12.2 in-memory cursor duration temp tables

By -
Image
Image courtesy of wikipedia This post covers one of the new SQL performance enhancements that we incorporated into Database 12c Release 2 . All of these enhancements are completely automatic, i.e. transparent to the calling app/developer code/query. These features are enabled by default because who doesn’t want their queries running faster with zero code changes? So in this post I am going to focus on the new In-Memory “cursor duration” temporary table  feature. Let’s start by looking at cursor duration temp tables… Above image courtesy of wikimedia.org What is a cursor duration temp table? This is a feature that has been around for quite a long time. Cursor duration temporary tables (CDTs) are used to materialize intermediate results of a query to improve performance or to support complex multi step query execution. The following types of queries commonly use cursor duration temp tables: WITH Clause and parallel recursive WITH Grouping Sets Star Trans
Keep reading

SQL Pattern Matching Deep Dive - Part 1

By -
Image
There has been quite a lot of interest in the new 12c MATCH_RECOGNIZE feature for SQL pattern matching. Therefore, I thought now would be the perfect time to start a series of quick deep dive posts that explain how SQL pattern matching works. Over the coming weeks I will cover the following topics in a series of posts. This is the start of a series of posts based on a presentation that I put together for the recent annual  BIWA conferenc e at Oracle HQ. The Oracle BI, DW and Analytics user community always puts on a great conference and this year was the best yet. You can download any or all of the presentations from this year’s conference by following this link . My pattern matching deep dive presentation started life about a year ago as a post covering some of the new keywords in the explain plan that are linked to pattern matching, see here . It has now expanded to cover a much wider range of topics. Firstly, a huge vote of thanks to my development team (Lei Sheng and Chun-Chie
Keep reading

SQL Pattern Matching Deep Dive - Part 6, state machines

By -
Image
The obvious way to start this particular post is to pose a couple of simple questions: what is a state machine and why should you care? In general I would say that you don't need to know about or care about state machines. That's the beauty of using SQL for pattern matching. The MATCH_RECOGNIZE clause encapsulates all the deep technical modelling and processing that has to be performed to run pattern matching on a data set. However, there are times when it is useful, probably vital, that you understand what is going on behind the scenes and one of the most obvious situations is when backtracking happens. Therefore, the content covered in this post is a going to be a gently lead-in into my next post where I am going to discuss the concept of “backtracking”  and the dreaded  ORA-30009 error. Let’s start our voyage of discovery…when you attempt to run a SQL statement containing a MATCH_RECOGNIZE clause during the compilation phase we generate a finite state machine ba
Keep reading