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.
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.
1.
Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.
Example:
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.
Example:
create
sequence test_seq start with 1 increment by 1 nocycle;
create
table test_tab
(
id number default test_seq.nextval primary
key
);
2. Invisible column:
Oracle Database 12c provides you the Invisible column feature. A Column defined as invisible, will not appear in generic queries (select * from). An Invisible Column need to be explicitly referred to in the SQL statement or condition. Also invisible column must be explicitly referred in INSERT statement to insert the database into invisible columns.
Example:
SQL>
create table my_table
2 (
3 id
number,
4 name
varchar2(100),
5
email varchar2(100),
6
password varchar2(100) INVISIBLE
7 );
SQL>
ALTER TABLE my_table MODIFY (password visible);
3. Multiple indexes on the same column
Before Oracle Database 12c, we could not have multiple indexes on a single column. In Oracle Database 12c a column may have multiple indexes but all should be of different types. Like a column may have B-Tree and BitMap Index both. But, only one index will be used at a given time.
4. VARCHAR2 length up to 32767
Form Oracle Database 12c, a varchar2 column can be sized upto 32767, which was earlier 4000. The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs). To use extended data types functionality we need to set MAX_STRING_SIZE initialization parameter to EXTENDED. The default value of MAX_STRING_SIZE is STANDARD, which restricts the maximum sizes to the traditional lengths.
5. Top-N feature
A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set
Example:
SQL>
SELECT value
2
FROM mytable
3
ORDER BY value DESC
4
FETCH FIRST 10 ROWS ONLY;
6. IDENTITY Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is a American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).
Example:
SQL>
create table my_table
2 (
3 id
number generated as identity,
4 name
varchar2(100),
5
email varchar2(100),
6
password varchar2(100) INVISIBLE
7 );
7. With Clause improvement
In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement and use it as an ordinary function. Using this construct results in better performance as compared with schema-level functions
Example:
SQL>
WITH
2
FUNCTION f_test(n IN NUMBER) RETURN NUMBER IS
3
BEGIN
4
RETURN n+1;
5
END;
6
SELECT f_test(1)
7
FROM dual
8 ;
8. Cascade for TRUNCATE and EXCHANGE partition.
With Oracle Database 12c, The TRUNCATE can be executed with CASCADE option which will also delete the child records.
9. Online RENAME/MOVE of Datafiles
Oracle Database 12c has provided a simple way to online renamed or moved data files by simply "ALTER DATABASE MOVE DATAFILE" command. Data files can also be migrated online from ASM to NON-ASM and NON-ASM to ASM easily now.
Examples:
Rename
datafile:
SQL> ALTER DATABASE MOVE DATAFILE
'/u01/oradata/indx.dbf' TO '/u01/oradata/indx_01.dbf';
Move
Datafile:
SQL> ALTER DATABASE MOVE DATAFILE
'/u01/oradata/indx.dbf' TO '/u01/oradata/orcl/indx.dbf';
NON-ASM
to ASM:
SQL> ALTER DATABASE MOVE DATAFILE
'/u01/oradata/indx.dbf' TO '+DISKGROUP_DATA01';
10. Move table partition to different Tablespace online
From Oracle 12c, it become very easy to move Table Partition to different tablespace and does not require complex steps
Example:
SQL> ALTER TABLE MY_LARGE_TABLE MOVE
PARTITION MY_LARGE_TABLE_PART1 TO TABLESPACE USERS_NEW;
11. Temporary Undo
Before Oracle Database 12c, undo records of temporary tables used to be stored in undo tablespace. With the temporary undo feature in Oracle Database 12c, the undo records of temporary tables can now be stored in a temporary table instead of stored in undo tablespace. The main benefits of temporary undo are 1) Low undo tablespace usages 2) less redo data generation. For using this feature Compatibility parameter must be set to 12.0.0 or higher and TEMP_UNDO_ENABLED initialization parameter must be Enabled.
12. DDL logging
By using the ENABLE_DDL_LOGGING initiation parameter in Oracle Database 12c, we can now log the DDL action into xml and log files to capture when the drop or create command was executed and by whom under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. The parameter can be set at the database or session levels.
Example:
SQL> ALTER SYSTEM SET
ENABLE_DDL_LOGGING=TRUE;
13. PGA_AGGREGATE_LIMIT parameter
Oracle Database 12c has provided us a way to limit PGA by PGA_AGGREGATE_LIMIT parameter. Before Oracle Database 12c there was no option to limit and control the PGA size. Oracle will automatically abort the session that holds the most untenable PGA memory when PGA limits exceeds the defined value.
14. SQL statement in RMAN
From Oracle Database 12c, we can execute any SQL and PL/SQL commands in RMAN without SQL prefix
Example:
RMAN> SELECT username,machine FROM
v$session;
15. Turning off redo for Data Pump the import
The new TRANSFORM option, DISABLE_ARCHIVE_LOGGING, to the impdp command line causes Oracle Data Pump to disable redo logging when loading data into tables and when creating indexes. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.
Example:
impdp directory=mydir dumpfile=mydmp.dmp
logfile=mydmp.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
No comments:
Post a Comment