Interval partitioning

Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.

The following restrictions apply:

·         You can only specify one partitioning key column, and it must be of NUMBER or DATE type.

·         Interval partitioning is NOT supported for index-organized tables.

·         You can NOT create a domain index on an interval-partitioned table.

·         Partition names are generated automatically with they Syntax “SYS_xxxx”

Interval partitioning based on a numeric value needs a correct interval definition. For e.g. the known “monthcode” value, a create table statement would look like:

CREATE TABLE autopartition

(

   monthcode        NUMBER(6)

,  employee          VARCHAR2 (40)

,  salary             NUMBER (10)

)

PARTITION BY RANGE

   (monthcode)

   INTERVAL ( 1)

   (

      PARTITION p_first

         VALUES LESS THAN (201202));

In the above table, only the first partition is named. If we insert data beyond the partition definition, a new partition is created.
Because the partitions are named automatically, Oracle has added new syntax to reference the specific partitions effectively by using either the generated name:

select

   *

from

   autopartitionnum partition (SYS_P62);

 or the range of a partition:

select

   *

from

   autopartitionnum partition for (201201);

Note that, because the partitions are names automatically, with an incremental numbering, partition SYS_P62 can belong to table A, whereas partition SYS_P63 van belong to table B, etc..etc..

Interval partitioning for DATE columns can be used based on year or month bases, using the NUMTOYM function in the INTERVAL table definition.

For interval partitioning on a more distinctive level, like days, a function called NUMTODSINTERVAL is available.
Creating an Interval partitioned table –seperated on month– then looks like:
Creating an Interval partitioned table –seperated on month– then looks like:

CREATE TABLE autopartitionmonth

(

   saldate        DATE

,  employee       VARCHAR2 (40)

,  salary         NUMBER (10)

)

PARTITION BY RANGE

   (saldate)

   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

   (

      PARTITION p_first

         VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')));

Or, with a separation of 1 day:

CREATE TABLE autopartitionday

(

   saldate        DATE

,  employee       VARCHAR2 (40)

,  salary         NUMBER (10)

)

PARTITION BY RANGE

   (saldate)

   INTERVAL ( NUMTODSINTERVAL (1, 'DAY') )

   (

      PARTITION p_first

         VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')));

Conclusions:

Interval partitioning can be used to automatically add new partitions when data is inserted into a partitioned table that is out of range of the available partitions.
We can easily switch to normal manual partitioning by omitting the “INTERVAL ( NUMTODSINTERVAL (1, 'DAY'))” line in the create statements

Advantages:

·         No more worries about partitions to be created automatically, oracle takes care of it

·         A partition “DEFAULT” for all values outside existing partitions is no longer necessary. This partition in old releases often worked like a trashcan.

Disadvantage

·         Partition naming is automatic, this means subsequently created partition names, can be part of different tables.

Index partitioning

When using Indexes on partitioned tables, make sure to use LOCAL indexes as much as possible. This means that the Index is also setup as a partitioned Index, with manageable parts on each table partition.
Where in the MISPRD all indexes where dropped before a certain table was loaded, in Oracle 11g it is now possible to set a certain Index or part of an index to “UNUSABLE”. This means the Index will not be updated during table changes, and will also not be used during selects from that table. As soon as the table changes are completed, the disabled Index or index parts cat simply rebuilt.

An example for the “autopartition” table above.

Let’s assume we want to insert/update 1000’s of records of the current month (201205) in this table.

Since we know the current month, we can use a statement like mentioned above to disable the Index for that particular partition:

ALTER TABLE autopartition MODIFY PARTITION FOR (201205) UNUSABLE LOCAL INDEXES; 

This sets the Index for that partition to UNUSABLE. This is confirmed by selecting the partition name with the high_value from the dba_ind_partitions view, like:

SELECT index_name, partition_name, high_value

  FROM dba_ind_partitions

 WHERE status = 'UNUSABLE;

The result looks like:

INDEX_NAME	PARTITION_NAME	HIGH_VALUE
AUTOPARTITION_X1	SYS_P69	201206

As soon as the load action is completed, all indexes partitions that are set to unusable can be found using the following query:

SELECT 'alter index '||index_name||' rebuild partition '||partition_name||';'

  FROM dba_ind_partitions

 WHERE status = 'UNUSABLE';'

Global Temporary Tables

Triggered by the huge amount of archive files generated by several databases each night, we have been looking for a solution to reduce this dramatically.

This solution exists !!

And it is called “Global Temporary Tables”.

The definition:

 Applications often use some form of temporary data store for processes that are too complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. As of Oracle 8i , the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.
Finally the data stored in global temporary tables generate NO REDO and thus reduce the amount of ARCHIVE FILES.

The usage

Above, three words are marked in bold. These words indicate the behavior of a temporary table. Although a global temporary table can exists in a global schema, like EHDA_KRG, all data inserted into that table is session-specific. This means that if more than one user session is inserting/updating data in a global temporary table, only the data of that user’s session is affected. So, although you’re working in the same temporary table you’re working on your own data. This makes the table private.

Furthermore there are two major differences in behavior. Default the table only keeps the data inserted/updated until the transaction is finished/committed. This is known as “ON COMMIT DELETE ROWS”.

The other behavior keeps all rows/changes in the table until the end of your session. This behavior is known as “ON COMMIT PRESERVE ROWS”. If you want the temporary table to behave this way, you have to include this command in the table create statement.

The creation of a global temporary table, that preserves the data until the session is ended, looks like:

CREATE GLOBAL TEMPORARY TABLE

   SELECT * FROM .....

ON COMMIT PRESERVE ROWS

AS .......

or

CREATE GLOBAL TEMPORARY TABLE

( )

ON COMMIT PRESERVE ROWS ;

And the creation of a global temporary table, that preserves the data only within a transaction, looks like:

CREATE GLOBAL TEMPORARY TABLE

ON COMMIT DELETE ROWS

AS

   SELECT * FROM .....

or

CREATE GLOBAL TEMPORARY TABLE

( )

ON COMMIT DELETE ROWS ;

Some other features:

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session. Indexes can however only be created on empty tables !!
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.
• A temporary table can only be dropped if all users that have data in it, have issued the 'truncate table' command against it, or disconnected their session. Otherwise an ORA-14452 is generated.