oracle 11g 并行DDL

最新推荐文章于 2023-06-20 18:12:57 发布

原创最新推荐文章于 2023-06-20 18:12:57 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #并行DDL

SQL--优化专栏收录该内容

11 篇文章

订阅专栏

本文介绍Oracle11g数据库中支持并行执行的数据定义语言（DDL）操作，包括创建索引、表及分区等，并解释了如何通过指定PARALLEL子句来实现并行化，同时探讨了并行度的确定方法及其限制。

oracle 11g 并行DDL

以下SQL DDL运行“并行化”

非分区表或索引并行DDL：

CREATE INDEX
CREATE TABLE ... AS SELECT
ALTER INDEX ... REBUILD

分区表或索引并行DDL：

CREATE INDEX
CREATE TABLE ... AS SELECT
ALTER TABLE ... [MOVE|SPLIT|COALESCE] PARTITION
ALTER INDEX ... [REBUILD|SPLIT] PARTITION
- This statement can be executed in parallel only if the (global) index partition being split is usable.

以上所有的DDL语句均可以在NOLOGGING模式下以并行或者串行方式运行。

Parallel CREATE TABLE ... AS SELECT statements on partitioned tables and parallel CREATE INDEX statements on partitioned indexes execute with a DOP equal to the number of partitions.

Parallel DDL cannot occur on tables with object columns. Parallel DDL cannot occur on nonpartitioned tables with LOB columns.

限制：并行DDL不能使用在含对象列（object column）的表；并行DDL不能使用在含有LOB列的未分区表。

Decision to Parallelize

DDL operations can be executed in parallel if a PARALLEL clause (declaration) is specified in the syntax. For CREATE INDEX and ALTER INDEX ... REBUILD or ALTER INDEX ... REBUILD PARTITION, the parallel declaration is stored in the data dictionary.

You can use the ALTER SESSION FORCE PARALLEL DDL statement to override the parallel clauses of subsequent DDL statements in a session.

---可以通过在与语句中指定并行。也可以通过在会话执行 ALTER SESSION FORCE PARALLEL DDL覆盖随后的 DDL 语句中的parallel项

Degree of Parallelism

The DOP is determined by the specification in the PARALLEL clause, unless it is overridden by an ALTER SESSION FORCE PARALLEL DDL statement. A rebuild of a partitioned index is never executed in parallel.

Parallel clauses in CREATE TABLE and ALTER TABLE statements specify table parallelism. If a parallel clause exists in a table definition, it determines the parallelism of DDL statements and queries. If the DDL statement contains explicit parallel hints for a table, however, those hints override the effect of parallel clauses for that table. You can use the ALTER SESSION FORCE PARALLEL DDL statement to override parallel clauses.

--可以通过在语句中指定DOP，或者也可以使用ALTER SESSION FORCE PARALLEL DDL指定一个并行度。

--摘自Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2)

SQL> alter session enable parallel ddl;

Session altered.


SQL> explain plan for create table test_c (id_no  number,name  varchar2(10)) parallel(degree 4);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------
| Id  | Operation              | Name | Cost (%CPU)|
----------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |     0   (0)|
----------------------------------------------------

6 rows selected.
--并没有并行执行create table 因为create table不支持并行。

--CREATE TABLE AS SELECT

SQL> explain plan for create table test_f  parallel (degree 4)as select * from test_c;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3231567781

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |          |     1 |    20 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR        |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000 |     1 |    20 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT      | TEST_F   |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR  |          |     1 |    20 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL | TEST_C   |     1 |    20 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------

--CREATE INDEX

SQL> explain plan for create index idx_test_c_1 on test_c(id_no) parallel(degree 4);

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1590849757

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |              |    82 |  1066 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001     |    82 |  1066 |            |          |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IDX_TEST_C_1 |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |              |    82 |  1066 |            |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |              |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000     |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |              |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| TEST_C       |    82 |  1066 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 65536  bytes

--REBUILD INDEX

SQL> explain plan for alter index idx_test_c_1 rebuild online nologging parallel (degree 4);

Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1590849757

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT    |              |     1 |    13 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001     |     1 |    13 |            |          |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| IDX_TEST_C_1 |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |              |     1 |    13 |            |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |              |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000     |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |              |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| TEST_C       |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

15 rows selected.