SQL> create table t_rowid as select * from dba_objects;

表已创建。

SQL> commit;

提交完成。

SQL> insert into t_rowid select * from t_rowid;

已创建3193984行。

SQL> commit;

提交完成。

SQL> select count(*) from t_rowid;

COUNT(*)

----------

6387968

SQL> lock table t_rowid in exclusive mode;

表已锁定。

SQL> create table t_rowid_test1 as select * from t_rowid where 1=2;

表已创建。

SQL> set autot on

SQL> set time on

21:14:21 SQL> set timing on

21:14:23 SQL> insert into t_rowid_test1 select * from t_rowid;

已创建6387968行。

已用时间: 00: 01: 28.43

执行计划

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

Plan hash value: 3629422811

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

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

| 0 | INSERT STATEMENT | | 6064K| 1023M| 19841 (3)| 00:03:59 |

| 1 | TABLE ACCESS FULL| T_ROWID | 6064K| 1023M| 19841 (3)| 00:03:59 |

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

Note

-----

- dynamic sampling used for this statement

统计信息

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

12379 recursive calls

768531 db block gets

265590 consistent gets

86817 physical reads

713761248 redo size

673 bytes sent via SQL*Net to client

580 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

258 sorts (memory)

0 sorts (disk)

6387968 rows processed

21:16:40 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00

21:19:49 SQL> create table t_rowid_test2 as select * from t_rowid where 1=2;

表已创建。

1 begin

2 for x in (select rowid,a.* from t_rowid a) loop

3 insert into t_rowid_test2 select * from t_rowid where rowid=x.rowid;

4 end loop;

5* end;

21:24:13 SQL> /

PL/SQL 过程已成功完成。

已用时间: 00: 04: 03.17

小结:能用单条sql就用单条sql,plsql性能还是要差很多

但通过rowid定位记录来迁移数据也是一种方法

ITPub博客

oracle单条sql与plsql rowid插入记录小记

最新文章