Oracle Tips by Burleson Consulting
Oracle partitioning is a
divide-and-conquer approach to improving Oracle maintenance and SQL
performance. Anyone with un-partitioned databases over 500 gigabytes is
courting disaster. Databases become unmanageable, and serious problems
SQL may perform poorly -
Without Oracle partitioning, SQL queries with
full-table scans take hours to complete. In a full scan, the
smaller the Oracle partition, the faster the performance. Also, index range scans
Recovery - Files recovery takes
days, not minutes
Maintenance - Rebuilding indexes
(important to re-claim space and improve performance)
many compelling reasons to implement Oracle partitioning for larger databases,
and Oracle partitioning has become the de-facto standard for systems over 500
gigabytes. Oracle partitioning has many benefits to improve
performance and manageability:
- 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.
– Oracle partitioning allows for multi-level keys, a combination
of the Range and List partitioning technique. The table is first
range-partitioned with Oracle partitioning, and then each individual range-partition is further
sub-partitioned using a list partitioning technique with Oracle
partitioning. Unlike composite
Range-Hash Oracle partitioning, the content of each sub-partition represents
a logical subset of the data, described by its appropriate Range and
List Oracle partition setup.
backups - A DBA can back-up a single Oracle partition of a table, rather
than backing up the entire table, thereby reducing backup time.
overhead – Because older Oracle partitioned tablespaces can be marked as
read-only, Oracle has less stress on the redo logs, locks and latches,
thereby improving overall performance. For more details, read
Robert Freeman's discussion of
management – 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.
SQL – Oracle is partition-aware, and some SQL may improve is speed
by several orders of magnitude (over 100x faster).
range scans – Oracle partitioning physically sequences rows in
index-order causing a dramatic improvement (over 10x faster) in the
speed of partition-key scans.
scans – Oracle partition pruning only accesses those data blocks required
by the query.
joins – Oracle partition-wise joins take the specific sub-set of the
query partitions, causing huge speed improvements on nested loop and
– Oracle parallel query for partitions improves batch load speed.
Oracle partitioning has a very-fast payback time and the immediate improvements
to performance and stress reduction on the Oracle server makes it a
Please see related partitioning pages:
interval partitioning tips
Oracle Partition Key Statistics & tuning
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.