oracle并行创建表,并行创建表和索引是怎么回事？

Why use parallel DML?

---------------------

Parallel DML can be used to speed up insert, update, and delete operations

against large database objects.  These DML operations are especially useful in

a data warehouse environment where tables and indexes tend to be very large.

Parallel DML must not be confused with the Oracle7 parallel query concept.  In

Oracle8, the parallel query concept is complemented by parallel support for

Insert, Delete, and Update.  Parallel queries can still be parallelized, even

if parallel DML is disabled.

When you can parallelize in Oracle8?

------------------------------------

1) SELECT statements.

A SELECT statement can be parallelized only if:

1.  The query includes a parallel hint.

2.  The table has parallel declaration specification. (i.e. a degree

of parallelism is specified.)

3.  The table in the query will require a full table scan.

If a query has a parallel hint and the table involved in the query has a

degree of parallelism specified, then the hint will take precedence over

the parallel declaration specification.

2) Update/Delete Statements.

An UPDATE or DELETE statement can be parallelized only on Partitioned

tables.  It is not possible to parallelize these functions on a

non-partitioned table.

Once parallel DML is enabled, an update or delete can be parallelized by:

1. Creating table with parallelism specified.

or

2. Specifying update/delete Hint in the statement.

Most parallel DML operations are used on partitioned tables and indexes.

The major advantage of parallel DML is improved performance.

Key Concepts:

-------------

In parallel execution of a single SQL statement, multiple processes work

together simultaneously and divide the work among themselves.  This can

dramatically improve performance.

In Oracle8, parallelism is augmented by providing the followings:

.Parallel Insert...Select direct load on Partitioned and

non-Partitioned tables.

.Parallel Delete on Partitioned tables.

.Parallel Update on Partitioned tables.

.Parallel Select using Rowid mechanism.

The parallelism is spread across partitions i.e. there is no

parallelism within a partition.

Parallel DML (insert, update, delete) uses a parallel execution mechanism

to speed up large DML operations against large tables or indexes.

- Parallel DMLs can only be ENABLED.

There is no init.ora parameter for this.

- Parallel DML does NOT influence parallel query.

Statements are still parallelized even if parallel DML is not

enabled.

A Commit or Rollback command MUST be executed before enabling

parallel DML or else you will receive ERROR MSG ORA-12830.

How to Enable Parallel DML:

---------------------------

The default mode of a session is Parallel DML is disabled.

SVRMGR> Commit;

SVRMGR> ALTER SESSION ENABLE PARALLEL DML;

SVRMGR>  Select * from tableA ;

SVRMGR>  Update tableA set C=C+1.

SVRMGR>  Commit;

The ALTER SESSION statement can only be executed between transactions with a

commit or rollback preceding it.  An update/insert/delete DML should end with

a Commit or rollback.

How to Disable Parallel DML:

----------------------------

To DISABLE Parallel DML:

SVRMGR> commit;

SVRMGR> Alter session disable parallel DML;

Parallel operations always have a master process that coordinates the slave

processes.  There are three types of parallelism:

1. Parallelize by ROWID ranges.  - Use this method with Select

operations.

2. Parallelize by Partitions.  - This is applicable to partitioned

tables only.  Use this method for Insert As Select, Update and

Delete DMLs.

3. Parallelize by Slave Processes.  - When new rows are inserted, they

do not have rowids, the rows are distributed among the slaves for

insertions.  This method of Insert is only applicable to

unpartitioned tables.

REMEMBER:

Parallel DML is most suitable for tables and indexes with large number of

rows.

Parallel DML can only be enabled or disabled.

Parallel DML can work on Partitioned or Non-Partitioned tables.

Parallel DML is NOT the same as the parallel query option introduced in