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.
Update 03-Mar-2017:
Oracle Database EE 12.2 (on Premises) is now available. Earlier it only available on Oracle Cloud.
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Following is the link for New Features of Oracle Database 12.2.
- https://docs.oracle.com/database/122/NEWFT/toc.htm
But the main improvement area (or you may say issue resolved) in Oracle 12.2 from 12.1 is, Oracle has improved a lot on “Adaptive Query Optimization”, which has given a lot of trouble (in my views) to DBAs and Developers in Oracle 12c R1.
- http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
Related Posts:
- Oracle Database 12c New Features for Developers
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- JSON in Oracle 12c Database with Examples
- New Features for Developers in Oracle 11g
- Sequence Performance Enhancement with each Oracle Version
- DML Error Logging - DBMS_ERRLOG.CREATE_ERROR_LOG
- Fill Gaps in Sparse Data - Partitioned Outer Join
- SQL Interview Question Answers
- Complex View Merging Transformation and ORA-00979 in Oracle 12c
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.
Update 03-Mar-2017:
Oracle Database EE 12.2 (on Premises) is now available. Earlier it only available on Oracle Cloud.
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Following is the link for New Features of Oracle Database 12.2.
- https://docs.oracle.com/database/122/NEWFT/toc.htm
But the main improvement area (or you may say issue resolved) in Oracle 12.2 from 12.1 is, Oracle has improved a lot on “Adaptive Query Optimization”, which has given a lot of trouble (in my views) to DBAs and Developers in Oracle 12c R1.
- http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
Related Posts:
- Oracle Database 12c New Features for Developers
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- JSON in Oracle 12c Database with Examples
- New Features for Developers in Oracle 11g
- Sequence Performance Enhancement with each Oracle Version
- DML Error Logging - DBMS_ERRLOG.CREATE_ERROR_LOG
- Fill Gaps in Sparse Data - Partitioned Outer Join
- SQL Interview Question Answers
- Complex View Merging Transformation and ORA-00979 in Oracle 12c
Are listagg length changes specific to 12.2? What is the new limit
ReplyDeleteOh they seem to have added options to handle the overflow
DeleteYes, Overflow truncated :) But as LISTAGG supports varchar2, and we can extend the size of varchar2 to 32767 in Oracle 12c by setting MAX_STRING_SIZE initialization parameter to EXTENDED
Deleteare there any drawbacks with extending it?
DeleteI have heard about some implications on indexes with extended data type and it cause chained rows which may hit performance. Also we need to test it for how listagg behaves with extended datatypes.
DeleteIt's a shame LISTAGG update didn't come with DISTINCT option
DeleteListagg distinct has been introduced in 19c I think if not in 18c
DeleteThe non-CDB architecture is NOT de-supported in 12.2. De-supported means removed and as you said, it still exists.
ReplyDeleteBlog Post Updated with Link to Oracle Documentation.
DeleteIs the release 12.2 had been released
ReplyDeletefor cloud only
DeleteYou might think about leaving "for developers" off your titles. Many of these features are more appropriate for DBAs...
ReplyDeleteYou could argue some categorisations, lines are blurred, but I see a lot of developer features. I think "Design" bucket are tools that could be considered by both, but not distinctly DBA.
DeleteDesign 1, 4, 6, 8, 15
Develop 2, 3, 9, 10, 11, 12, (14)
DBA 5, 7, 13, 14
Question of perspective. All of the above features, except for maybe point 14, are features we would have to take care of ourselves as developers (at least in my company), without the intervention of the DBA team. So, I guess it depends on your company and situation.
DeleteFor me every thing comes under developer profile except installation, backup & recovery and related stuff :)
DeleteThat is a valid point. Many of the features never come up in day to day developer activity.
DeleteThey look like some great features. I think the materalized view enhancements will be the one I get the most out of. And of course the long identifiers! 30 characters was very small!
ReplyDelete12 Things Developers Will Love About Oracle Database 12c Release 2 by Chirs Saxon (AskTom)
ReplyDeletehttps://blogs.oracle.com/sql/entry/12_things_developers_will_love
Hi all,
ReplyDelete12.2 brings also some interesting improvements in PL/SQL-only data types binding using the DBMS_SQL API. With the new procedure DBMS_SQL.BIND_VARIABLE_PKG you can use binding with associative array indexed by PLS_INTEGER and RECORD type.
My spouse and I love your blog and find almost all of your posts to be just what I’m looking for. Appreciating the persistence you put into your blog and the detailed information you provide. I found another one blog like you Oracle ADF.Actually I was looking for the same information on internet for Oracle Application Development Framework and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.
ReplyDelete