Oracle append Hint(四)

来源：互联网发布：烘被机好用吗知乎编辑：程序博客网时间：2024/05/13 10:53

既然直接加载会产生少量的redo，那么再加入并行会并行的往表中插入数据，肯定速度会更快，即:

insert/*+append parallel(t,4)*/ into t

select * from t1;

到底结果是不是这样呢？请看例子：

sys@ORCL> alter session enable parallel dml;

Session altered.

sys@ORCL> alter session set sql_trace=true;

Session altered.

执行两条语句：

sys@ORCL> insert/*+append parallel(t,4)*/ into t

2 select * from t1;

9999 rows created.

sys@ORCL> rollback;

Rollback complete.

sys@ORCL> insert/*+parallel(t,4)*/ into t

2 select * from t1;

9999 rows created.

sys@ORCL> rollback;

Rollback complete.

下面是tkprof之后TRACE文件的输出：

********************************************************************************

insert/*+append parallel(t,4)*/ into t

select * from t1

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 1 0 0

Execute 1 0.03 0.43 17 131 88 9999

Fetch 0 0.00 0.00 0 0 0 0

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

total 2 0.03 0.44 17 132 88 9999

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows RowSource Operation

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

4 PX COORDINATOR (cr=131 pr=17 pw=0 time=350426 us)

0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0time=0 us)

0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)

0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)

0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)

0 PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0pw=0 time=0 us)

9999 TABLE ACCESS FULL T1 (cr=129 pr=17 pw=0time=70286 us)

******************************************************************************

insert/*+parallel(t,4)*/ into t

select * from t1

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 1 0 0

Execute 1 0.02 0.31 0 131 87 9999

Fetch 0 0.00 0.00 0 0 0 0

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

total 2 0.02 0.32 0 132 87 9999

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows RowSource Operation

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

4 PX COORDINATOR (cr=131 pr=0 pw=0 time=370710 us)

0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0time=0 us)

0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)

0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)

0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)

0 PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0pw=0 time=0 us)

9999 TABLE ACCESS FULL T1 (cr=129 pr=0 pw=0time=80099 us)

********************************************************************************

可以看到两条语句的执行方式是一样，因此可以判断执行速度几乎是一样的。

这是因为INSERT使用并行时，Oracle自动使用直接加载的方式进行数据的加载，所以在这种情况下append提示是可以忽略的。