| Non-deterministic query [message #633267] |
Fri, 13 February 2015 04:25  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've constructed a case (never mind why) where identical SQLs produce different results, depending on how a table was implemented:drop table t1;
drop sequence s1;
create table t1 (c1 number,c2 number) partition by range (c1) interval (5) (partition p1 values less than (0));
create sequence s1;
insert into t1(c1) select rownum from dual connect by level < 10;
update t1 set c2=s1.nextval;
select * from t1 order by c1;
drop table t1;
drop sequence s1;
create table t1 (c1 number,c2 number) partition by hash(c1) partitions 2;
create sequence s1;
insert into t1(c1) select rownum from dual connect by level < 10;
update t1 set c2=s1.nextval;
select * from t1 order by c1;
This is not a real-world case. I just find it worrying when Uncle Oracle appears not to play by the rules of the relational paradigm.
|
|
|
|
| Re: Non-deterministic query [message #633270 is a reply to message #633267] |
Fri, 13 February 2015 05:34  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I did a quick test to see if there would be any difference if we remove the dependency on the sequence and the update by creating an identity column. Now the statements reduce to INSERT/SELECT against the table, sequence is taken care by Oracle -
drop table t1 purge;
drop sequence s1;
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER GENERATED ALWAYS AS IDENTITY) PARTITION BY RANGE (c1) INTERVAL (5) (PARTITION p1 VALUES LESS THAN (0));
--create sequence s1;
INSERT INTO t1(c1) SELECT ROWNUM FROM dual CONNECT BY LEVEL < 10;
--update t1 set c2=s1.nextval;
select * from t1 order by c1;
DROP TABLE t1 PURGE;
--drop sequence s1;
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER generated always as identity) PARTITION BY HASH(c1) PARTITIONS 2;
--create sequence s1;
INSERT INTO t1(c1) SELECT ROWNUM FROM dual CONNECT BY LEVEL < 10;
--update t1 set c2=s1.nextval;
select * from t1 order by c1;
No difference in this case.
|
|
|
|
| Re: Non-deterministic query [message #633273 is a reply to message #633270] |
Fri, 13 February 2015 06:08 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your test gives a deterministic result, because you are using the identity sequence to populate C2 at row insertion time:orclz>
orclz> select COLUMN_NAME,IDENTITY_COLUMN,DATA_DEFAULT from user_tab_cols where table_name='T1';
COLUMN_NAME IDE
------------------------------ ---
DATA_DEFAULT
--------------------------------------------------------------------------------
C1 NO
C2 YES
"SCOTT"."ISEQ$$_93995".nextval
orclz> |
|
|
|
|
|
| Re: Non-deterministic query [message #633276 is a reply to message #633275] |
Fri, 13 February 2015 06:27 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sorry, I should have done so. First from the range partitioned table:
orclz> select * from t1 order by c1;
C1 C2
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
and now the hash partitioned table:orclz> select * from t1 order by c1;
C1 C2
---------- ----------
1 5
2 1
3 6
4 7
5 2
6 3
7 8
8 4
9 9
9 rows selected.
orclz>
|
|
|
|
| Re: Non-deterministic query [message #633282 is a reply to message #633276] |
Fri, 13 February 2015 08:56 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I see - I'm pretty certain that's 100% legal and not against any rules what so ever.
Not sure if there's a specific warning in the docs saying that'll happen but various other warnings make it unavoidable.
This goes hand in hand with the fact that a select (absent an order by) can return data in any order.
Really an update is a select with a data modification instruction tacked on.
In discussions on AskTom about order by I remember seeing examples (can't find it at the moment) where he did things to the contents of a table to force it return data in a different order to the order of insertion.
You could get one of those examples, add 2 columns, update one after first inserting data, update the second after whatever is done to force the order to change. The values will be different across the 2 columns.
In fact by oracles own rules it is theoretically possible for that update to give different results when run against 2 tables that were created the same way (this is highly unlikely of course, but completely legal)
You're effectively randomly assigning values from a discreet set to a set of rows.
|
|
|
|
|
|
| Re: Non-deterministic query [message #633287 is a reply to message #633283] |
Fri, 13 February 2015 21:23 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
John Watson wrote on Sat, 14 February 2015 02:15The UPDATE is non-deterministic because, as we all know, a SELECT without an ORDER BY is non-deterministic.
I agree ... kind of. A SELECT without ORDER BY is sorted non-deterministically, but the tuples themselves are deterministic. The problem is the non-deterministic sequence.NEXTVAL combined with the non-deterministic sort. Any deterministic function would have been fine.
I don't see any rules broken here.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
