“If double his strength, divide him” writed Sun tzu, Oracle particioning is a matter of “divide and conquer” aproach to improving Oracle maintenance and SQL performance. Oracle partitioning has become the de-facto standard for systems over 500 gigabytes. without Oracle partitioning, databases can perform poorly and become unmanageable, SQL queries with full-table scans take lot of hours to complete, in a full scan, the smaller the Oracle partition, the faster the performance and serious problems occur:
There are many compelling reasons to implement Oracle partitioning for medium and larger databases:
1.Partitioning for faster backup. A DBA can backup a single partition of a table, rather than backing up the entire table, thereby reducing backup time.
2.Less overhead i.e. I/O, less stress on the redo logs, locks and latches, thereby improving overall performance.
3.Faster SQL, no full table scan from Oracle partition pruning. But FTS is not always bad than of index scan, I mean its not a thumb rule that FTS are always bad in compare to index scan; as always “It Depends”. Oracle is partition-aware, and some SQL may improve is speed by several orders of magnitude (over 100x faster!!).
4.Easy maintenance. Maintenance of Oracle partitioned tables is improved because maintenance can be focused on particular portions of tables. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.
5. Stable – Oracle partitioning is a very stable technology and has been used in Oracle since Oracle8, back in 1997. Each new release of Oracle improves Oracle partitioning features.
6.Practical usages of partitioning depends upon the database use, i.e. if it is Data warehouse then partition type would be date range, for OLTP it would be frequently accessed key, for ODS the partition type would be date range and a key.
When to Partition a Table??
– Tables greater than 2 GB should always be considered as candidates for partitioning.
– Tables containing historical data, in which new data is added into the newest partition. A typical example is a
– Historical table where only the current month’s data is updatable and the other 11 months are read only.
– When the contents of a table need to be distributed across different types of storage devices.