Q: What is partition?

Partition:

Partitioning enables tables and indexes to be split into smaller, more manageable components and is a key requirement for any large database with high performance and high availability requirements.

Benefits:

- The primary purpose of partition is to improve performance of queries.
- Making the administration task of big table easier.
- Fast deleting of data based on partition.
- Faster archiving.
- Faster Data movement across other table.
- Efficient backup strategy.
- Better Management of data life cycle.


There are 6 different partitioning techniques on Oracle 10g:

1) Range Partition:

• Most common and used whenever your query/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.
• Here, 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.