Let’s talk about a “developer” feature today. If you have a need to generate a column value [mostly primary key] based on a sequence, in pre-12c Oracle database we have to create a number data type column in the table, and use a sequence that is separately created. There is no restriction that the sequence can be used only to populate one table or column.

To comply with ANSI SQL [extension] Oracle 12c now includes the “INDENTITY” column. While defining the table [CREATE TABLE] or modifying the table [ALTER TABLE] you can now define the IDENTITY column with a “sequence generator” definition.

According to Oracle documentation, the syntax for this new option in CREATE TABLE is

GENERATED keyword tells Oracle that this column value is generated.

ALWAYS is the default and specifies that the column value is never assigned and during INSERT/UDPATE statements, this column will always be evaluated to NULL – value will be populated by Oracle based on the “identity_options”.

BY DEFAULT specifies that the column value is generated by Oracle (similar to ALWAYS), but you can explicitly assign values to the column using INSERT/UPDATE statements. If you specify ON NULL with BY DEFAULT, generated value is assigned to the column only when the column value is evaluated to NULL during INSERT/UPDATE.

“identity_options” is basically the syntax for sequence generator – same as the CREATE SEQUENCE options.

There are some restrictions, please read the Oracle documentation link above. Notable one is that you can have only one IDENTITY column per table. IDENTITY column has a NOT NULL constraint automatically created.

 

 

Oracle Database 12c, now TRUNCATE statement is enhanced with  CASCADE option. This is pretty useful, that you need not worry about truncating all “child” tables before truncating the parent. With the CASCADE option, Oracle will truncate all tables with a foreign key constraint to the table being truncated. The foreign key must be ENABLED and defined with “ON DELETE CASCADE” clause. There is no set limit to the recursive level of cascaded truncate. All child, grand child, great grandchild, etc tables will be truncated.

Oracle12c also extended the same feature to truncating a partition. Now, ALTER TABLE … TRUNCATE PARTITION can include the CASCADE option as well. Specify CASCADE to truncate the corresponding partition(s) or subpartition(s) in all reference-partitioned child tables of table being truncated.

 

 

Oracle Database 12c now includes a row_limiting_clause in the SELECT statement. This new clause limits the rows returned in a more meaningful and effective way, compared to the methods available in previous releases [using ROWNUM or using Top-N rows using subqueries or using analytic functions like RANK or DENSE_RANK].

The syntax for the new clause according to the Oracle documentation is:

the row_limiting_clause follows the ORDER BY clause in the SELECT statement, though ORDER BY is not mandatory, but is recommended for consistent results if you are looking for Top-N analysis.

OFFSET clause is used to skip the specified number of rows before the limiting begins. If the offset is higher than the number of rows retrieved or is NULL, no rows are returned. ROW, ROWS keywords are there only for readability.

FETCH clause can specify the number of rows to return or a percentage of rows to return.  FIRST, NEXT keywords can be used interchangeably and is for semantic clarity only.

ONLY specifies to return exact number or percent of rows to return.

WITH TIES specifies to return all rows that have the same sort keys as the last row of the row-limited result set (requires an ORDER BY clause).

Examples:

 

 

 

 

 

 

 

 

%d bloggers like this: