|
|
|
Oracle Partitioning
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
occur:
-
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
become inefficient.
-
Recovery - Files recovery takes
days, not minutes
-
Maintenance - Rebuilding indexes
(important to re-claim space and improve performance)
There are
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:
-
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.
-
Robust
? 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.
-
Faster
backups - A DBA can back-up a single Oracle partition of a table, rather
than backing up the entire table, thereby reducing backup time.
-
Less
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
read-only
tablespace performance.
-
Easier
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.
-
Faster
SQL ? Oracle is partition-aware, and some SQL may improve is speed
by several orders of magnitude (over 100x faster).
- Index
range scans ? Oracle partitioning physically sequences rows in
index-order causing a dramatic improvement (over 10x faster) in the
speed of partition-key scans.
- Full-table
scans ? Oracle partition pruning only accesses those data blocks required
by the query.
- Table
joins ? Oracle partition-wise joins take the specific sub-set of the
query partitions, causing huge speed improvements on nested loop and
hash joins.
- Updates
? Oracle parallel query for partitions improves batch load speed.
In sum,
Oracle partitioning has a very-fast payback time and the immediate improvements
to performance and stress reduction on the Oracle server makes it a
slam-dunk decision.
Please see related partitioning pages:
Oracle
partitioning methods
oracle
partitioning tips
Oracle Partitioning
Oracle
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
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
|