RE: Oracle set operator (UNION, MINUS) and read consistency
Date: Tue, 17 Nov 2015 10:50:42 -0800
Message-ID: <BLU179-W716E6B0867C82EA0F6541EEB1D0_at_phx.gbl>
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 ProductionWith 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:2601:11:27
SQL>SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith 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 allselect * 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 Tue Nov 17 2015 - 19:50:42 CET