Previn's Planet

1) Row Limiting Clause for Top-N Queries

A Top-N query is used to retrieve the top N rows from an ordered set.

Table Demo12C
NUM_VAL

_________

1
2
4
5
3
6
10
7

SELECT NUM_VAL FROM Demo12C ORDER BY NUM_VAL DESC FETCH FIRST 5 ROWS ONLY;
===============================================

2) Temporal Validity

This feature adds a time dimension to each row in the table consisting of two date-time
columns to denote validity of data. The Temporal Validity is controlled by the user or
application who defines the valid time dimension for the table via the PERIOD FOR clause

For e.g Consider a Sales_History table.

ALTER TABLE Sales_History PERIOD FOR active_date;DESC Sales_History will not show the column active_date because it is hidden.Select column_name,data_type from user_tab_cols where table_name='Sales_History' and
hidden_column='YES';

COLUMN_NAME DATA_TYPE
-------------------------------------------------- ----------------------active_date NUMBERactive_date_END TIMESTAMP(6) WITH TIME ZONEactive_date_START TIMESTAMP(6) WITH TIME ZONE

SELECT * FROM Sales_History AS OF PERIOD FOR active_date TO_TIMESTAMP('01-JUN-2012 12.00.01 PM')
======================================================
3) Duplicate Indexes

In 11 G or previous version it was not possible to create multiple indexes either on the same column or set of columns. 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.

CREATE INDEX emp_ind1 ON EMP(ENO,ENAME);

CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
==============================================================
4) Identity Columns

In previous releases of the Oracle database, there was no direct equivalent of the AutoNumber or Identity functionality of other database engines. Instead, this behavior had to be implemented using a combination of sequences and triggers.

CREATE TABLE identity_test (
id NUMBER GENERATED AS IDENTITY,
txt_descVARCHAR2(30)
);

INSERT INTO identity_test (txt_desc) VALUES ('12c examples');

The Identify_options are :

START WITH: 1, INCREMENT BY: 1,
MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1

Courtsey : Dijeesh M J @ Letterkenny