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定位記錄來遷移資料也是一種方法

oracle單條sql與plsql rowid插入記錄小記

相關文章