Oracle Interval Partitioning - RANGE Partitioning

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added. Range partitioning allows an object to be partitioned by a specified range on the partitioning key.  

For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with each month in a different partition. Therefore, every month a new partition would need to be defined in order to store rows for that month.  If a row was inserted for a new month before a partition was defined for that month, the following error would result:

ORA-14400: inserted partition key does not map to any partition 

If this situation occurs, data loading will fail until the new partitions are created.  This can cause serious problems in larger data warehouses where complex reporting has many steps and dependencies in a batch process.  Mission critical reports might be delayed or incorrect due to this problem. 

An Interval Partitioning Example

Interval partitioning can simplify the manageability by automatically creating the new partitions as needed by the data.  Interval partitioning is enabled in the table’s definition by defining one or more range partitions and including a specified interval.  For example, consider the following table:

create table
pos_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

Here, two partitions have been defined and an interval of one month has been specified.  If data is loaded into this table with a later date than the greatest defined partition, Oracle will automatically create a new partition for the new month.  In the table above, the greatest defined interval is between July 1, 2007 and August 1, 2007. 

Inserting a row that has a date later than August 1, 2007 would raise an error with normal range partitioning.  However, with interval partitioning, Oracle determines the high value of the defined range partitions, called the transition point, and creates new partitions for data that is beyond that high value.

insert into pos_data (start_date, store_id, inventory_id, qty_sold)
values ( '15-AUG-07', 1, 1, 1);

SELECT
   TABLE_NAME,
   PARTITION_NAME,
   PARTITION_POSITION,
   HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE
   TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Notice that a system generated partition named SYS_P81 has been created upon inserting a row with a partition key greater than the transition point.  Oracle will manage the creation of new partitions for any value beyond the high value.  Therefore, the values do not need to be inserted in sequence.

Since the partitions are named automatically, Oracle has added a new syntax in order to reference specific partitions effectively.  The normal way to reference a specific partition is to use the partition (partition_name) in the query:

select
   *
from
   pos_data partition (SYS_P81);

However, it would be cumbersome to look up the system generated partition name each time.  Therefore, the new syntax to specify a partition is by using thepartition for (DATE) clause in the query:

select 

   *
from
   pos_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'));

Another useful feature of partitioning is the ability to distribute partitions across different tablespaces.  With interval partitioning, this can be accomplished by naming all of the tablespaces in the table definition’s “store in” clause.  The system created partitions are then assigned to different tablespaces in a round robin manner.  For example, if the choice was to distribute the table across three tablespaces - tablespaceA, tablespaceB, and tablespaceC - use the following clause in the table definition.

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (tablespaceA, tablespaceB, tablespaceC)

Example 2:
Defining Interval for Range partitioning where each partition specifies a range of an Year ( Range is specified on A Date Type column). 
CREATE TABLE orders_range(order_id NUMBER 
                         ,client_id NUMBER 
                         ,order_date DATE)  
  PARTITION BY RANGE(order_date)
    INTERVAL (NUMTOYMINTERVAL(1,'year'))
    (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy'))

    ,PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')));

Example 3:
Defining Interval for Range partitioning on a column with Number Data type, each new partition will be created for accommodating  5000 more in range of values than previous partition as shown below:

create table test

           (sno number(6),
           last_name varchar2(30),
           salary number(6))
           partition by range(salary)
           Interval  (5000)
          (
       partition p1 values less than (5000),
       partition p2 values less than (10000),
       partition p3 values less than (15000),
       partition p4 values less than (20000));

Table created.

NUMTOYMINTERVAL

Syntax: NUMTOYMINTERVAL(n, 'interval_unit')

NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The argumentinterval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

• 'YEAR'
• 'MONTH'

interval_unit is case insensitive. 

Restrictions on  Interval Partitioning

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

• Cannot be used for index organized tables
• Must use only one partitioning key column and it must be a DATE or NUMBER
• Cannot create domain indexes on interval partitioned tables
• Are not supported at the sub-partition level

NOTE: This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

Interval Partitioning Commands

There are a few new commands to manage interval partitioning.  First, convert a range partitioned table to use interval partitioning by using command:

alter table set interval(expr).

Consider this range partitioned table:

create table
pos_data_range (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
   PARTITION BY RANGE (start_date)
(
   PARTITION pos_data_p0 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

If a row with a date of August 15, 2007 is inserted into the table, it will cause an error.

SQL> insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
  2  values ( '15-AUG-07', 1, 1, 1);
insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This range partitioned table can easily be converted to use interval partitioning by using the following command:

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Interval partitioning is now enabled, and the row with 15-AUG-07 can be inserted without error since Oracle will automatically create the new partition. To convert the table back to only range partitioning, use the following command:

alter table pos_data_range set INTERVAL();

The table is converted back to a range partitioned table and the boundaries for the interval partitions are set to the boundaries for the range partitions.

Using the same syntax, the interval can also be changed for existing interval partitioned tables.  If changing the original table to be partitioned every three months instead of monthly, use:

alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

After inserting a row with the date of 15-NOV-07, a new partition is automatically generated with a high value of 01-DEC-07.

insert into
   pos_data (start_date, store_id, inventory_id, qty_sold)
values
   ('15-NOV-07', 1, 1, 1);

SELECT
   TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE    TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P84    TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

The tablespace storage of the interval partitioned table can also be changed using a similar syntax.  For example, when using a round robin tablespace assignment for the table between tablespace1 to tablespace3, issue the following command:

alter table pos_data set STORE IN(tablespace1, tablespace2, tablespace3);

Oracle interval partitioning offers a very useful extension to range partitioning.  This greatly improves the manageability of range partitioned tables.  In addition to providing system generated new partitions, Oracle has provided a new syntax to simplify the reference of specific partitions.  Furthermore, Oracle offers a group of commands to manage the new partitioning option. 

Source: http://www.dba-oracle.com/t_interval_partitioning.htm

Virtual Columns in Oracle Database 11g Release 1

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.

CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;

Querying the table shows the inserted data plus the derived commission-based salaries.

SELECT * FROM employees;

        ID FIRST_NAME LAST_NAME      SALARY      COMM1      COMM2    SALARY1    SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 JOHN       DOE               100          5         10        105        110
         2 JAYNE      DOE               200         10         20        220        240

2 rows selected.

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1                        ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2                        ROUND("SALARY"*(1+"COMM2"/100),2)

8 rows selected.

SQL>

Notes and restrictions on virtual columns include:

• Indexes defined against virtual columns are equivalent to function-based indexes.
• Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
• Tables containing virtual columns can still be eligible for result caching.
• Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
  • Constraint on the virtual column must be disabled and re-enabled.
  • Indexes on the virtual column must be rebuilt.
  • Materialized views that access the virtual column must be fully refreshed.
  • The result cache must be flushed if cached queries have accessed the virtual column.
  • Table statistics must be regathered.
• Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
• The expression used in the virtual column definition has the following restrictions:
  • It cannot refer to another virtual column by name.
  • It can only refer to columns defined in the same table.
  • If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
  • The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

Working with Virtual Column:

Creating a Virtual Column:

We will begin by creating a simple table with a single virtual column, as follows.

SQL> CREATE TABLE t
  2  ( n1 INT
  3  , n2 INT
  4  , n3 INT GENERATED ALWAYS AS (n1 + n2) VIRTUAL
  5  );

Table created.

We can see that the virtual column is generated from a simple expression involving the other columns in our table. Note that the VIRTUAL keyword is optional and is included for what Oracle calls "syntactic clarity".

Virtual column values are not stored on disk. They are generated at runtime using their associated expression (in our example, N1 + N2). This has some implications for the way we insert data into tables with virtual columns, as we can see below.

SQL> INSERT INTO t VALUES (10, 20, 30);

INSERT INTO t VALUES (10, 20, 30)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

We cannot explicitly add data to virtual columns, so we will attempt an insert into the physical columns only, as follows.

SQL> INSERT INTO t VALUES (10, 20);

INSERT INTO t VALUES (10, 20)
            *
ERROR at line 1:
ORA-00947: not enough values

Despite the fact that we cannot insert or update virtual columns, they are still considered part of the table's column list. This means, therefore, that we must explicitly reference the physical columns in our insert statements, as follows.

SQL> INSERT INTO t (n1, n2) VALUES (10, 20);

1 row created.

Of course, fully-qualified inserts such as our example above are best practice so this should be a trivial restriction for most developers. Now we have data in our example table, we can query our virtual column, as follows.

SQL> SELECT * FROM t;

        N1         N2         N3
---------- ---------- ----------
        10         20         30

1 row selected.

Our expression is evaluated at runtime and gives the output we see above.

Indexes and Constraints

Virtual columns are valid for indexes and constraints. Indexes on virtual columns are essentially function-based indexes (this is covered in more detail later in this article). The results of the virtual column's expression are stored in the index. In the following example, we will create a primary key constraint on the N3 virtual column.

SQL> CREATE UNIQUE INDEX t_pk ON t(n3);
Index created.

SQL> ALTER TABLE t ADD
  2     CONSTRAINT t_pk
  3     PRIMARY KEY (n3)
  4     USING INDEX;

Table altered.

If we try to insert data that results in a duplicate virtual column value, we should expect a unique constraint violation, as follows.

SQL> INSERT INTO t (n1, n2) VALUES (10, 20);
INSERT INTO t (n1, n2) VALUES (10, 20)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T_PK) violated

As expected, this generates an ORA-00001 exception.

It follows, therefore, that if we can create a primary key on a virtual column, then we can reference it from a foreign key constraint. In the following example, we will create a child table with a foreign key to the T.N3 virtual column.
SQL> CREATE TABLE t_child
  2  ( n3 INT
  3  , CONSTRAINT tc_fk
  4       FOREIGN KEY  (n3)
  5       REFERENCES  t(n3)
  6  );

Table created.

We will now insert some valid and invalid data, as follows.
SQL> INSERT INTO t_child VALUES (30);

1 row created.

SQL> INSERT INTO t_child VALUES (40);
INSERT INTO t_child VALUES (40)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.TC_FK) violated - parent key not found

Adding Virtual Column

Virtual columns can be added after table creation with an ALTER TABLE statement. In the following example, we will add a new virtual column to our existing table. We will include a check constraint for demonstration purposes.

SQL> ALTER TABLE t ADD
  2     n4 GENERATED ALWAYS AS (n1 * n2)
  3        CHECK (n4 >= 10);

Table altered.

As stated earlier, an index on a virtual column will store the results of the expression. A check constraint, however, will evaluate the expression at the time of adding or modifying data to the underlying table. This is seemingly obvious, because there is no associated storage structure (i.e. index) with a check constraint.

Our new virtual column, N4, has a check constraint to ensure that the product of the N1 and N2 columns is greater than 10. We will test this, as follows.

SQL> INSERT INTO t (n1, n2) VALUES (1, 2);

INSERT INTO t (n1, n2) VALUES (1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0010001) violated

http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php

http://www.oracle-developer.net/display.php?id=510