Enhancements in Oracle 12c Database Part2

Invisible columns

In Oracle 11g R1, Oracle introduced a couple of good enhancements in the form of invisible indexes and virtual columns. Taking the legacy forward, invisible column concepts has been introduced in Oracle 12c R1. A Column defined as invisible, will not appear in generic queries (select * from). An Invisible Column needs to be explicitly referred to in the SQL statement or condition. Also invisible column must be explicitly referred in INSERT statement to insert the data into invisible columns.

Example:

If you want to make the column visible again

SQL> ALTER TABLE my_table MODIFY (password visible);

When the columns are defined as invisible, they won’t be displayed when you describe the table structure. However, you can display the information about the invisible columns by setting the following on the 

SQL*Plus prompt: 

SQL> SET COLINVISIBLE ON|OFF

The above setting is only valid for DESCRIBE command. It has not effect on the SELECT statement results on the invisible columns. Now one would be wondering where one could use the feature of invisible. Well it could be handy when you are adding a column to your table, but you don’t want any existing code to be impacted by it.

Another case where it could be useful is when using audit columns. Columns as the creation_dt, update_dt, user_creation and user_update will only be of any added value when you would like to audit a certain column. Packages with inserts, updates, references to this table will not be impacted by the creation of this column.

On the other hand there is also a risk that you forget that this column is in there, because you have to explicitly call for it (a describe or select * will not show this column).

Most importantly it could be handy, but don’t forget this column or it will pollute your table.

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 on 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 sub-partitions 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.

Session level sequences

A new SESSION level database sequence can be created now in 12c to support the session level sequence values. These types of sequences are most useful and suitable on global temporary tables that have session level existence.  Session level sequences produce a unique range of values that are limited within the session, not across the sessions. Once the session ends, the state of the session sequences also goes away.
The following example explains creating a session level sequence:

SQL> CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION;

SQL> ALTER SEQUENCE my_seq GLOBAL|SESSION; 
The CACHE, NOCACHE, ORDER or NOORDER clauses are ignored for SESSION level sequences.

<<<Previous     Continue>>>