Oracle 12C new features for Developers

As we all know most of our Oracle systems are migrating from 11g to 12c then this is the right time to make ourselves familiar with new features of 12c. In this forum, I will be covering from developer’s perspective.

1) Invisible columns :->  The idea of invisible columns is similar to the idea of creating a view on a table while leaving out the columns that you do not want the end-user to see.
In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa:
SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables, and cluster tables won’t support invisible columns.

2) Handling default columns in Oracle 12c:->  Oracle 12c enhanced the defaulting methods of table columns. Now, a column can default to a sequence. In addition, a column can have a default value which will be used only when the column is NULL.

Before Oracle 12c, if a column is expected to pull values from a sequence, it must be a done in a trigger or a procedurally assigned in the program unit. In 12c, a sequence can be directly assigned to a column in the table.
In the below illustration, we create a table T with three columns. Column ‘X’ is defaulted to a sequence S, while column Z defaults to 13 when NULL.

sql> create sequence s;
Sequence created.
sql> create table t
( x number default s.nextval primary key,
y number,
z number default on null 13 );
Table created.
sql> insert into t (y,z) values ( 55, NULL );
sql> insert into t (y,z) values ( 100, NULL );
sql> insert into t (x,y,z) values (-3,-2,-3);
sql> select * from t;
X          Y          Z
———- ———- ———-
1         55         13
2        100         13
-3         -2         -3

This feature will standardize numeric primary key also it will reduce the use of the trigger and decode function for setting up default values.

3) Truncate table CASCADE:->In the previous releases, there wasn’t a direct option provided to truncate a master table while it is referred to by the child tables and child records exist. The TRUNCATE TABLE with CASCADE option in 12c truncates the records in the master table and automatically initiates recursive truncate on child tables too, subject to foreign key reference as DELETE ON CASCADE. There is no CAP on the number of recursive levels as it will apply to all child, grand child, and great grandchild etc.

This enhancement gets rid of the prerequisite to truncate all child records before truncating a master table. The new CASCADE clause can also be applied on table partitions and subpartitions etc..

SQL> TRUNCATE TABLE <table_name> CASCADE;
SQL> TRUNCATE TABLE <table_name> PARTITION <partition_name> CASCADE;

An ORA-14705 error will be thrown if no ON DELETE CASCADE option is defined with the foreign keys of the child tables.

4) Move table partition to different Tablespace online:-> From Oracle 12c, it becomes 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;

Above feature will make post partition activities easier.

5) Top N queries using FETCH FIRST and OFFSET:->This feature can be really useful for ETL and reporting purpose. Oracle 12c eases the cases of top-N analysis by introducing FETCH FIRST clause in SQL language. The clause internally sorts the query result set and retrieves the specified number of rows from the set. There are two flavors of pulling out the data from the result set i.e. either retrieve fixed number of rows (FETCH FIRST 10 ROWS ONLY) or retrieve a percentage of rows from the result set (FETCH 5 PERCENT ONLY). If one needs to retrieve the data set after excluding a certain number of rows from the top, OFFSET clause can be used. If more than one row in the set satisfies the fetch condition, retrieve all the rows obeying the boundaries using WITH TIES clause.

The below SQL query fetches top-5 employees sorted by their salary, in the company.

SELECT employee_id, last_name
FROM employees
ORDER BY salary
FETCH FIRST 5 ROWS ONLY ;

The below SQL query fetches top 5% employees sorted by their salary and includes the those with the same salary.
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary

FETCH FIRST 5 PERCENT ROWS WITH TIES ;
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
the starting point for the FETCH is OFFSET+1.

6) Multiple indexes on the same column:->This feature can be particularly useful when you want to change the index definition in some way but you want to reduce the time in which the corresponding columns might not be indexed.
Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.
Here’s the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME);
SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;

7) DDL logging :-> There was no direction option available to log the DDL action in the previous releases. In 12cR1, you can now log the DDL action into XML and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature. The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an XML and a log file under the $ORACLE_BASE/diag/RDBMS/DBNAME/log|ddl location. An XML file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.

To enable DDL login
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the XML/log file:
o CREATE|ALTER|DROP|TRUNCATE TABLE
o DROP USER
o CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
The DDL logging data is written in XML format to the file located at $DIAG\rdbms\DBName\INSTNAME\log\ddl

8) Identity Columns :->A column in a table can be marked as identity column which generates its value by itself. Oracle implicitly creates a sequence of default configuration for the identity column. For each insert operation, the current value of the sequence gets automatically assigned to the identity column. The feature syntax is as below.

SQL> create table t_id_col ( x number
generated by default as identity
( start with 10 increment by 15 ) primary key,
y varchar2(30))
/
Table created.
SQL> insert into t (x,y) values ( 1, ‘hello1’ );
1 row created.
SQL> insert into t (x,y) values ( default, ‘hello2’ );
1 row created.
SQL> insert into t (y) values ( ‘hello3’ );
1 row created.
SQL> select * from t;
X                                       Y
——————————  ———————————
1                                      hello1
10                                    hello2
25                                    hello3

9) Extended String Datatypes :->Until Oracle 11g SQL, the maximum precision allowed for a string type column was 4000. In Oracle 12c, the precision has been increased up to 32767 bytes or 32K. The new string data types will be known as Extended String Types in Oracle 12c. The feature is controlled by an initialization parameter MAX_STRING_SIZE. The database must be in upgrade mode to enable this feature. Note that once the feature is enabled, thereafter the parameter cannot be disabled.

ALTER SYSTEM SET max_string_size = ENABLED;

This will increase the use of varchar2 data type in our applications.

10) 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
FUNCTION f_test(n IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN n+1;
END;
SELECT f_test(1)
FROM dual
;

11) PGA_AGGREGATE_LIMIT:->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.

 

Talk to us

Leave a Reply