Database Reference
In-Depth Information
5 loop
6 execute immediate
7 'alter table big_table2 move partition ' ||
8 x.partition_name;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
All of the information you need is there in the Oracle data dictionary, and most sites that have implemented
partitioning also have a series of stored procedures they use to make managing large numbers of partitions easy.
Additionally, many GUI tools such as Enterprise Manager have the built-in capability to perform these operations as
well, without your needing to type in the individual commands.
Another factor to consider with regard to partitions and administration is the use of
sliding windows
of data
in data warehousing and archiving. In many cases, you need to keep data online that spans the last N units of time.
For example, say you need to keep the last 12 months or the last 5 years online. Without partitions, this is generally
a massive
INSERT
followed by a massive
DELETE
two massive transactions. Lots of DML, and lots of redo and undo
generated. Now with partitions, you can simply do the following:
1.
Load a separate table with the new months' (or years, or whatever) data.
2.
Index the table fully. (These steps could even be done in another instance and transported
to this database).
3.
Attach this newly loaded and indexed table onto the
end
of the partitioned table using a
fast DDL command:
ALTER TABLE EXCHANGE
PARTITION
.
4.
Detach the oldest partition off the other end of the partitioned table.
So, you can now very easily support extremely large objects containing time-sensitive information. The old data
can easily be removed from the partitioned table and simply
dropped
if you do not need it, or it can be archived off
elsewhere. New data can be loaded into a separate table, so as to not affect the partitioned table until the loading,
indexing, and so on is complete. We will take a look at a complete example of a sliding window later.
In short, partitioning can make what would otherwise be daunting, or in some cases unfeasible, operations as
easy as they are in a small database.
Enhanced Statement Performance
The third general (potential) benefit of partitioning is in the area of enhanced statement (
SELECT
,
INSERT
,
UPDATE
,
DELETE
,
MERGE
) performance. We'll take a look at two classes of statements—those that modify information and those
that just read information—and discuss what benefits we might expect from partitioning in each case.
Parallel DML
Statements that modify data in the database may have the potential to perform
parallel DML
(
PDML
). During PDML,
Oracle uses many threads or processes to perform your
INSERT
,
UPDATE
,
DELETE
or
MERGE
instead of a single serial
process. On a multi-CPU machine with plenty of I/O bandwidth, the potential increase in speed may be large for mass
DML operations. In releases of Oracle prior to 9
i
, PDML required partitioning. If your tables were not partitioned,
you could not perform these operations in parallel in the earlier releases. If the tables were partitioned, Oracle
would assign a
maximum
degree of parallelism to the object, based on the number of physical partitions it had. This
restriction was, for the most part, relaxed in Oracle9
i
and later with two notable exceptions. If the table you wish
to perform PDML on has a bitmap index or a
LOB
column, then the table must be partitioned in order to have the
operation take place in parallel, and the degree of parallelism will be restricted to the number of partitions.
