oracle sql 语句 并行,SQL中并行操作

--并行查询

select /*+parallel(t 4)*/ count(*) from user_tables t;

select /*+parallel(t 4)*/ count(*) from t where object_name in(select /*+parallel(t1 4)*/ object_name from t1);

--一个并行查询能够执行，需要满足下面其中一个：

a)sql语句中有hint提示，比如parallel或者parallel_index；

b)sql语句中引用的对象被设置了并行属性；

c)多表关联中至少有一个表执行全表扫描(full table scan)或者跨越分区的index range scan

d)在会话中执行：alter session force parallel query parallel 4;

--并行ddl操作：

create table t parallel 4 as select * from dba_objects;

alter table t move partition p1 parallel 4;

alter table t split partition p1 at(5000) into (partition p1,partition p2) parallel 4;

alter table t coalesce partition parallel 4;

create index t_ind on t(id) parallel 4;

alter index t_ind rebuild parallel 4;

alter index t_ind rebuild partition p1 parallel 4;

alter index t_ind split partition p1 at(5000) into (partition p11,partition p12) parallel 4;

--并行DML操作：

DML操作使用并行，必须显示地在会话里执行：

alter session enable parallel dml;

delete /*+ parallel(t 2)*/ from t;

update /*+ parallel(t 2)*/ t set name=name||'dcs';

merge /*+parallel(t 2)*/ into t using t1 on (t.id=t1.id) when matched then update set t.name=t1.name;

insert /*+parallel(t 4)*/ into t select /*+parallel(t1 4)*/ * from t1;

insert into t select /*+parallel(t1 4)*/ * from t1;

insert /*+parallel(t 4)*/ into t select * from t1;

insert /*+ append parallel(t,2)*/ into t select * from t;

--sql*loader和parallel：

--如果使用sqlloader的并行直接加载选项，并且表上有索引，将导致加载失败，可以调整参数：skip_index_maintenance=true,先把数据插入，在rebuild索引。

sqlldr userid=test/oracle control=t.ctl direct=true parallel=true skip_index_maintenance=true log=log.txt;

alter index t_ind rebuild parallel 4;