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.
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.
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;
Oracle 12c uses the index compression technique so as to improve the compression rations which provides the efficient access to index.
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.
To handle the heavy database load oracle 12c provides the functionality of automatic caching for big tables.
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.
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.
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.
In this section i would like to explain the Oracle 12c New Features related to development.
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.
The size limit for varchar 2,nvarchar2 and raw datatypes is increased.the size limit is improved from 4k to 32767 bytes.
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.
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
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.
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.
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.
The DEFAULT
definition of a column can be extended to have the DEFAULT
being applied for explicit NULL
insertion.
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
.
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.
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 )
In my previous article I have given details about application support engineer day to day…
In my previous articles I have given the roles and responsibilities of L1,L2 and L3…
In my previous articles i have given the hierarchy of production support in real company…
In this article i would like to provide information about production support organization structure or…
In my previous article I have given roles for L1 and L2 support engineer with…
I have started this new series of how to become application support engineer. This article…