Database Reference
In-Depth Information
communities to user communities of hundreds or thousands. Consider a data warehouse front-ended by a web-based
application: it could be accessible to literally thousands or more users simultaneously.
But a DBA performing the large batch operations, perhaps during a maintenance window, is a different story.
The DBA is still a single individual and he or she might have a huge machine with tons of computing resources available.
The DBA has only one thing to do, such as load this data, or reorganize that table, or rebuild that index. Without
parallel execution, the DBA would be hard-pressed to really use the full capabilities of the hardware. With parallel
execution, they can. The following SQL DDL commands permit parallelization:
•
CREATE
INDEX
: Multiple parallel execution servers can scan the table, sort the data, and write
the sorted segments out to the index structure.
•
CREATE TABLE AS
SELECT
: The query that executes the
SELECT
may be executed using parallel
query, and the table load itself may be done in parallel.
•
ALTER INDEX
REBUILD
: The index structure may be rebuilt in parallel.
•
ALTER TABLE
MOVE
: A table may be moved in parallel.
•
ALTER TABLE SPLIT|COALESCE
PARTITION
: The individual table partitions may be split or
coalesced in parallel.
•
ALTER INDEX SPLIT
PARTITION
: An index partition may be split in parallel.
•
CREATE/ALTER MATERIALIZED VIEW
: Create a materialized view with parallel processes or
change the default degree of parallelism.
■
See the
Oracle Database SQL Language Reference
manual for a complete list of statements that support
parallel operations.
Note
The first four of these commands work for individual table/index partitions as well—that is, you may
MOVE
an
individual partition of a table in parallel.
To me, parallel DDL is where the parallel execution in Oracle is of greatest measurable benefit. Sure, it can be
used with parallel query to speed up certain long-running operations, but from a maintenance standpoint, and from
an administration standpoint, parallel DDL is where the parallel operations affect us, DBAs and developers, the most.
If you think of parallel query as being designed for the end user for the most part, then parallel DDL is designed for the
DBA/developer.
Parallel DDL and Data Loading Using External Tables
One of my favorite features introduced in Oracle 9
i
was external tables; they are especially useful in the area of data
loading. We'll cover data loading and external tables in some detail in the next chapter, but as a quick introduction,
we'll take a brief look at these topics here to study the effects of parallel DDL on extent sizing and extent trimming.
External tables allow us to easily perform parallel direct path loads without “thinking” too hard about it.
Oracle 7.1 gave us the ability to perform parallel direct path loads, whereby multiple sessions could write directly
to the Oracle data files, bypassing the buffer cache entirely, bypassing undo for the table data, and perhaps even
bypassing redo generation. This was accomplished via SQL*Loader. The DBA would have to script multiple
SQL*Loader sessions, split the input data files to be loaded manually, determine the degree of parallelism, and
coordinate all of the SQL*Loader processes. In short, it could be done, but it was hard.
