Hi
With your union all queries it works as you say but if I remove the union
all every executions of "select x.z.o1,x.z.o2 from (select get_ot z from
dual where rownum>0)x;" does 3 function calls. Are you suggesting using
UNION ALL as workaroudn...?
WITH UNION ALL:
SQL> truncate table t1;
Table truncated.
SQL> select 0,0 from dual union all
2 select x.z.o1,x.z.o2 from (select get_ot z from dual)x;
0 0
---------- ----------
0 0
1 2
SQL> select * from t1;
C1
---------------------------------------------------------------------------
18-JUL-16 03.58.36.949040 PM
18-JUL-16 03.58.36.951646 PM
SQL> select 0,0 from dual union all
2 select x.z.o1,x.z.o2 from (select get_ot z from dual where rownum>0)x;
0 0
---------- ----------
0 0
1 2
SQL> select * from t1;
C1
---------------------------------------------------------------------------
18-JUL-16 03.58.36.949040 PM
18-JUL-16 03.58.36.951646 PM
18-JUL-16 03.58.49.694260 PM
without UNION ALL
SQL> truncate table t1;
Table truncated.
SQL> select x.z.o1,x.z.o2 from (select get_ot z from dual where rownum>0)x;
Z.O1 Z.O2
---------- ----------
1 2
SQL> select * from t1;
C1
---------------------------------------------------------------------------
18-JUL-16 04.01.05.399085 PM
18-JUL-16 04.01.05.403076 PM
18-JUL-16 04.01.05.403575 PM
SQL> select x.z.o1,x.z.o2 from (select get_ot z from dual where rownum>0)x;
Z.O1 Z.O2
---------- ----------
1 2
SQL> select * from t1;
C1
---------------------------------------------------------------------------
18-JUL-16 04.01.05.399085 PM
18-JUL-16 04.01.05.403076 PM
18-JUL-16 04.01.05.403575 PM
18-JUL-16 04.01.13.476238 PM
18-JUL-16 04.01.13.476443 PM
18-JUL-16 04.01.13.476544 PM
6 rows selected.
On Mon, Jul 18, 2016 at 3:50 PM, Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
As I said before SQL*Plus adds extra calls. I don't know how exactly, but
it's always just for first 1 fetch/row.
Compare:
select 0,0 from dual union all
select x.z.o1,x.z.o2 from (select get_ot z from dual)x;
and
select 0,0 from dual union all
select x.z.o1,x.z.o2 from (select get_ot z from dual where rownum>0)x;
--
Best regards,
Sayan Malakshinov
http://orasql.org
