分类： Oracle
2008-04-24 20:51:50
序列已创建。
SQL> insert all
2 INTO T1
3 (C1)
4 VALUES
5 (C1)
6 INTO T2
7 (C1)
8 VALUES
9 (C1)
10 select seq.nextval AS C1 from EMP;
select seq.nextval AS C1 from EMP
*
ERROR 位于第 10 行:
ORA-02287: 此处不允许序号
我们可以通过使用一个函数来绕过该限制。
为了绕过这个限制，我们将该sequence放到一个函数中，然后我们调用该函数生成序列号：
SQL> CREATE OR REPLACE FUNCTION f_get_sequence
2 RETURN CHAR
3 AS
4 /***********************************
5 设计说明:
6 1.
7 ************************************/
8 vv_seq VARCHAR2(24);
9 BEGIN
10 select seq.nextval into vv_seq from dual;
11 vv_seq := lpad(vv_seq, 24, '0') ;
12 RETURN vv_seq;
13 EXCEPTION
14 WHEN OTHERS THEN
15 raise;
16 END;
17 /
函数已创建。
SQL> insert all
2 INTO T1
3 (C1)
4 VALUES
5 (C1)
6 INTO T2
7 (C1)
8 VALUES
9 (C1)
10 select f_get_sequence AS C1 from EMP;
已创建28行。
SQL> select * from t1;
C1
------------------------
000000000000000000000002
000000000000000000000004
000000000000000000000006
000000000000000000000008
000000000000000000000010
000000000000000000000012
000000000000000000000014
000000000000000000000016
000000000000000000000018
000000000000000000000020
000000000000000000000022
000000000000000000000024
000000000000000000000026
000000000000000000000028
已选择14行。
SQL> select * from t2;
C1
------------------------
000000000000000000000003
000000000000000000000005
000000000000000000000007
000000000000000000000009
000000000000000000000011
000000000000000000000013
000000000000000000000015
000000000000000000000017
000000000000000000000019
000000000000000000000021
000000000000000000000023
000000000000000000000025
000000000000000000000027
000000000000000000000029
已选择14行。
从结果中可以看到，这样虽然能插入数据，但是两个表中的数据不一样，这也不是我们想要的结果。
有没有什么方法能让插入到两个表中的数据完全一样呢？
在《Effective Oracle by design》这本书中，Tom讲了几种用rownum伪列进行性能调整的方法，
其中提到：如果子查询中有rownum，则该子查询将先被实体化，然后用于语句的其它部分。
我们的这个问题能通过rownum的使用来解决吗？做个测试就明白了：
SQL> rollback;
回退已完成。
SQL> select * from t1;
未选定行
SQL> select * from t2;
未选定行
SQL> insert all
2 INTO T1
3 (C1)
4 VALUES
5 (C1)
6 INTO T2
7 (C1)
8 VALUES
9 (C1)
10 select f_get_sequence AS C1,rownum AS rn from EMP;
已创建28行。
SQL> select * from t1;
C1
------------------------
000000000000000000000030
000000000000000000000031
000000000000000000000032
000000000000000000000033
000000000000000000000034
000000000000000000000035
000000000000000000000036
000000000000000000000037
000000000000000000000038
000000000000000000000039
000000000000000000000040
000000000000000000000041
000000000000000000000042
000000000000000000000043
已选择14行。
SQL> select * from t2;
C1
------------------------
000000000000000000000030
000000000000000000000031
000000000000000000000032
000000000000000000000033
000000000000000000000034
000000000000000000000035
000000000000000000000036
000000000000000000000037
000000000000000000000038
000000000000000000000039
000000000000000000000040
000000000000000000000041
000000000000000000000042
000000000000000000000043
已选择14行。
看到这些，我们应该高兴了，通过这种方法实现了向两个表中插入相同的数据。
对于这两个insert语句，唯一的差别就是第二个语句中多了一个rownum伪列，
为什么会有这么大的不同呢？通过做一个10046跟踪，我们可以看看后台发生了什么：
不带rownum伪列的：
********************************************************************************
insert all
INTO T1
(C1)
VALUES
(C1)
INTO T2
(C1)
VALUES
(C1)
select f_get_sequence AS C1 from EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.07 0.06 0 5 3 28
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.07 0.07 0 5 3 28
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 MULTI-TABLE INSERT (cr=90 r=0 w=0 time=167006 us)
14 TABLE ACCESS FULL EMP (cr=3 r=0 w=0 time=52 us)
********************************************************************************
SELECT seq.nextval
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 28 0.01 0.03 0 0 0 0
Fetch 28 0.00 0.01 0 84 1 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 57 0.03 0.05 0 84 1 28
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
28 SEQUENCE (cr=85 r=0 w=0 time=56735 us)
28 TABLE ACCESS FULL DUAL (cr=84 r=0 w=0 time=681 us)
********************************************************************************
带有rownum伪列的：
********************************************************************************
insert all
INTO T1
(C1)
VALUES
(C1)
INTO T2
(C1)
VALUES
(C1)
select f_get_sequence AS C1,rownum AS rn from EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.04 0 5 3 28
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.04 0 5 3 28
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 MULTI-TABLE INSERT (cr=46 r=0 w=0 time=116157 us)
14 VIEW (cr=46 r=0 w=0 time=116041 us)
14 COUNT (cr=3 r=0 w=0 time=78 us)
14 TABLE ACCESS FULL EMP (cr=3 r=0 w=0 time=57 us)
********************************************************************************
SELECT seq.nextval
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 14 0.03 0.02 0 0 0 0
Fetch 14 0.03 0.01 0 42 1 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 0.06 0.03 0 42 1 14
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 27 (recursive depth: 1)
********************************************************************************
从跟踪的统计数据来看，带有rownum列的insert语句执行了0.04 秒，不带rownum列的insert语句执行了 0.07 秒，
差别将近一倍；
从执行计划来看，带有rownum列的insert语句将select子句实体化了，而不带rownum列的insert语句则没有；
区别更大的地方还在下面，在带有rownum列的insert语句的执行过程中SELECT seq.nextval from dual这条
递归SQL执行了14次；而不带rownum列的insert语句中，该递归语句执行了28次！整整多出一倍！
这就解释了这两条insert语句的执行时间为什么会有将近一倍的差别。
