Top 15 new features of Oracle Database 12.2 for developers

Oracle Database 12c Release 2 in now available. With this post I am trying to compile top 15 new features of Oracle Database 12.2 for developers. You can check Oracle Database 12c New Features for Developers here (in comparison of Oracle 11g).

1) Long Identifiers: The maximum length of identifiers is increased to 128 bytes from 30 bytes. It gives you greater flexibility in defining longer and more expressive table (and other objects) names.

2) LISTAGG Functionality Enhanced: LISTAGG aggregates the values of a column by concatenating them into a single string. LISTAGG function has been improved to manage situations where the length of the concatenated string is too long. The new option, overflow truncated, adds three dots at the end of the concatenated string followed by the number of truncated characters in brackets.

3) CAST Function Enhanced With Error Handling: CAST function is enhanced to return a user-specified value instead of raising an error, when the conversion is not valid.

4) Auto-List Partitioning: With Auto-List Partitioning, database automatically creates a new partition for every distinct partition key value. It removes the management burden to manually maintain a list of partitions for a large number of distinct key values that require individual partitions. It also automatically deals with the unplanned partition key values without the need of a DEFAULT partition.

5) With Oracle 12c R2 Non-partitioned tables can now be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. We can also Online SPLIT Partition and Sub-partition.

6) Multi-Column List Partitioning: List partitioning functionality is expanded to allow multiple partition key columns.

7) Read-Only Partitions: Partitions and sub-partitions can be individually set to a read-only state.

8) Materialized Views - Delta Computation: Materialized views can be used for query rewrite even if they are not fully synchronized with the base tables. Using materialized view logs for delta computation with stale materialized view, and return correct results in real time.

9) Materialized Views: Statement-Level Refresh: Materialized join views can now be refreshed when a DML operation takes place, without the need to commit. It offers built-in refresh capabilities that avoids the need of customer-written trigger-based solutions.

10) VALIDATE_CONVERSION Function: New SQL function, VALIDATE_CONVERSION, determines whether a given input value can be converted to the requested data type.

11) Approximate Query Processing: Approximate query aggregation is a common requirement now a days for data analysis. The approximate processing of large volumes of data is significantly faster than the exact aggregation.

12) JSON searching Improvements: We can now generate JSON documents directly from SQL queries. The JSON search index supports RANGE and LIST partitioned tables and support range-based searching on numeric values. JSON documents can be manipulated using PL/SQL now.

13) High level of index compression: it provides significant space savings while also improving performance for queries that are executed using indexes. High compression offers the various advantages over low compression.

14) Index Monitoring: With 12.2 Index monitoring is now enabled by default, tracks the usage at execution level and keeps a lot of information

15) External Tables – partitions: External tables can now be partitioned by using a set of files (instead of the single one), each representing a table partition.

Apart from above new features, As per the Oracle Documentation, The non-CDB architecture is deprecated in Oracle Database 12c, and may be desupported and unavailable in a release after Oracle Database 12c Release 2. Oracle recommends use of the CDB architecture.

To read about all the new features of Oracle 12.2, check Oracle Documentation

Related Posts:
- Oracle Database 12c New Features for Developers
- JSON in Oracle 12c Database with Examples
- New Features for Developers in Oracle 11g
- Sequence Performance Enhancement with each Oracle Version
- Fill Gaps in Sparse Data - Partitioned Outer Join
- SQL Interview Question Answers