ORACLE 並行(PARALLEL)實現方式及優先順序

1 Alter session force parallel query;

2 Alter table tab1 parallel n;

3 Hist方式

Alter table tab1 parallel n;

Select /*+parallel(tab n)*/ from tab;

Alter session force parallel query parallel n;

如果沒有設定將執行預設並行度

SQL> create table tab_3 as select * from dba_objects;

Table created.

SQL> select table_name,degree from user_tables;

TABLE_NAME                     DEGREE

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

TAB_3                                   1

If you are sure you want to execute in parallel and want to avoid setting the DOP for a table or modifying the queries involved, you can force parallelism with the following statement:

ALTER SESSION FORCE PARALLEL QUERY;

All subsequent queries are executed in parallel provided no restrictions are violated. You can also force DML and DDL statements. This clause overrides any parallel clause specified in subsequent statements in the session, but is overridden by a parallel hint.

In typical OLTP environments, for example, the tables are not set parallel, but nightly batch scripts may want to collect data from these tables in parallel. By setting the DOP in the session, the user avoids altering each table in parallel and then altering it back to serial when finished.

Alter session force parallel DML;

Alter table tab1 parallel n;

Hist 方式

Alter table tab1 parallel n;

Alter session force parallel DML parallel n;

UPDATE /*+ PARALLEL(tab1,4) */ tbl_2 SET c1=c1+1;

INSERT /*+ PARALLEL(tbl_ins,2) */ INTO tbl_ins

SELECT /*+ PARALLEL(tbl_sel,4) */ * FROM tbl_sel;

DELETE /*+ PARALLEL (t1, 2) */ FROM t1

如果沒有設定將執行預設並行度

非分割槽表

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

ALTER SESSION FORCE PARALLEL DDL

PARALLEL clause

ALTER SESSION FORCE PARALLEL DDL parallel 10;

CREATE INDEX ….parallel 10;

ALTER INDEX ... REBUILD parallel 10;

ALTER INDEX ... MOVE PARTITION parallel 10;

ALTER INDEX ...SPLIT PARTITION parallel 10;