oracle 11g 并行DDL
来源:互联网 发布:2017淘宝对职业退货师 编辑:程序博客网 时间:2024/06/05 17:33
oracle 11g 并行DDL
以下SQL DDL运行“并行化”
非分区表或索引并行DDL:
CREATE
INDEX
CREATE
TABLE
...AS
SELECT
ALTER
INDEX
...REBUILD
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 parallelCREATE
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 withLOB
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. ForCREATE
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 anALTER
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.
- oracle 11g 并行DDL
- Oracle 并行DDL简介
- oracle 11g 并行DML
- Oracle 11g 并行DML
- DDL_LOCK_TIMEOUT --Oracle 11g DDL 的 wait选项
- Oracle 11g Golden Gate DDL单向同步实例演示
- ORACLE 11G R2中的并行执行,dbms_parallel_execute
- Oracle 11g merge into log error及并行注意事项
- DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1
- 『ORACLE』 对永久表空间进行DDL操作(11g)
- [Oracle 11g r2(11.2.0.4.0)]sql实现ddl记录日志
- 并行DDL浅析
- Oracle 11g 使用 dbms_parallel_execute 对大表进行并行update
- Oracle 11g 使用 dbms_parallel_execute 对大表进行并行update
- Oracle DDL
- Oracle-DDL
- oracle ddl
- ORA-14411: 该 DDL 不能与其他 DDL 并行运行
- 1916:合唱队形
- hdu-1232 畅通工程
- Qt对话框
- JavaScript复合类型
- 如何myeclipse 中配置Jdk
- oracle 11g 并行DDL
- poj 2192 Zipper
- 个人重构机房收费系统小总结
- JavaScript内置类
- poj 2007 极角排序
- linux下设置nfs共享
- Linux 内核配置机制(make menuconfig、Kconfig、makefile)讲解
- 2013年8月11日
- Goagent详细版