Oracle 11g vs 12c | What is Difference between Oracle 11G and Oracle 12c

Oracle 11g vs 12c :

In my previous article, I have given the idea about the Oracle 11g new features. There are some newly added features in Oracle 12c over oracle 11G.In the article I will try to explain the newly added features in oracle 12c as well as Oracle 11g vs 12c. From 12c Onwards, the instance is shared with multiple databases. This multiple databases are self-contained and pluggable from one database to another database. This is very useful methodology where database consolidation. In short a single SGA and background process will be shared to multiple databases, the databases can be created on fly and drop or attach and detach from one server to another server.This article will give you idea about Oracle 11g vs 12c .

Oracle 12c Features:

1.Advanced Indexing Techniques:

Oracle 12c user will be able to create multiple indexes on same column.Prior to oracle 12c, user will be able to create single index on one column. While creating multiple index-using oracle 12c the type of index should be different and only one type of index is usable at a time. Means if user create one index on column ‘a’ which is simple index and usable then user cannot create the index on same column.

Only one type of index on column is usable at a same time.

To create multiple index on it column alter the session and set following attribute as true:

Optimizer_use_invisible_index=true

Then create indexes,

Create index SI_Employee on Employee (Employee_ID);

Create bitmap index BM_Employee on Employee (Employee_ID) Invisible;

2.Newly added Functions:

Oracle 12c has added new features to handle the huge data aggregation using Approx_Count_distinct () function which handles the approximate count distinct aggregation. Optimizing the processing time and resource consumption by orders of magnitude while providing almost exact results speeds up any existing processing and enables new levels of analytical insight.

3.Automatic Big table caching:

In previous versions of oracle, in-memory parallel query did not work well when multiple scans contended for cache memory. The new cache mechanism called as big table cache is used to improve the performance for full table scan.

4.Full Database caching:

Oracle 12c provides the full database-caching feature, which is beneficial to improve the performance of application especially in I/O throughput and response time.

 5.In Memory aggregation:

Oracle 12C supports In-memory aggregation, which optimizes the queries that joins the dimension tables to fact tables and aggregate data. User needs to use KEY VECTOR and VECTOR GROUP BY operations to achieve the In-memory aggregation. In memory, aggregation is used in star schema, which improves the performance of Star queries and reduces the CPU usage. The In memory aggregation eliminates the need of summary tables in most of cases which simplifies the star schema query.

6.JSON support :

Oracle 12C supports the Java Object Notation(JSON) data to Oracle Database and allows the database to enforce that JSON stored in the Oracle Database conforms to the JSON rules.

7.Online migration of table partition or subpartition :

To migrate the table partition and subpartition to different tablespace can be moved to different tablespace online or offline.

8.Invisible Columns :

Oracle 12c introduces new feature of invisible columns in the table.When specific column is defined as invisible it will not appear in generic queries.It is pretty easy to add invisible columns or modify invisible columns.

Example :

Create table Employee

(Employee_no Number(5),

salary number(8) invisible);

Table Visible:

Alter table Employee

Modify (salary visible);

Oracle 11g vs 12c

Oracle 11g vs 12c :

Oracle 11G Oracle 12C
Indexing: User can create only one index on one column. The Invisible index feature has been implemented in Oracle 11G. Indexing: User can create more than one index on same column. Only one type of index on column is usable at a same time.
Newly added Functions: The newly added function are not considering the performance tuning of the data. The newly added functions in oracle 11G are LISTAGG and Nth_value functions. Newly added Functions: Oracle 12C has added the new feature approx_count_distinct () which provides approximate count distinct aggregation.
Caching in Oracle 11G: The Oracle 11G caching In memory parallel query did not working well with multiple scans contended for cache memory.

Full Database caching is not implemented in Oracle 11G.

Caching in Oracle 12C: The new cache mechanism called as big table cache is used to improve the performance for full table scan. This new concept is known as Automatic big table caching in Oracle 12C.Big table cache provides significant performance improvement for full table scan.

Oracle 12C provides the feature of full database caching to improve the significant performance benefits especially for workloads that will be previously limited by I/O throughput or response time.

 

In Memory table :

Oracle 11G supports the concept of In-memory tables, which will, used to improve the full table scans. The user needs to put the table in the memory so that user can access the fast data.

Oracle 11G does not support the In memory aggregation concept.

In Memory aggregation :

Oracle 12C supports the In memory aggregation concept which is very useful in star queries.

Table partition and subpartition movement : To move the partition and subpartition from one tablespace to other tablespace user needs to write complex procedural logic. Table partition and subpartition movement : To migrate the table partition and subpartition oracle 12c uses 2 methods one is  with online keyword and second is offline method.
Invisible columns : In Oracle 11g,the couple of good enhancements introduced in form of invisible indexes and virtual columns. The invisible column has not been introduced in oracle 11g. Invisible columns : In Oracle 12c R1,User can define the invisible columns in the table. When column is defined as invisible column it will not come in generic query. It is explicitly referred to SQL statement or condition in SQL statement.

Hope these article will help every oracle user to find out the difference between Oracle 11g vs 12c.