Table Partitioning in Oracle

Partitioning:

                A partitioned object has multiple pieces that can be managed either individually or collectively

Partitioning Key :

                Each row in a partitioned table is unambiguously assigned to  a partition. The partition key consists of one or more columns that determine the partition where each row is stored.

When to Partition a Table:

Here are some suggestions

• ·         Table greater than 2GB
• ·         Tables containing historical data, in which new data is added into new partition
• ·         When contents of table to be distributed across different storage devices

Benefits of Partitioning :

• ·         Performance
• ·         Availability
• ·         Manageability

Partitioning Strategies:

•      Range based
•       List based
•       Hash

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. Rows are mapped into partitions based on a hash value of the partitioning key

CREATE table SCOTT.STUDENT_HASH_PARTITION

(STDNO number(8),

  FIRSTNAME varchar2(10),

  MIDDLENAME varchar2(10),

  LASTNAME varchar2(10),

  MOBILENO varchar2(12),

  PHONENO varchar2(12)

)

partition by hash(STDNO)

partitions 4

STORE IN (STUDENT1,STUDENT2,STUDENT3,STUDENT4);

Benefits of Table Partitioning :

• Instead of having a 100 gig tablespace to backup, you have 100, 1 gig tablespaces. (each tablespace spends less time in backup mode, reduces the amount of potential extra redo, reduces the amount of manual recovery you need to do if the instance failes during backup).  same with restores.
• instead of having an index on a 100 gig table, you have say 100 indexes on 1 gig tables.  You can rebuild each one fast.... (and online with minimal overhead).  rebuilding a single index on a 100 gig table -- that would take maybe 100 times longer then each individual rebuild (true parallel query might reduce that).
• You can analyze each partition quickly, instead of running an analyze on a 100 gig table.
• You can reorg each partition independent of any other partition
• You can easily redistribute the load over many disks, you now have evenly distributed the data into 100 1 gig partitions, move them at will.  If you did this manually -- you would have a heck of a time moving the stuff around.

How can I delete the data in a single partition :
   SQL> ALTER TABLE <TABLENAME> DROP PARTITION <PARTITIONNAME>;