Home » SQL & PL/SQL » SQL & PL/SQL » Sequene problem with Insert All (10.2.0.3, WinXp, Linux)
Sequene problem with Insert All [message #349001] Thu, 18 September 2008 09:35 Go to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Has anyone else got uch exprience using the Insert All syntax - I'm using it in anger for the first time, and the way it interacts with sequences seems....odd.

If you run this test, you can clearly see that it is only selecting from the seqence once per row from the driving table, not once per inserted row:
create table test_0101 (col_1 number, col_2 number);

create sequence test_0101_seq;

insert all
into test_0101 (col_1,col_2)
values (test_0101_seq.nextval,x)
into test_0101 (col_1,col_2)
values (test_0101_seq.nextval,-x)
select level x from dual connect by level <=5;

select * from test_0101 order by col_1;

     COL_1      COL_2
---------- ----------
         1          1
         1         -1
         2          2
         2         -2
         3          3
         3         -3
         4         -4
         4          4
         5         -5
         5          5

10 rows selected.


It's not just an artifact caused by inserting into the same table multiple times: If you run this test and insert into two different tables, it does the same thing:
create table test_0102 (col_1 number, col_2 number);

delete test_0101;

insert all
into test_0101 (col_1,col_2)
values (test_0101_seq.nextval,x)
into test_0102 (col_1,col_2)
values (test_0101_seq.nextval,-x)
select level x from dual connect by level <=5;

SQL> select * from test_0101 order by col_1;

     COL_1      COL_2
---------- ----------
         6          1
         7          2
         8          3
         9          4
        10          5

SQL> 
SQL> select * from test_0102 order by col_1;

     COL_1      COL_2
---------- ----------
         6         -1
         7         -2
         8         -3
         9         -4
        10         -5


Has anyone else run into this as a problem, and is there a workaround other than using a trigger to populate the field with a sequence value?
Re: Sequene problem with Insert All [message #349027 is a reply to message #349001] Thu, 18 September 2008 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but it is not a problem it is how it works. Nextval is evaluated only once for each row, the subsequent ones are treated as currval.

This is the same way than:
SQL> select s.nextval s1, s.nextval s2 from dual connect by level <= 5;
        S1         S2
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

5 rows selected.

Regards
Michel
Re: Sequene problem with Insert All [message #349099 is a reply to message #349001] Thu, 18 September 2008 21:08 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
How about changing the sequence increment value to the number
of branches in the insert and augmenting each value?


insert all
into test_0101 (col_1,col_2)
values (test_0101_seq.nextval + 1,x)
into test_0101 (col_1,col_2)
values (test_0101_seq.nextval + 2,-x)


If the sequence were to be used elsewhere, such that an increment value other than 1 is not an option, how about
embedding an increment in a function for the last VALUE
branch.



CREATE OR REPLACE FUNCTION F_N(p NUMBER) RETURN NUMBER
IS 

ln_temp NUMBER(9) := 0;

BEGIN 
---increment for each branch in the insert, if two:

ln_temp :=  test_0101_seq.nextval;
ln_temp :=  test_0101_seq.nextval;

RETURN p;
END;


Then the same manual augment but with the function wrapped
around the last values.

insert all
into test_0101 (col_1,col_2)
values (test_0101_seq.nextval + 1,x)
into test_0101 (col_1,col_2)
values (f_n(test_0101_seq.nextval + 2),-x)


I wish I was at a terminal right now to do this testing,
you've helped me so much I'd like to be able to contribute
something back!

Best Regards,
Harry
Re: Sequene problem with Insert All [message #349143 is a reply to message #349099] Fri, 19 September 2008 01:52 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
@harrysmall3, I'm not shure, but what about another user getting a test_0101_seq.nextval while You are in Your function F_N() between the two test_0101_seq.nextval, in this case Your offset would be incorrect ?
Re: Sequene problem with Insert All [message #349188 is a reply to message #349143] Fri, 19 September 2008 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Michel

I see what's happening, but I'm not convinced it's correct - If the Nextval were part of the SELECT that drives the Insert All, then I'd agree with you - one row rom the query = 1 value from the sequence, but it's part of the Insert statement instead, and that generates multiple rows.

The fact that it works differently depending on whether you have the Nextval in the Values clause or in a row level trigger just doesn't seem right.

On the other hand, you've described the way Oracle think it should work pretty clearly, so I should probably just knuckle down and accept it.
Re: Sequene problem with Insert All [message #349196 is a reply to message #349188] Fri, 19 September 2008 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The fact that it works differently depending on whether you have the Nextval in the Values clause or in a row level trigger just doesn't seem right.

This because you switch between the 2 engines (SQL and PL/SQL) and they ignore each other and forget the execution environment.

In SQL Reference 10gR2, Sequence Pseudocolumns section (sorry, can't give you the link our proxy/firewall is currently blocking access to Oracle documentation), you can read:
Quote:
For a multitable insert, the reference to NEXTVAL must appear in the VALUES clause, and the sequence is updated once for each row returned by the subquery, even though NEXTVAL may be referenced in multiple branches of the multitable insert.


Regards
Michel
Re: Sequene problem with Insert All [message #349213 is a reply to message #349196] Fri, 19 September 2008 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's reasonably definitive then.

we're having problems with the Oracle documentation too - keep getting errors from the Oracle site when I try to look at the online docs.
Simplified Solution: Sequence problem with Insert All [message #349268 is a reply to message #349213] Fri, 19 September 2008 10:09 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I seemed to have brainstormed beyond what was necessary.
To bypass the limitation all that is required is to
return sequence.NEXTVAL through a function in your VALUES
clause.

I simplified the test.

#1 Recreation

create sequence test001;
CREATE TABLE TBL_TEMP1 
(ID1 NUMBER(5), ID2 NUMBER(5), ID3 NUMBER(5));

INSERT INTO TBL_TEMP1 (select
test001.nextval,
test001.nextval,
test001.nextval
FROM 
EDP_HCFA_CLAIM WHERE ROWNUM < 11);

commit;


Same Value is populated in each ID:

SELECT * FROM TBL_TEMP1;
ID1   ID2   ID3   
1     1     1     
2     2     2     
3     3     3     
4     4     4     
5     5     5     
6     6     6     
7     7     7     
8     8     8     
9     9     9     
10    10    10  


Now return NEXTVAL from a function.

INSERT INTO TBL_TEMP1 (select
 
fgetseq,
fgetseq,
fgetseq 
FROM 
EDP_HCFA_CLAIM WHERE ROWNUM < 11);

ID1   ID2   ID3   
1     1     1     
2     2     2     
3     3     3     
4     4     4     
5     5     5     
6     6     6     
7     7     7     
8     8     8     
9     9     9     
10    10    10    
11    12    13    
14    15    16    
17    18    19    
20    21    22    
23    24    25    
26    27    28    
29    30    31    
32    33    34    
35    36    37    
38    39    40    



Code for the function:

CREATE OR REPLACE FUNCTION FGETSEQ 
RETURN NUMBER 
IS

lv_seq number(9);
BEGIN
select test001.nextval into lv_seq from dual; 

return lv_seq;
END FGETSEQ;


It can be done as well without the function if
you materialize it up front; short on time
to run the test, will try it at lunch.

@_JUM
Quote:
@harrysmall3, I'm not shure, but what about another user getting a test_0101_seq.nextval while You are in Your function F_N() between the two test_0101_seq.nextval, in this case Your offset would be incorrect ?



What is your definition of "correct" as this was not part
of the problem specification;however, the scenario applies
to any concurrent processes that utilize the same sequence.
Usually sequences are for creating unique surrogate keys.
A gap in the sequence would not interfere with this result.
If no gaps in the sequence are part of a requirement
for two concurrent processes using the sequence then I would say
to use two sequences.

Regards,
Harry






Re: Simplified Solution: Sequence problem with Insert All [message #349272 is a reply to message #349268] Fri, 19 September 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It can be done as well without the function if you materialize it up front;

What does this mean?

Regards
Michel
Re: Simplified Solution: Sequence problem with Insert All [message #349281 is a reply to message #349272] Fri, 19 September 2008 11:34 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I was thinking of the WITH clause structure of
INSERT INTO {...}
WITH S1 {Select sequences to use for field1},
S2 {....for field2),
etc
SELECT /*+ MATERIALIZE */ {from the defined aliases}

Havent tried it yet, needed to find a good example
for the syntax of using WITH and INSERT.

I was lucky to find some great examples at
http://www.psoug.org/reference/with.html

From some author:

Quote:
Built from code posted by Michel Cadot at comp.databases.oracle.misc 28-Sep-2006


Smile

Re: Simplified Solution: Sequence problem with Insert All [message #349283 is a reply to message #349281] Fri, 19 September 2008 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot use the sequence in the select part (or with one) of insert.

Regards
Michel
Re: Sequene problem with Insert All [message #349306 is a reply to message #349213] Fri, 19 September 2008 15:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
JRowbottom wrote on Fri, 19 September 2008 04:07

we're having problems with the Oracle documentation too - keep getting errors from the Oracle site when I try to look at the online docs.



Per the OTN forum administrator:

"As of Thursday 9/18: There is an outage with the OTN downloads and documentation. Folks are working on it and we should be back up soon. Thanks for your patience."

Re: Simplified Solution: Sequence problem with Insert All [message #349631 is a reply to message #349268] Mon, 22 September 2008 07:39 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nice piece of lateral thinking there @Harry
I've fin ished that piece of code now, but if I revisit it during the project, I'll have a look at doing it your way.
Previous Topic: Another way to write hierarchical query...???
Next Topic: count of rows within 5 minute intervals
Goto Forum:
  


Current Time: Fri Dec 09 19:48:22 CST 2016

Total time taken to generate the page: 0.06042 seconds