My first response to your results was: "<expletive deleted> it's running the
UNION ALL from bottom to top".
Then I noticed you had got "mm" instead of "mi" in your date format - and that
also explained how you seemed to have a 59 second gap in a 2 second experiment.
Apart from the moment of horror - yes, much more convincing.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Iggy Fernandez [iggy_fernandez@xxxxxxxxxxx]
Sent: 17 November 2015 21:27
To: Oracle-L Freelists
Subject: RE: Oracle set operator (UNION, MINUS) and read consistency
How about the following. Still not a proof, but indicative.
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 17 15:50:58 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> create or replace function autonomous return date as
2 currenttime date;
3 pragma autonomous_transaction;
4 begin
5 currenttime := sysdate;
6 dbms_lock.sleep(1);
7 insert into dummy select sysdate from dual;
8 commit;
9 return currenttime;
10 end;
11 /
Function created.
SQL>
SQL> drop table dummy;
Table dropped.
SQL>
SQL> create table dummy as
2 select sysdate as currenttime from dual where 1=2;
Table created.
SQL>
SQL> insert into dummy values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_lock.sleep(1);
PL/SQL procedure successfully completed.
SQL>
SQL> alter session set nls_date_format = 'hh:mm:ss';
Session altered.
SQL>
SQL> select d.* from dummy d;
CURRENTT
--------
03:11:58
SQL> select sysdate from dual;
SYSDATE
--------
03:11:59
SQL>
SQL> select 'Branch I' as branch, autonomous() as executiontime, d.* from dummy
d
2 union all
3 select 'Branch II' as branch, autonomous() as executiontime, d.* from
dummy d;
BRANCH EXECUTIO CURRENTT
--------- -------- --------
Branch I 03:11:59 03:11:58
Branch II 03:11:00 03:11:58
SQL>
SQL> select d.* from dummy d;
CURRENTT
--------
03:11:58
03:11:00
03:11:01
SQL> select sysdate from dual;
SYSDATE
--------
03:11:01
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
________________________________
From: jonathan@xxxxxxxxxxxxxxxxxx
To: iggy_fernandez@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Oracle set operator (UNION, MINUS) and read consistency
Date: Tue, 17 Nov 2015 19:20:50 +0000
I don't think your example proves the point - it may simply be demonstrating
that UNION ALL queries operate from the bottom up, and that function calls in
the select list operate after column projection.
(I don't believe, that, by the way, but it is an alternative explanation for
your result.)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Iggy Fernandez [iggy_fernandez@xxxxxxxxxxx]
Sent: 17 November 2015 18:50
To: Oracle-L Freelists
Subject: RE: Oracle set operator (UNION, MINUS) and read consistency
The assumption behind this question is that UNION ALL (and other set operators)
dictate that Oracle process the branches in linear fashion as dictated by the
order in which they appear in the text. That assumption is incorrect. For
example, "join factorization" may produce an unexpected query plan. See
https://blogs.oracle.com/optimizer/entry/optimizer_transformations_join_factorization.
As another example, Oracle is capable of processing the branches in parallel.
See
https://docs.oracle.com/database/121/VLDBG/GUID-1F4C90F9-3EF5-423A-B55B-2593FB3F1433.htm.
Here is a demonstration that indicates that read consistency applies to the
entire statement not just to individual branches. The demonstration uses an
autonomous transaction.
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 17 13:39:26 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> create or replace function autonomous return integer as
2 pragma autonomous_transaction;
3 begin
4 dbms_lock.sleep(1);
5 insert into dummy select sysdate from dual;
6 commit;
7 return 1;
8 end;
9 /
Function created.
SQL>
SQL> drop table dummy;
Table dropped.
SQL>
SQL> create table dummy as
2 select sysdate as currenttime from dual where 1=2;
Table created.
SQL>
SQL> insert into dummy values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter session set nls_date_format = 'hh:mm:ss';
Session altered.
SQL>
SQL> select * from dummy;
CURRENTT
--------
01:11:26
SQL>
SQL> select 1, autonomous(), d.* from dummy d
2 union all
3 select 2, null, d.* from dummy d;
1 AUTONOMOUS() CURRENTT
---------- ------------ --------
1 1 01:11:26
2 01:11:26
SQL>
SQL> select * from dummy;
CURRENTT
--------
01:11:26
01:11:27
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
________________________________
Date: Tue, 17 Nov 2015 17:46:11 +0000
From: dmarc-noreply@xxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: Oracle set operator (UNION, MINUS) and read consistency
Hi list,
I have a query using set operators like the following
select * from <table 1>
union all
select * from <table 2>
Does Oracle consider this as ONE query which allows both 'select' statements
have read consistency back to the time when the first 'select' statement starts?
Thanks,
Lei
