oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step

Oracle SQL

Tutorial for Oracle SQL


Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions

Download Oracle 11g Software

Links to Download Oracle 11g for Linux, Windows etc.

How to Create and Manage Partition Tables in Oracle

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.


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

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, ...).

     (partno NUMBER,
      description VARCHAR2 (60))
   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 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:

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).

      description VARCHAR(32),
       costprice NUMBER)
     SUBPARTITION BY HASH(description)
    SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)



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;


NEXT ( Altering and Managing Partition Tables)



HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats

Forms Data Loader

Tool to load data into Oracle Applications 11i / R12 using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved