SQL Zone is brought to you in partnership with:

Darshan has posted 4 posts at DZone. View Full User Profile

Oracle Database 12C new features

10.21.2013
| 3008 views |
  • submit to reddit

1) Table partition maintenance enhancements:-

Adding multiple new partitions

Before Oracle 12c R1, it was only possible to add one new partition at a time to an existing partitioned table. To add more than one new partition, you had to execute an individual ALTER TABLE ADD PARTITION statement to every new partition. Oracle 12c provides the flexibility to add multiple new partitions using a single ALTER TABLE ADD PARTITION command. The following example explains how to add multiple new partitions to an existing partitioned table:

SQL> CREATE TABLE emp_part
				(eno number(8), ename varchar2(40), sal number (6))
		PARTITION BY RANGE (sal)
		(PARTITION p1 VALUES LESS THAN (10000),
		 PARTITION p2 VALUES LESS THAN (20000),
		 PARTITION p3 VALUES LESS THAN (30000)
		);

Now lets add a couple of new partitions:

SQL> ALTER TABLE emp_part ADD PARTITION
		PARTITION p4 VALUES LESS THAN (35000),
		PARTITION p5 VALUES LESS THAN (40000);

In the same way, you can add multiple new partitions to a list and system partitioned table, provided that theMAXVALUE partition doesn’t exist.

How to drop and truncate multiple partitions/sub-partitions

As part of data maintenance, you typically either use drop or truncate partition maintenance task on a partitioned table.  Pre 12c R1, it was only possible to drop or truncate one partition at a time on an existing partitioned table.  With Oracle 12c, multiple partitions or sub-partitions can be dropped or merged using a single ALTER TABLE table_name {DROP|TRUNCATE} PARTITIONS command.

The following example explains how to drop or truncate multiple partitions on an existing partitioned table:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;
SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;

To keep indexes up-to-date, use the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause, shown below:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE emp_part TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;

If you truncate or drop a partition without the UPDATE GLOBAL INDEXES clause, you can query the columnORPHANED_ENTRIES in the USER_INDEXES or USER_IND_PARTITIONS dictionary views to find out whether the index contains any stale entries.

Splitting a single partition into multiple new partitions

The new enhanced SPLIT PARTITION clause in 12c will let you split a particular partition or sub-partition into multiple new partitions using a single command. The following example explains how to split a partition into multiple new partitions:

SQL> CREATE TABLE emp_part
				(eno number(8), ename varchar2(40), sal number (6))
		PARTITION BY RANGE (sal)
		(PARTITION p1 VALUES LESS THAN (10000),
		 PARTITION p2 VALUES LESS THAN (20000),
		 PARTITION p_max VALUES LESS THAN (MAXVALUE)
		);

SQL> ALTER TABLE emp_part SPLIT PARTITION p_max INTO
		(PARTITION p3 VALUES LESS THAN (25000),
		 PARTITION p4 VALUES LESS THAN (30000), PARTITION p_max);

Merge multiple partitions into one partition

You can merge multiple partitions to a single partition using a single ALTER TBALE MERGE PARTITIONS statement:

SQL> CREATE TABLE emp_part
		(eno number(8), ename varchar2(40), sal number (6))
		PARTITION BY RANGE (sal)
		(PARTITION p1 VALUES LESS THAN (10000),
		 PARTITION p2 VALUES LESS THAN (20000),
		 PARTITION p3 VALUES LESS THAN (30000),
		 PARTITION p4 VALUES LESS THAN (40000),
		 PARTITION p5 VALUES LESS THAN (50000),
		 PARTITION p_max (MAXVALUE)
		);

SQL> ALTER TABLE emp_part MERGE PARTITIONS p3,p4,p5 INTO PARTITION p_merge;

If the range falls in the sequence, you can use the following example:

SQL> ALTER TABLE emp_part MERGE PARTITIONS p3 TO p5 INTO PARTITION p_merge;
2) 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:
create sequence test_seq start with 1 increment by 1 nocycle;

create table test_tab
(
    id number default test_seq.nextval primary key
);


3) 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);  
0
Average: 4 (1 vote)
Published at DZone with permission of its author, Darshan Bobra.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Previn Kutty replied on Wed, 2013/10/23 - 10:10am

4) 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; 
===============================================

5) 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')
======================================================
6) 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; 
==============================================================
7) 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

Darshan Bobra replied on Thu, 2013/10/24 - 7:33am in response to: Previn Kutty

thanks  previn kutty to update my article ..yup these feature is also provide by 12c database....


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.