|
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:
-
Files
recovery takes days, not minutes
-
Rebuilding indexes (important to re-claim
space and
improve performance) can take days
-
Queries
with full-table scans take hours to complete
-
Index
range scans become inefficient
|
One of the best features in Oracle
data warehousing is the ability to
swap-out standard Oracle tables and
partitioned tables. Here is
the syntax of the “exchange
partition” command:
EXCHANGE PARTITION <partition_name>
WITH TABLE <new_table_name>
<including | excluding> INDEXES
<with | without> VALIDATION
EXCEPTIONS INTO <schema.table_name>;
|
Preparing a new table partition is
resource-intensive and loading the new data
into a staging area relieves and
concurrent-user issues because the staging
table is segregated from the “live” data
warehouse.
Once the data is loaded into the staging
table you can build the indexes, estimate
the CBO statistics and use the “exchange
partition” command to load the table into
the production environment with minimal
impact.
Mike Ault has published table partitioning
internals and tips in his book "Oracle
Internals":
Oracle DBA Management Scripts Book & Code
Depot
|
