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
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 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定位记录来迁移数据也是一种方法