New Features

• The DISTINCT keyword of the LISTAGG function enables you to eliminate duplicates from the specified expression.

  See LISTAGG Function.

• Materialized views can be created based on hybrid partitioned tables. Query rewrite and refresh is supported for materialized views based on hybrid partitioned tables.

  See Creating Materialized Views Based on Hybird Partitioned Tables, Refreshing Materialized Views Based on Hybrid Partitioned Tables, and About Query Rewrite in the Presence of Hybrid Partitioned Tables.

• The performance of queries that contain COUNT(DISTINCT) operations is enhanced by using materialized views with non-additive facts.

  See Creating a Materialized View Containing Bitmap-based COUNT(DISTINCT) Functions and Query Rewrite and Materialized Views Based on Bitmap-based COUNT(DISTINCT) Functions.

New Features

• Enhancements to approximate query processing

  Business intelligence (BI) applications extensively use aggregate functions, including analytic functions, to provide answers to common business queries. With approximate query processing, you can instantly improve the performance of existing analytic workloads and enable faster ad-hoc data exploration. New approximate SQL functions for rank, sum and count are now available for top N style queries.

  See Also:

  About Approximate Top-N Query Processing

• Enhancements to parallel partition-wise operations

  Parallel partition-wise SQL operations can improve query performance significantly, leading to better response time. Parallel partition-wise joins are used commonly for processing large joins efficiently and fast. In addition to parallel partition-wise joins, you can also initiate parallel partition-wise execution of SQL operations with SQL windowing clauses.

  See Also:

  Parallel Partition-Wise Operations with Windowing Functions

• Enhancements to analytic views

  • In a WITH or FROM clause of a SELECT statement, a query of an analytic view may now include the FILTER FACT keywords to filter the data accessed by the analytic view prior to aggregation. Using the new ADD MEASURES keywords, you can define analytic view calculated measures within a SELECT statement, so that an application can now define its own measures dynamically.

    See Also:

    Analytic View Queries with Filtered Facts and Added Measures

  • The JOIN PATH keywords in the CREATE ATTRIBUTE DIMENSION statement support the use of snowflake-style dimension tables. The REFERENCES DISTINCT keywords in the CREATE ANALYTIC VIEW statement support the use of a denormalized fact table, in which dimension attributes and fact data are in the same table.

  • Analytic views now support a variety of new functions that you can use in calculated measure expressions. The new functions include RANK_*, PERCENTILE_*, STATS_*, COVAR_*, HIER_DEPTH, HIER_LEVEL, HIER_MEMBER_NAME, HIER_MEMBER_UNIQUE_NAME, HIER_CAPTION, and HIER_DESCRIPTION. Also, you can now use hierarchical attributes in the definition of calculated measures. For example, in a CASE statement you can specify different calculation expressions based on the value of an attribute.

New Features

• Attribute dimensions, hierarchies, and analytic views

  Analytic views provide a fast and efficient way to create analytic queries of data stored in existing database tables and views. Attribute dimensions specify dimension table or view columns as attributes and organize the attributes into levels. Hierarchies organize the levels hierarchically. Analytic views reference hierarchies and specify measures that reference data from fact tables and that specify calculations to make using the data.

  See Overview of Analytic Views

• Real-time materialized views

  Real-time materialized views further improve the availability of materialized views by providing user queries with fresh data even when the materialized view is marked as stale. When queries access a real-time materialized view that is stale, Oracle Database computes the fresh query result on the fly by using the stale data in the materialized view plus the delta information stored in the materialized view logs.

  See Using Real-time Materialized Views

• ON STATEMENT refresh mode for materialized views

  The ON STATEMENT refresh mode refreshes materialized views every time a DML operation is performed on any base table, without the need to commit the transaction. This mode does not require you to maintain materialized view logs on the base tables.

  See About ON STATEMENT Refresh for Materialized Views.

• Managing materialized view refresh statistics

  Statistics for materialized view refresh operations are collected and stored in the database. You can use current and historical statistics to analyze materialized view refresh performance over time.

  See Monitoring Materialized View Refresh Operations.

• Support for approximate query processing

  Approximate query processing provides extremely fast responses to explorative queries using SQL functions that return approximate results, with negligible deviation from the exact result. Queries containing SQL functions that return approximate results, or approximate queries, can be used to create materialized views. Materialized views based on approximate queries are eligible for query rewrite and can be fast refreshed.

  See About Approximate Query Processing.

• LISTAGG enhancements

  The LISTAGG function now provides control over scenarios where the concatenated string returned by the LISTAGG function exceeds the maximum length supported by the VARCHAR2 data type.

  See LISTAGG Function.

• Improved handling of data errors using SQL functions

  The CAST operator can now return a user-specified value when a data type conversion error occurs. The VALIDATE_CONVERSION function determines if conversion to a specified data type can succeed.

  See Handling Data Errors with SQL.

• Enhanced query performance with the IM column store

  In-Memory Expressions (IM expressions) calculate and populate the results of frequently-evaluated query expressions into the In-Memory Column Store (IM column store). Populating IM expressions into the IM column store ensures that they can be used by subsequent queries to provide faster query response times.

  In-memory virtual columns (IM virtual columns) enable you to populate virtual columns defined on a table into the IM column store. You can populate all or just a subset of virtual columns from a table into memory to improve the performance of analytic queries that access these virtual columns.

  See About Improving Query Performance Using In-Memory Expressions and About Using In-Memory Virtual Columns to Improve Query Performance.

• Automatic Data Optimization (ADO) support for IM column store

  ADO can be used to manage the contents of the IM column store. ADO uses heat map statistics to ensure that only the elements that benefit most are stored in the IM column store. This enhances the performance benefits of using the IM column store.

  See About In-Memory Column Store and Automatic Data Optimization.

• Optimize the performance of batch updates during table redefinition

  The EXECUTE_UPDATE procedure in the DBMS_REDEFINITION package can optimize the performance of bulk updates to a table.

  See About Batch Updates and Online Table Redefinition.

• Support for materialized view refresh during online table redefinition

  The DBMS_REDEFINITION package can be used to incrementally refresh fast refreshable dependent materialized views during online table redefinition.

  See About Refreshing Dependent Materialized Views During Online Table Redefinition.

• Support for partitioning of external tables

  Partitioning of external tables extends existing Oracle partitioning to external tables. This allows for better integration with external sources, for example, to align the partitioning of an external table with the partitions of a HIVE table. It also improves query performance for data stored outside of database using existing partition pruning techniques.

  See Partitioning for Easier Data Access.

• Database operation monitoring

  Extraction, Transformation, and Loading (ETL) jobs that are performing suboptimally can be monitored to help identify performance bottlenecks.

  See Overview of Monitoring ETL Operations.