Today's Messages (on)  | Unanswered Messages (off)

Forum: SQL & PL/SQL
 Topic: Getting same sequence number in select query
Re: Getting same sequence number in select query [message #689706 is a reply to message #689689] Wed, 27 March 2024 12:02
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Issue is no_merge calls function twice:

drop sequence test_seq; 
create sequence test_seq;
create or replace
  function get_seq_val
     return varchar2
     is
         v_seq_val varchar2(100);
     begin
         v_seq_val :=  test_seq.nextval;
         dbms_output.put_line('get_seq_val:' || v_seq_val);
         return v_seq_val;
end get_seq_val;
/
set serveroutput on
column seqval format a6
SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
1      1

get_seq_val:1
get_seq_val:2
SQL> with temp as( select
  2         get_seq_val as seqval  from dual)
  3         select /*+ no_merge(temp) */ seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
3      3

get_seq_val:3
get_seq_val:4
SQL> with temp as( select /*+ materialize */
  2         get_seq_val as seqval from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
5      5

get_seq_val:5
SQL>
SY.

[Updated on: Wed, 27 March 2024 12:03]

Report message to a moderator

Re: Getting same sequence number in select query [message #689707 is a reply to message #689706] Wed, 27 March 2024 12:12
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
We would need to declare function deterministic even though it isn't which could cause unexpected results in other statements:

drop sequence test_seq; 
create sequence test_seq;
create or replace
  function get_seq_val
     return varchar2
     deterministic
     is
         v_seq_val varchar2(100);
     begin
         v_seq_val :=  test_seq.nextval;
         dbms_output.put_line('get_seq_val:' || v_seq_val);
         return v_seq_val;
end get_seq_val;
/
set serveroutput on
column seqval format a6
SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
1      1

get_seq_val:1
SQL> with temp as( select
  2         get_seq_val as seqval  from dual)
  3         select /*+ no_merge(temp) */ seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
2      2

get_seq_val:2
SQL> with temp as( select /*+ materialize */
  2         get_seq_val as seqval from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
3      3

get_seq_val:3
SQL>
But we would get unexpected results in, for example, multi-row select:

SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select deptno,seqval,seqval from temp,dept;

    DEPTNO SEQVAL SEQVAL
---------- ------ ------
        10 4      4
        20 4      4
        30 4      4
        40 4      4

get_seq_val:4
SQL>
while most likely we expexted:

SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select deptno,seqval,seqval from temp,dept;

    DEPTNO SEQVAL SEQVAL
---------- ------ ------
        10 4      4
        20 5      5
        30 6      6
        40 7      7

get_seq_val:4
get_seq_val:5
get_seq_val:6
get_seq_val:7
SQL>
SY.



Current Time: Thu Mar 28 08:26:07 CDT 2024