Oracle Database Table Partitioning

Table Partitioning 

Now a days enterprises run databases of hundred of Gigabytes in size. These databases are known as

Very Large Databases (VLDB). From Oracle Ver. 8.0  Oracle has provided the feature of

table partitioning i.e. you can partition a table according to some criteria . For example you

have a SALES table with the following structure

Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993
and 1994.  And most of the time you are concerned about only one year i.e. you give queries like the following

select sum(amt) from sales where year=1991;

select product,sum(amt) from sales where year=1992

          Group by product;

Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to
year, then the performance is improve since oracle will scan only a single partition instead of whole table.

CREATING PARTITION TABLES

To create a partition table give the following statement

create table sales (year number(4),

                    product varchar2(10),

                   amt number(10,2))

     partition by range (year)

     partition p1 values less than (1992) tablespace u1,

     partition p2 values less than (1993) tablespace u2,

     partition p3 values less than (1994) tablespace u3,

     partition p4 values less than (1995) tablespace u4,

   partition p5 values less than (MAXVALUE) tablespace u5;

In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 1991 and
it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2.
Similarly p3 and p4.

In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be
able to insert any row with year above 1994.

Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces
then you can isolate problems due to failures as only a particular partition will not be available and rest of the
partitions will still be available.

The above example the table is partition by range.

 In Oracle you can partition a table by

• Range Partitioning
• Hash Partitioning
• List Partitioning
• Composite Partitioning

Range Partitioning

This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed;
for example, value of year. Performance is best when the data evenly distributes across the range

Hash partitioning

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for
performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a
specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key

The following example shows how to  create a hash partition table.

The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created
and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).

CREATE TABLE products

     (partno NUMBER,

      description VARCHAR2 (60))

   PARTITION BY HASH (partno)

   PARTITIONS 4

   STORE IN (tab1, tab2, tab3, tab4);

 List Partitioning

Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete

values for the partitioning column in the description for each partition. This is different from range partitioning, where a

range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to

partition mapping.

List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally

The following example creates a table with list partitioning

Create table customers (custcode number(5),

                  Name varchar2(20),

                  Addr varchar2(10,2),

                  City varchar2(20),

                  Bal number(10,2))

     Partition by list (city),

Partition north_India values (‘DELHI’,’CHANDIGARH’),

Partition east_India values (‘KOLKOTA’,’PATNA’),

Partition south_India values (‘HYDERABAD’,’BANGALORE’,

                               ’CHENNAI’),

Partition west India values (‘BOMBAY’,’GOA’);

If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the

city column the row is stored in that partition.

COMPOSITE PARTITONING

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using
the hash method. Composite partitions are ideal for both historical data and striping, and provide improved
manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating composite partitions, you specify the following:

• Partitioning method: range
• Partitioning column(s)
• Partition descriptions identifying partition bounds
• Subpartitioning method: hash
• Subpartitioning column(s)
• Number of subpartitions for each partition or descriptions of subpartitions

The following statement creates a composite-partitioned table. In this example, three range partitions are created, each
containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the 
STORE IN clause distributes them across the 4 specified tablespaces (tab1, ...,tab4).

CREATE TABLE PRODUCTS (partno NUMBER,

      description VARCHAR(32),

       costprice NUMBER)

  PARTITION BY RANGE (partno)

     SUBPARTITION BY HASH(description)

    SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)

      (PARTITION p1 VALUES LESS THAN (100),

       PARTITION p2 VALUES LESS THAN (200),

       PARTITION p3 VALUES LESS THAN (MAXVALUE));

     

ALTERING PARTITION TABLES

To add a partition

You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition

at the beginning or in the middle of a table, use the SPLIT PARTITION clause.

For example to add a partition to sales table give the following command.

alter table sales add partition p6 values less than (1996);

To add a partition to a Hash Partition table give the following command.

Alter table products add partition;

Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.

To add a partition by user define name and in your specified tablespace give the following command.

Alter table products add partition p5 tablespace u5;

To add a partition to a List partition table give the following command.

alter table customers add partition central_India

            values (‘BHOPAL’,’NAGPUR’);

Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions
of the table.

Coalescing Partitions

Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.

To coalesce a hash partition give the following statement.

Alter table products coalesce partition;

This reduces by one the number of partitions in the table products.

DROPPING PARTITIONS

To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.

Alter table sales drop partition p5;

Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.

Alter index sales_ind rebuild;

To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop
the partition like this

Delete from sales where year=1994;

Alter table sales drop partition p4;

This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Another method of dropping partitions is give the following statement.

ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;

This causes the global index to be updated at the time the partition is dropped.

 Exchanging a Range, Hash, or List Partition

To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITIONstatement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned.

ALTER TABLE stocks

    EXCHANGE PARTITION p3 WITH stock_table_3;

 Merging Partitions

Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.

You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.

You can only merged two adjacent partitions, you cannot merge non adjacent partitions.

For example the merge the partition p2 and p3 into one partition p23 give the following statement.

Alter table sales merge partition p2 and p3 into

partition p23;

 Modifying Partitions: Adding Values

Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.

The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      ADD VALUES ('KOCHI', 'MANGALORE');

Modifying Partitions: Dropping Values

Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.

You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITION statement instead. 

The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.

The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      DROP VALUES (‘KOCHI’,’MANGALORE’);

 SPLITTING PARTITIONS

You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command.

Alter table sales split partition p5 into

  (Partition p6 values less than (1996),

   Partition p7 values less then (MAXVALUE));

TRUNCATING PARTITON

Truncating a partition will delete all rows from the partition.

To truncate a partition give the following statement

Alter table sales truncate partition p5;

LISTING INFORMATION ABOUT PARTITION TABLES

To see how many partitioned tables are there in your schema give the following statement

Select * from user_part_tables;

To see on partition level partitioning information

Select * from user_tab_partitions;

Instead of Triggers on Views Example in Oracle Database

INSTEAD OF trigger example

Triggers are not very commonly used in Oracle apps customization. This is because Oracle discourages using triggers on base tables. For certain business triggers are created on custom tables but mainly for INSERT/UPDATE/DELETE. We shall discuss the INSTEAD OF trigger which is used very sparingly.
The INSTEAD OF trigger is always created on a VIEW instead of a table. The main reason for the existence of the INSTEAD OF trigger is for Oracle forms to insert data directly into the base table instead of inserting data into the view which in turn would insert data into the base table on which the VIEW is created. But why should we go into this trouble?
This because there could be a possibility of creating a view on a table but the view might not include the primary or the unique key of the table. In such a case the insert into the view will fail. To avoid this failure an INSTEAD OF trigger is created on this view so that the insert can be written separately in the trigger and successfully insert data.
The following is a demo of an INSTEAD OF trigger
Create a table

1 2 3 4 5 6 7 8 9 10

CREATE TABLE XX_DATA (   XXNUM      VARCHAR2(10 BYTE),   XXTITLE    VARCHAR2(20 BYTE),   BUYERID    VARCHAR2(20 BYTE),   BUYERNAME  VARCHAR2(20 BYTE),   ITEMNUM    VARCHAR2(10 BYTE),   ITEMQTY    VARCHAR2(10 BYTE) ) /

 Create a view on this table

1 2 3 4 5

CREATE OR REPLACE VIEW XX_DATA_V (xxnum, xxtitle, buyerid, buyername, itemnum, itemqty) AS    SELECT "XXNUM", "XXTITLE", "BUYERID", "BUYERNAME", "ITEMNUM", "ITEMQTY"      FROM xx_data /

Check the table

1	SELECT * from XX_DATA_V

No records in table

No records exist in the table now as no data has been inserted into it.
Insert data into the table and the view and query the view/table

1 2 3 4 5

INSERT INTO XX_DATA (XXNUM, XXTITLE, BUYERID) VALUES (10, 20, 30);    INSERT INTO XX_DATA_V (XXNUM, BUYERNAME, BUYERID) VALUES (20, 'TEST', 1.3);    SELECT * from XX_DATA_V;

Data inserted

Both records are inserted into the table.
Now let us create an INSTEAD OF trigger on the view, XX_DATA_V
This trigger will simply enter 2 hard coded columns into 1 row of the table for each insert call

1 2 3 4 5 6

CREATE OR REPLACE TRIGGER APPS.xx_data_ins_v INSTEAD OF INSERT ON APPS.XX_DATA_V FOR EACH ROW BEGIN     INSERT INTO xx_data (xxnum, xxtitle) VALUES (150, 'TT'); END;

Creating the INSTEAD OF trigger

Now let us insert a record into the view, XX_DATA_V

1	INSERT INTO XX_DATA_V (BUYERNAME, BUYERID) VALUES ('XXTEST', 9.5)

Let us query the view/table

1	SELECT * from XX_DATA_V

Table data

Notice the 3rd row at the bottom. This row has been inserted in the previous insert into the view. Since the view had an INSTEAD OF trigger on it the INSTEAD OF trigger executed the insert statement within it and hence none of the data, that was originally inserted, has gone into the table. The INSTEAD OF trigger has completely disregarded the statement.


OR Below is Simple Example

Instead of Triggers on Views Example in Oracle

Instead of triggers in oracle database are defined on views only. A normal DML trigger executes only when a DML operation is issued on a table. Whereas instead of trigger fires when a DML statment is issued on the view. Instead-of triggers must be row level.

Create Instead of Trigger - Example:

Take a look at the following view defintion:

Create or replace view emp_dept_join as
  Select d.department_id,
  d.department_name,
  e.first_name,
  e.last_name
  from   employees e,
         departments d
  where  e.department_id = d.department_id;

As the view consists of two table joins, it is illegal to insert records into this view as the insert requires both the underlying tables to be modified.

By creating an instead-of trigger on the view, you can insert the records into both the underlying tables. An examples is shown below:

CREATE OR REPLACE TRIGGER insert_emp_dept
  INSTEAD OF INSERT ON emp_dept_join
DECLARE
  v_department_id departments.department_id%TYPE;
BEGIN
  BEGIN
    SELECT department_id INTO v_department_id
    FROM   departments
    WHERE  department_id = :new.department_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO departments (department_id, department_name)
             VALUES (dept_sequence.nextval, :new.department_name)
             RETURNING ID INTO v_department_id;
  END;

  INSERT INTO employees (employee_id, first_name, last_name, department_id)
         VALUES(emp_sequence.nextval, :new.first_name, :new.last_name, v_department_id);
END insert_emp_dept;
/