1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Partitioning in Oracle 10g

Discussion in 'Server Administration and Options' started by Arju, Oct 24, 2008.

  1. Arju

    Arju Forum Expert

    Likes Received:
    Trophy Points:
    The primary purpose of partition is to improve performance of queries. But doing partition over a table or index does not merely serve performance benefit or your purpose of doing partition may be other than partition. It also offer benefits like,

    1.Making the administration task of big table easier.
    2.Fast deleting of data based on partition.
    3.Faster archiving.
    4.Data movement across other table.
    5.Efficient backup strategy.
    6.Management of data life cycle.

    With of Oracle 10g there are six different partitioning techniques are available of tables.

    1)Range Partition: Most common and used whenever your query or administration is based on date column. Suppose you want to archive data one month old or your database query lies within a month then you can choose range partition.

    2)List partition: If within your column data contains a list of values like, a department can be divided into several lists, CSE, EEE, MCE, CIVIL. Then you might choose to partition list wise.

    3)Hash partition: If you could not decide either to be range or list then you can choose hash partition. Like id of a table as you dont have idea about its range so you might think hash while choosing partitioning. In this case oracle internal hash algorithm is applied to the partitioned key and the row lies within a partition.

    4)Range-Hash Partition: The table is first ranged partitioned and then further each partition is sub partitioned by hash values.

    5)Range-List Partition: The table is first ranged partitioned and then further each partition is sub partitioned by list values.

    6)There is another partitioning choice of index organized table which can be partitioned by range, hash or list.

    Oracle 10.2g provides three types of partitioned indexes.

    1)Local Indexes: You can also say it as local partitioned index. Index is partitioned as the same manner as table was partitioned. Each partition of a
    local index corresponds to one and only one partition of the underlying

    2)Global Partitioned Indexes: The index is partitioned with a different partitioned key than the table is partitioned.

    3)Global non-partitioned Indexes: This one is identical to an index on a non-partitioned table. The index structure is not partitioned.