Re: Oracle set operator (UNION, MINUS) and read consistency
Date: Tue, 17 Nov 2015 15:46:48 -0800
Message-ID: <564BBC68.6010502_at_oracle.com>
This is one of the most common masking errors made and hardest to spot ! Well spotted Jonathan
On 11/17/2015 2:16 PM, Iggy Fernandez wrote:
> Oops :-)
>
>
> ------------------------------------------------------------------------
> From: jonathan_at_jlcomp.demon.co.uk
> To: oracle-l_at_freelists.org
> Subject: RE: Oracle set operator (UNION, MINUS) and read consistency
> Date: Tue, 17 Nov 2015 21:32:53 +0000
>
>
>
> 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
> _at_jloracle
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]
> on behalf of Iggy Fernandez [iggy_fernandez_at_hotmail.com]
> *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_at_jlcomp.demon.co.uk
> To: iggy_fernandez_at_hotmail.com; oracle-l_at_freelists.org
> 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
> _at_jloracle
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]
> on behalf of Iggy Fernandez [iggy_fernandez_at_hotmail.com]
> *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_at_freelists.org
> To: oracle-l_at_freelists.org
> 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
-- -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 18 2015 - 00:46:48 CET