Oracle 12c New Features :

In my previous article i have given different features of Oracle 18c as well as Oracle 11G vs Oracle 12c.In this article i would like to give you Oracle 12c New Features.I would also like to give you Oracle 12C New features with real life examples.These Oracle 12c New Features are really helpful for users to achieve the business requirement easily.

Oracle 12c New Features – Related to Performance Tuning :

In this section i will give you different Oracle 12c New features which are very much helpful for the performance tuning of database. These features are important to improve the database performance as compare to oracle 11G.

1.Advanced Indexing Techniques :

Oracle 12c uses the advanced indexing techniques as compare to oracle 11g.In oracle 11G user can create only one index on one column.The oracle 12c gives user the right to create multiple indexes on same column.To create multiple indexes on same column user needs to use the different type of index.

At a time only one index is usable.If both of indexes are usable then it will be confusing for oracle engine to use the index.

Example :

Let us consider that user needs to create the index on Student table.

Create index SI_Student_Id on Student(Student_id);

Index created;

Create bitmap index BM_Student_Id on Student(Student_id);

Index created;

2.Index Compression :

Oracle 12c  uses the index compression technique so as to improve the compression rations which provides the efficient access to index.

3.Approximate_Count_Distinct function to improve performance :

In Oracle 12c provides new function which is optimized function named APPROX_COUNT_DISTINCT() function.This function will process large volume of the data which is faster than exact aggregation.

4.Automated caching for Big table :

To handle the heavy database load oracle 12c provides the functionality of automatic caching for big tables.

5.Adaptive Query Optimization with online stat gathering :

Oracle 12c provides the online stat gathering strategy to improve the performance of the query. It will provide the adaptive query optimization to improve the query performance.

6.In-Memory Aggregation :

Oracle 12c provides the In-memory aggregation which optimized the queries that joins the fact and dimension tables.It will reduce the CPU usages and it will prove the fast and more consistent query performance which supports the large number of concurrent user.

7.Full Database caching :

Full database caching will cache the entire database in memory.The full database caching should be used when the buffer cache size of database instance is greater than the whole database size.

Oracle 12c New Features – Related to development :

In this section i would like to explain the Oracle 12c New Features related to development.

1.Improved Error Handling in Oracle 12c :

Oracle 12c gives the facility of improved error handling oracle 12c.It provides the user friendly exception processing and gives the facility to provide user friendly messages instead of the oracle system generated error messages.

2.Size limit for datatypes are improved :

The size limit for varchar 2,nvarchar2 and raw datatypes is increased.the size limit is improved from 4k to 32767 bytes.

3.Table Partitioning and sub-partition :

You can very easily migrate a partition or sub partition from one tablespace to another. Similar to how the online migration was achieved for a non-partitioned table in prior releases, a table partition or sub partition can be moved to another tablespace online or offline. When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.

4.Temporary Undo system :

Prior to 12C, undo records generated by TEMP Tablespace is stored in the undo tablespace. With Temp undo feature in 12C, temp undo records can be stored in temporary table instead of UNDO TS. The benefit is – reduced undo tablespace and reduced redo log space used.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=1G;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; –disables the hard limit

5.Use of Rowid :

ROWID can now be used for automatic DML processing (as an alternative to identifying the primary key columns).

Using ROWID instead of a constrained number of primary key columns allows developers to utilize the standard wizards when defining forms and reports based on tables with more than two primary key columns. This is particularly important in commercial off-the-shelf (COTS) applications, such as People-soft.

6.PL/SQL Functions Defined in the SQL WITH Clause :

You can define a PL/SQL function in the WITH clause of a subquery and use it as an ordinary function beginning with this release.

The procedural logic needed to support a SQL statement is encapsulated with the SQL statement. This is particularly useful in a read-only database.

Using this construct results in better performance as compared with schema-level functions.

7.Default Values for Columns Based on Oracle Sequences :

Default values for columns can directly refer to Oracle sequences. Valid entries are sequence.CURVAL and sequence.NEXTVAL.

Providing the functionality to directly refer to a sequence as a default value expression simplifies code development.

8.DEFAULT Values for Columns on Explicit NULL Insertion :

The DEFAULT definition of a column can be extended to have the DEFAULT being applied for explicit NULLinsertion.

The DEFAULT clause has a new ON NULL clause, which instructs the database to assign a specified default column value when an INSERT statement attempts to assign a value that evaluates to NULL.

9.IDENTITY Columns :

Table columns have been enhanced to support the American National Standards Institute (ANSI) SQL keyword IDENTITY.

This provides a standards based approach to the declaration of automatically incrementing columns simplifying application development and making the migration of DDL to Oracle simpler.

10. FDA Support for CDBs :

Flashback Data Archive (FDA) is supported for multi-tenant container databases (CDBs) in this release.

Customers can now use Flashback Data Archive in databases that they are consolidating using Oracle Multi-tenant, providing the benefits of easy history tracking to applications using pluggable databases (PDB) in a multi-tenant container database.

These are some most important features as well as advantages of oracle 12c.If you like this article or if you have any concerns with the same kindly comment in to comments section.

(Source : Oracle Website  )