Thanks for the question, Dusan.

Asked: May 07, 2004 - 3:38 am UTC

Last updated: November 22, 2005 - 4:19 pm UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Recently we solved following problem. We wanted to insert rows into table from specific select statement with order by clause. To every new row we wanted to assign sequence number, in order according order by clause. Please, see following with my comments.

valasekd@DV10G> drop table t_source
2 /

Table dropped.

valasekd@DV10G> drop sequence t_seq
2 /

Sequence dropped.

valasekd@DV10G> drop table t_ordered
2 /

Table dropped.

valasekd@DV10G> create table t_source as select OBJECT_ID from all_objects
2 where rownum <10
3 /

Table created.

valasekd@DV10G> alter table t_source add seq number
2 /

Table altered.

valasekd@DV10G> create sequence t_seq
2 /

Sequence created.

valasekd@DV10G> create table t_ordered as select * from t_source where 1=0
2 /

Table created.

valasekd@DV10G> insert into t_ordered (object_id, seq)
2 select object_id, t_seq.nextval
3 from t_source
4 order by object_id
5 /
select object_id, t_seq.nextval
*
ERROR at line 2:
ORA-02287: sequence number not allowed here
-- Here we discovered that it is not possible this way.
-- So, we created function returning sequence number

valasekd@DV10G> create or replace function f_seq return number as
2 l_return number;
3 begin
4 select t_seq.nextval into l_return from dual;
5 return l_return;
6 end;
7 /

Function created.

valasekd@DV10G> insert into t_ordered (object_id, seq)
2 select object_id, f_seq()
3 from t_source
4 order by object_id
5 /

9 rows created.

valasekd@DV10G> select object_id, seq
2 from t_ordered
3 order by object_id
4 /

OBJECT_ID SEQ
--------- ---------
3 6
15 4
20 1
25 7
28 3
29 5
39 8
44 2
51 9

9 rows selected.
-- Sequence was not assigned as we wanted. It means, that f_seq()
-- was called before sorting of rows
-- So, we tried another solution - using analytical functions

valasekd@DV10G> truncate table t_ordered
2 /

Table truncated.

valasekd@DV10G> insert into t_ordered (object_id, seq)
2 select object_id
3 ,case when row_number() over(partition by object_id
4 order by object_id
5 ) <> 0 then t_seq.nextval
6 end
7 from t_source
8 order by object_id
9 /
) <> 0 then t_seq.nextval
*
ERROR at line 5:
ORA-02287: sequence number not allowed here
-- Nevertheless, still the same ORA-02287 as before
-- So, lets try function

valasekd@DV10G> insert into t_ordered (object_id, seq)
2 select object_id
3 ,case when row_number() over(partition by object_id
4 order by object_id
5 ) <> 0 then f_seq
6 end
7 from t_source
8 order by object_id
9 /

9 rows created.

valasekd@DV10G> select object_id, seq
2 from t_ordered
3 order by object_id
4 /

OBJECT_ID SEQ
--------- ---------
3 10
15 11
20 12
25 13
28 14
29 15
39 16
44 17
51 18

9 rows selected.
-- Here we go!
-- Seems that we even do not need order by clause, see following

valasekd@DV10G> truncate table t_ordered;

Table truncated.

valasekd@DV10G> insert into t_ordered (object_id, seq)
2 select object_id
3 ,case when row_number() over(partition by object_id
4 order by object_id
5 ) <> 0 then f_seq
6 end
7 from t_source
8 /

9 rows created.

valasekd@DV10G> select object_id, seq
2 from t_ordered
3 order by object_id;

OBJECT_ID SEQ
--------- ---------
3 19
15 20
20 21
25 22
28 23
29 24
39 25
44 26
51 27

9 rows selected.

Nevertheless, before putting this into production, we are not shure if this is correct solution. We do not want to fall in trap as someone who used something like "order by rowid".

Thanks,

Dushan

and Tom said...

the ordering is not going to be deterministic.

A simple change in a query plan can and will change the outcome.

Only procedural solutions will be "100% safe" in this case. You are relying on a side effect that does not have to happen -- the behaviour of that query could change at any time.

In theory, this should work as well:

ops$tkyte@ORA9IR2> insert into t
2 select /*+ NO_MERGE(a) */ s.nextval, a.*
3 from (select /*+ NO_MERGE */ *
4 from t2
5 order by user_id) a
6 /

44 rows created.

but if my life depended on the sequence being assigned in sorted order, i would bulk collect 100, forall i insert 100 and so on.

Rating

(19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How can I use multiinsert statement (INSERT ALL)

Dushan, May 07, 2004 - 12:11 pm UTC

Thanks for response! The case as I described, was planned for multiinsert statement, generating master and detail sequences ,populating master and detail tables in one run. Constraint between master-detail was deferrable.
We wanted to get seq. numbers and order rows from select statement like this:

master_seq detail_seq seq
111 211 1 <= insert into master and detail
111 212 2 <= insert into detail
111 213 3 <= insert into detail
112 214 1 <= insert into master and detail
112 215 2 <= insert into detail

If we cannot guarantee generating of sequences on ordered manner, I am afraid that we cannot use multiinsert statement in our case.

May 07, 2004 - 1:17 pm UTC

(the case was not described as a multi-table insert)

that 1,2,3 number is less then useful, the detail_seq already does all of the ordering you'll ever need.

not sure I under the inputs or outputs here tho at all.

seq -1,2,3

Dushan, May 07, 2004 - 1:31 pm UTC

The 1,2,3 was decision point in multiinsert statement:
If seq=1, insert into master
If seq=1,2,3 ... (All rows) insert into detail

May 07, 2004 - 2:39 pm UTC

where do master/detail seq come from -- i'm missing something than i guess cause given that input, i can multi-table insert... not quite seeing where the "order by" is coming into play.

Thirumal, May 09, 2004 - 12:44 am UTC

Hello tom,

Can your please tell me how to rewrite the query given
below
"
select n, dt
from t t1
where 3 >=
(select count(*)
from t t2
where t2.n=t1.n and t2.dt<= t1.dt)
order by 1,2
"
with the help of using Inline views. I am novice progg.
help me.

May 10, 2004 - 7:33 am UTC

really wouldn't be a condidate for an inline view.

analytics, sure.  you are doing a TOP-N.  analytics rock and roll for that.

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select mod(rownum,10) n, created dt
  4    from all_objects
  5   where rownum <= 5000
  6  /
 
Table created.
 
Elapsed: 00:00:00.25
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select n, dt
  2  from t t1
  3  where 3 >=
  4    (select count(*)
  5      from t t2
  6            where t2.n=t1.n and t2.dt<= t1.dt)
  7            order by 1,2
  8  /
 
         N DT
---------- --------------------
         0 12-may-2002 17:13:01
         0 12-may-2002 17:13:01
         1 12-may-2002 17:13:01
         2 12-may-2002 17:13:02
         2 12-may-2002 17:13:02
         2 12-may-2002 17:13:02
         3 12-may-2002 17:13:02
         3 12-may-2002 17:13:02
         3 12-may-2002 17:13:02
         4 12-may-2002 17:13:02
         5 12-may-2002 17:13:01
         7 12-may-2002 17:13:02
         7 12-may-2002 17:13:02
         7 12-may-2002 17:13:03
         9 12-may-2002 17:13:01
         9 12-may-2002 17:13:02
 
16 rows selected.
 
Elapsed: 00:00:05.51
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from (
  3  select n, dt,
  4         count(*) over (partition by n order by dt) cnt
  5    from t
  6         )
  7   where cnt <= 3
  8  /
 
         N DT                          CNT
---------- -------------------- ----------
         0 12-may-2002 17:13:01          2
         0 12-may-2002 17:13:01          2
         1 12-may-2002 17:13:01          1
         2 12-may-2002 17:13:02          3
         2 12-may-2002 17:13:02          3
         2 12-may-2002 17:13:02          3
         3 12-may-2002 17:13:02          3
         3 12-may-2002 17:13:02          3
         3 12-may-2002 17:13:02          3
         4 12-may-2002 17:13:02          1
         5 12-may-2002 17:13:01          1
         7 12-may-2002 17:13:02          2
         7 12-may-2002 17:13:02          2
         7 12-may-2002 17:13:03          3
         9 12-may-2002 17:13:01          1
         9 12-may-2002 17:13:02          2
 
16 rows selected.
 
Elapsed: 00:00:00.14
ops$tkyte@ORA9IR2>

Multi-table insert

Dushan, May 09, 2004 - 1:31 am UTC

Ok, I see that it is not clear, sorry! I'll give you full example of my multi-table insert:
valasekd@DV10G> drop table t_source;

Table dropped.

valasekd@DV10G> drop table t_master;

Table dropped.

valasekd@DV10G> drop table t_detail;

Table dropped.

valasekd@DV10G> drop sequence t_master_seq;

Sequence dropped.

valasekd@DV10G> drop sequence t_detail_seq;

Sequence dropped.

valasekd@DV10G>
valasekd@DV10G> --This is the source table. From this table we want to populate
valasekd@DV10G> --t_master, t_detail
valasekd@DV10G>
valasekd@DV10G> create table t_source as
2 select OBJECT_ID
3 ,owner
4 from all_objects;

Table created.

valasekd@DV10G> create table t_master ( id number not null primary key
2 ,owner varchar2(30));

Table created.

valasekd@DV10G> create sequence t_master_seq;

Sequence created.

valasekd@DV10G> create sequence t_detail_seq;

Sequence created.

valasekd@DV10G>
valasekd@DV10G>
valasekd@DV10G> create or replace function f_seq(p_seq_type in varchar2) return number as
2 l_return number;
3 begin
4 if p_seq_type = 'NEXT' then
5 select t_master_seq.nextval
6 into l_return from dual;
7 else
8 select t_master_seq.currval
9 into l_return from dual;
10 end if;
11 return l_return;
12 end;
13 /

Function created.

valasekd@DV10G> create table t_detail( id number not null primary key
2 ,owner_id number not null);

Table created.

valasekd@DV10G>
valasekd@DV10G> INSERT ALL
2 WHEN SEQ = 1 THEN
3 INTO t_master( id,owner)
4 VALUES (owner_id,owner)
5 WHEN SEQ <> 0 THEN
6 INTO t_detail(id ,owner_id)
7 VALUES (t_detail_seq.nextval,owner_id)
8 select owner
9 ,row_number() over(partition by owner order by owner
10 ) seq
11 ,case when row_number() over(partition by owner order by owner
12 ) = 1 then f_seq('NEXT')
13 else
14 f_seq('CURR')
15 end owner_id
16 from t_source
17 /

50018 rows created.

valasekd@DV10G>
valasekd@DV10G>
valasekd@DV10G> select * from t_master;

ID OWNER
--------- ------------------------------
1 BI
2 CCS
3 CTXSYS
4 DBSNMP
5 DMSYS
6 EXFSYS
7 HR
8 IX
9 MDSYS
10 MV
11 OE
12 OLAPSYS
13 ORDPLUGINS
14 ORDSYS
15 OUTLN
16 PM
17 PUBLIC
18 REPADM
19 SCOTT
20 SH
21 SI_INFORMTN_SCHEMA
22 SYS
23 SYSMAN
24 SYSTEM
25 VALASEKD
26 WKSYS
27 WK_TEST
28 WMSYS
29 XDB

29 rows selected.

valasekd@DV10G>
valasekd@DV10G> select count(*) from t_master;

COUNT(*)
---------
29

valasekd@DV10G> select count(*) from t_detail;

COUNT(*)
---------
49989

valasekd@DV10G>
valasekd@DV10G> select count(*)
2 from (select a.owner
3 ,b.id
4 from t_master a
5 ,t_detail b
6 where a.id = b.owner_id);

COUNT(*)
---------
49989

valasekd@DV10G>
valasekd@DV10G> select count(*) from t_source;

COUNT(*)
---------
49989

May 10, 2004 - 7:42 am UTC

that is something I would not feel safe doing in a multi-table insert. too many side effects of side effects being "counted" on.

what about this solution...

Nemec, May 11, 2004 - 3:30 am UTC

Dusan,

what about this solution....

INSERT ALL
WHEN master_yn = 'Y' THEN
INTO t_master( id,owner)
VALUES (master_id,owner)
WHEN master_yn IN ('Y','N') THEN -- master is detail as well
INTO t_detail(id ,owner_id)
VALUES (object_id,master_id)
select
case when row_number() over (partition by owner order by object_id) = 1 THEN 'Y' else 'N' end as master_yn ,
min(object_id) over (partition by owner order by object_id) master_id,
object_id, owner
from t_source;

if you dont like gaps in IDs you may use dense_rank instead of row_number:

select
case when row_number() over (partition by owner order by object_id) = 1 THEN 'Y' else 'N' end as master_yn ,
dense_rank() over (order by owner) master_id,
row_number() over (order by owner,object_id) object_id,
owner
from t_source;

If you load your tables incrementally you may wont to shift the ID by adding of MAX(last_assigned_id).

Is it now safe Tom?.

Jaromir D.B. Nemec

May 11, 2004 - 8:22 am UTC

depends on their needs. if they are incrementally loading this over and over -- dense_rank won't work (need to add the max(existing key) to it)

if object_id repeats from load to load but needs to be "differentiated", then this would result in dups with object_id

depends on their needs.

RE:what about this solution

Dushan, May 11, 2004 - 1:24 pm UTC

Unfortunately, I cannot do as Jaromir suggested, due reasons as Tom raised ...
I must use sequences for both master and detail tables. My "source table" is really big, huge SELECT statement. I do no want to run it twice .... I wish avoid procedural solution (if possible), this is performance-critical part.
I would appreciate some good example or idea.

Thanks,

Dusan

May 11, 2004 - 3:07 pm UTC

if you want it 100% safe, you'll have to 2 pass it.

once to populate master, another to populate detail.

or, array fetch -- populate the right stuff into the arrays, array insert.

Your query falls into the realm of "no reason it shouldn't probably work", meaning, I cannot say with 100% confidence it will always work.

That and it calls plsql from sql, that would probably chew up more cpu than you think.

Multi-table insert and forall insert comparison

Dusan, May 12, 2004 - 4:41 am UTC

I have created procedural forall (array) solution and compared it with multi-table insert. Procedural solution is about 20% slower (i run it many times in various environments).
I understand that it is safe, nevertheless, I think that multi-table insert should be a tool for things like this.

sczp@CZP1TN.CPOJ.CZ> drop table t_source;

Table dropped.

Elapsed: 00:00:00.02
sczp@CZP1TN.CPOJ.CZ> drop table t_detail;

Table dropped.

Elapsed: 00:00:00.08
sczp@CZP1TN.CPOJ.CZ> drop table t_master;

Table dropped.

Elapsed: 00:00:00.03
sczp@CZP1TN.CPOJ.CZ> drop sequence t_master_seq;

Sequence dropped.

Elapsed: 00:00:01.00
sczp@CZP1TN.CPOJ.CZ> drop sequence t_detail_seq;

Sequence dropped.

Elapsed: 00:00:01.00
sczp@CZP1TN.CPOJ.CZ> --This is the source table. From this table we want to populate
sczp@CZP1TN.CPOJ.CZ> --t_master, t_detail
sczp@CZP1TN.CPOJ.CZ> create table t_source as
2 select OBJECT_ID
3 ,owner
4 ,1 seq
5 from all_objects;

Table created.

Elapsed: 00:00:02.08
sczp@CZP1TN.CPOJ.CZ> create table t_master ( id number not null primary key
2 ,owner varchar2(30));

Table created.

Elapsed: 00:00:01.00
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> create table t_detail( id number not null primary key
2 ,owner_id number not null);

Table created.

Elapsed: 00:00:01.00
sczp@CZP1TN.CPOJ.CZ> create sequence t_master_seq;

Sequence created.

Elapsed: 00:00:00.00
sczp@CZP1TN.CPOJ.CZ> create sequence t_detail_seq;

Sequence created.

Elapsed: 00:00:00.00
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> declare
2 type tp_master is table of t_master%rowtype
3 index by binary_integer;
4 type tp_detail is table of t_detail%rowtype
5 index by binary_integer;
6 type tp_source is table of t_source%rowtype
7 index by binary_integer;
8 dv_master tp_master;
9 dv_detail tp_detail;
10 dv_source tp_source;
11 cursor c is
12 select
13 object_id
14 ,owner
15 ,row_number() over(partition by owner order by owner
16 ) seq
17 from t_source
18 order by owner, seq
19 ;
20 j number:=0;
21 function f_seq(p_seq_type in varchar2) return number as
22 l_return number;
23 begin
24 if p_seq_type = 'NEXT' then
25 select t_master_seq.nextval
26 into l_return from dual;
27 elsif p_seq_type = 'CURR' then
28 select t_master_seq.currval
29 into l_return from dual;
30 else
31 select t_detail_seq.nextval
32 into l_return from dual;
33 end if;
34 return l_return;
35 end;
36 begin
37 open c;
38 loop
39 j:=0;
40 fetch c bulk collect into dv_source limit 100;
41 dv_master.delete;
42 dv_detail.delete;
43 for i in 1 .. dv_source.count loop
44 if dv_source(i).seq = 1 then
45 j:=j+1;
46 dv_master(j).id :=f_seq('NEXT');
47 dv_master(j).owner :=dv_source(i).owner;
48 end if;
49 dv_detail(i).id :=f_seq('DETAIL');
50 dv_detail(i).owner_id :=f_seq('CURR');
51 end loop;
52
53 forall j in 1 .. dv_master.count
54 insert into t_master values dv_master(j);
55
56 forall i in dv_detail.first .. dv_detail.last
57 insert into t_detail values dv_detail(i);
58 exit when c%notfound;
59 end loop;
60 end;
61 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.08
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> select count(*)
2 from (select a.owner
3 ,b.id
4 from t_master a
5 ,t_detail b
6 where a.id = b.owner_id);

COUNT(*)
---------
22655

1 row selected.

Elapsed: 00:00:00.00
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> truncate table t_detail;

Table truncated.

Elapsed: 00:00:02.00
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> truncate table t_master;

Table truncated.

Elapsed: 00:00:00.04
sczp@CZP1TN.CPOJ.CZ> INSERT ALL
2 WHEN SEQ = 1 THEN
3 INTO t_master( id,owner)
4 VALUES (owner_id,owner)
5 WHEN SEQ <> 0 THEN
6 INTO t_detail(id ,owner_id)
7 VALUES (t_detail_seq.nextval,owner_id)
8 select owner
9 ,row_number() over(partition by owner order by owner
10 ) seq
11 ,case when row_number() over(partition by owner order by owner
12 ) = 1 then f_seq('NEXT')
13 else
14 f_seq('CURR')
15 end owner_id
16 from t_source
17 order by owner,seq
18 /

22660 rows created.

Elapsed: 00:00:05.02
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> select count(*)
2 from (select a.owner
3 ,b.id
4 from t_master a
5 ,t_detail b
6 where a.id = b.owner_id);

COUNT(*)
---------
22655

1 row selected.

Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.

Elapsed: 00:00:09.00
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> select count(*)
2 from (select a.owner
3 ,b.id
4 from t_master a
5 ,t_detail b
6 where a.id = b.owner_id);

COUNT(*)
---------
22655

1 row selected.

Elapsed: 00:00:00.00
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> truncate table t_detail;

Table truncated.

Elapsed: 00:00:01.09
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> truncate table t_master;

Table truncated.

Elapsed: 00:00:00.06
sczp@CZP1TN.CPOJ.CZ> INSERT ALL
2 WHEN SEQ = 1 THEN
3 INTO t_master( id,owner)
4 VALUES (owner_id,owner)
5 WHEN SEQ <> 0 THEN
6 INTO t_detail(id ,owner_id)
7 VALUES (t_detail_seq.nextval,owner_id)
8 select owner
9 ,row_number() over(partition by owner order by owner
10 ) seq
11 ,case when row_number() over(partition by owner order by owner
12 ) = 1 then f_seq('NEXT')
13 else
14 f_seq('CURR')
15 end owner_id
16 from t_source
17 /

22660 rows created.

Elapsed: 00:00:07.01
sczp@CZP1TN.CPOJ.CZ>
sczp@CZP1TN.CPOJ.CZ> select count(*)
2 from (select a.owner
3 ,b.id
4 from t_master a
5 ,t_detail b
6 where a.id = b.owner_id);

COUNT(*)
---------
22655
1 row selected.

In Response to the original post

A reader, May 31, 2004 - 11:19 am UTC

Intead of
valasekd@DV10G> insert into t_ordered (object_id, seq)
2 select object_id, f_seq()
3 from t_source
4 order by object_id
5 /

Why not just use

insert into t_ordered (object_id, seq)
select object_id , f_seq()
from(
select object_id
from t_source
order by object_id )

This will give you ordered sets .
Why we need analytics.
Tom any thoughts ?

May 31, 2004 - 1:29 pm UTC

too many potential side effects. if it needs to be procedurally sequentially numbered....

It works

A reader, May 31, 2004 - 2:37 pm UTC

This is pure SQL with a function call.

OBJECT_ID SEQ
--------------------------------------- ---------------------------------------
7559 1
9849 2
10173 3
11261 4
13615 5
17287 6
22543 7
22748 8
22798 9
9 rows selected.

even this works insert into t_ordered (object_id, seq)
select object_id , t_seq.nextval
from(
select object_id
from t_source
order by object_id ).

Why to do it procedurally when possible sql-laly.
Problems I see is
The sequence needs to be used just by one session.If seq is cached it looses some number there.
Can you pls elaborate on the side effects ?

May 31, 2004 - 3:40 pm UTC

if you flip a coin 50 times and it comes up heads -- does that mean the obvious? that the coin will always come up heads?

and we have a multi-table insert here, lots of stuff going on.

One more question about row_number()

Victor, June 08, 2004 - 10:23 am UTC

Tom,
I know, I can use row_number() to reset rows numbering in a query.
But is there a way to write a query like

select empno, row_number() over (partition by deptno order by empno) from emp

without the use of analitics (we've got 8.1.7 standard edition and this option is not enabled).
Could you give an example, please. I don't really need them sorted by empno within a partition, just reset the row numbers.

Thanks a lot for the site and the books.

June 08, 2004 - 10:34 am UTC

Well, depends on the size of the set. I'd say "no, you want to do this in code" (eg: the client does the control break processing and the client assigns the row number)

anything without analytics is going to have a huge impact on the query performance. You could use a scalar subquery:

1 select deptno,
2 empno,
3 (select count(*)
4 from emp e2
5 where e2.deptno = emp.deptno
6 and e2.empno <= emp.empno) rn,
7 row_number() over (partition by deptno order by empno) rn2
8 from emp
9* order by deptno, empno
scott@ORA9IR2> /

DEPTNO EMPNO RN RN2
---------- ---------- ---------- ----------
10 7782 1 1
10 7839 2 2
10 7934 3 3

20 7369 1 1
20 7566 2 2
20 7788 3 3
20 7876 4 4
20 7902 5 5

30 7499 1 1
30 7521 2 2
30 7654 3 3
30 7698 4 4
30 7844 5 5
30 7900 6 6

14 rows selected.

on smallish sets.

Couple questions ...

Greg, August 24, 2005 - 2:55 pm UTC

Hi, I started to ask a similar question in another thread on Analytics - but soon I realized it was in the wrong place - so I figured I'd get out of that thread .. sorry about that ...

But I think this thread makes more sense for a couple questions .. (and it has already answered a couple more questions I did have ... ) :)

So let's start simple:

I know this is documented:

select dummy, seq_junk2.nextval from dual order by dummy;
select dummy, seq_junk2.nextval from dual order by dummy
*
ERROR at line 1:
ORA-02287: sequence number not allowed here

But I was hoping you could take it a step beyond the documentation dryly telling me: "you can't do this" ... why not?

To me, this just seems like an overly simple query - fetch a row from dual, process the select portion (and assign the sequence value) .. then order the rows by the order by ..

I'm sure there's a larger/more complex scenario that might explain why this is a "bad thing" .. but I'm just not seeing it right now ... :)

Second question is a bit of a re-post from that other thread .. I know you mentioned there you have a note of it to follow up on it .. but I figured this thread made more sense to put that information in ... (no, I'm not trying to harass you .. I'm not expecting a response from you until you've had a chance to look over your notes more ...) ... sorry for the double post ... I just wanted to get things "in the right place".

(Note to self - "self, do better searches .. " *sigh* )

Why does the following query return "NULL" and not ora-2287? (as you indicated it should in that other thread)

select first_value ( seq_junk2.nextval ) over () from dual;
------more------

FIRST_VALUE(SEQ_JUNK2.NEXTVAL)OVER()
------------------------------------

1 row selected.

August 25, 2005 - 3:17 am UTC

why not?

because all kinds of software have documented restrictions.

For sequences, it has to do with deterministic results (run same query, get same answer under same circumstances).

Using order by (set operations in general) preclude many things.

I believe it should return 2287 and is not working properly currently.

Ok, that makes sense ...

Greg, August 25, 2005 - 7:18 am UTC

"For sequences, it has to do with deterministic results (run same query, get same answer under same circumstances)."

Fair enough .. I forgot about that ... that's fair enough! ;)

I'm just wondering then ... I assume there's a specific order in which Oracle processes a query, isn't there?

For example, if we have a very simple "structure" of a query like this:

select a, f(b), Analytic_function(c), sequence(c)
from d, inline_view(e)
where f
group by g
having h
order by i

what order does Oracle typically execute things in?

To my best understanding, it would be:

1) Process inline_view(e)
2) Join d & e using f
3) Evaluate a, f(b), Analytic_function(c), sequence(c)
(although I'm not sure on the order it evaluates these guys)
4) Group by g
5) Having h
6) order by i

or do I just have that all wrong?
Is the order actually dependant on certain other things? (such as the existence of a function, or the existence of an order by, etc?)

(Just trying to get a better understanding of this ... documentation doesn't cover these kinds of details very well ... heh)

Thanks for you time, Tom!!

August 25, 2005 - 8:37 am UTC

I assume there's a specific order in which Oracle processes a query, isn't there?

no, not at all.

specifically with regards to the inline view!! we do view merging,

select ...
from t, ( select * from t2 where ... ) t2
where t.x = t2.x

is not any different to us from

select ...
from t, t2
where t.x = t2.x
and ......

we merge, we push, we rewrite, we add predicates........

Do not ascribe procedural attributes to a language (SQL) that is defined to be "non procedural"

Ahhh .. I think I see the light!!

Greg, August 25, 2005 - 10:48 am UTC

Thanks again for your patience and explanations!!

No sorting at all

Dmytro, November 21, 2005 - 10:03 am UTC

I can not accept such explanation as there's no sorting in my example. Moreover that code worked in 8.1.7:

CREATE SEQUENCE myseq;

CREATE TABLE src as SELECT ROWNUM id FROM all_objects
WHERE rownum<2;

CREATE TABLE dst AS SELECT * FROM src WHERE 1=0;

INSERT INTO dst
SELECT myseq.NEXTVAL FROM src
UNION ALL
SELECT myseq.NEXTVAL FROM src;

So what is the reason? Should I write 2 different statements and how about read consistency?

November 21, 2005 - 10:39 am UTC

sorry that you cannot accept it?

Not sure what you are not accepting though?

what is the reason for what exactly?

OK, trying to describe better.

Dmytro, November 22, 2005 - 2:14 am UTC

Sorry, I was a bit ambiguous

In your first answer you explained ORA-02287 by using "non deterministic" sorting/grouping. My original query (obviously a bit different from the above) contained UNION clause and failed to recompile on 9i. Ok, I was ready for such behaviour and added ALL as getting distinct values was not my aim. But... nothing had happened: the same ORA-02287 error. I suspect that both in original case with ORDER BY clause and in my own without any sorting at all the reason is the same, most probably an attempt to fix a problem that doesn't really exist. Again, that code worked on 8.1.7 for some years and I can not find any reason why it's not working now. Can you?
Thank you in advance.

November 22, 2005 - 8:30 am UTC

Not sure why the union all is doing that - that could be a bug, however:


ops$tkyte@ORA9IR2> select s.nextval from ( select * from dual union all select * from dual );

   NEXTVAL
----------
         1
         2


will work around it - if you would like, you can pursue the "is it a doc bug or a sql bug" via an iTAR.

Hope this is a bug

Dmytro, November 22, 2005 - 11:08 am UTC

Thank you, in fact I used that workaround. But in 8.1.7 this worked without any workarounds and without any need in explanations of determinism :-)

9.2.0.6

Gabe, November 22, 2005 - 12:18 pm UTC

flip@FLOP> select s.nextval as n from dual union all select 1 as n from dual;
select s.nextval as n from dual union all select 1 as n from dual
*
ERROR at line 1:
ORA-02287: sequence number not allowed here

flip@FLOP> select 1 as n from dual union all select s.nextval as n from dual;
select 1 as n from dual union all select s.nextval as n from dual
*
ERROR at line 1:
ORA-02287: sequence number not allowed here

Union All, Union, Intersect, Minus ... all the same.

November 22, 2005 - 4:19 pm UTC

union, intersect, minus - I "get"

union all - that one doesn't "distinct" so...

Really a great one

ravi kiran, July 13, 2006 - 12:13 am UTC

This was mosty useful lesson I learnt!!!
Thanks Tom... & to those who raised these questions.

Not works even in 8.1.7

karthick, September 13, 2007 - 1:31 am UTC

SQL*Plus: Release 9.0.1.0.1 - Production on Thu Sep 13 10:58:50 2007

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
JServer Release 8.1.7.2.1 - Production

SQL> select name_seq.nextval as seq from dual
  2  union all
  3  select name_seq.nextval as seq from dual
  4  /
select name_seq.nextval as seq from dual
                         *
ERROR at line 1:
ORA-02287: sequence number not allowed here

Not works even in 8.1.7

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How can I use multiinsert statement (INSERT ALL)

seq -1,2,3

Multi-table insert

what about this solution...

RE:what about this solution

Multi-table insert and forall insert comparison

In Response to the original post

It works

One more question about row_number()

Couple questions ...

Ok, that makes sense ...

Ahhh .. I think I see the light!!

No sorting at all

OK, trying to describe better.

Hope this is a bug

9.2.0.6

Really a great one

Not works even in 8.1.7

More to Explore

Analytics

Sequence numbers in "insert into select from order by"

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How can I use multiinsert statement (INSERT ALL)

seq -1,2,3

Multi-table insert

what about this solution...

RE:what about this solution

Multi-table insert and forall insert comparison

In Response to the original post

It works

One more question about row_number()

Couple questions ...

Ok, that makes sense ...

Ahhh .. I think I see the light!!

No sorting at all

OK, trying to describe better.

Hope this is a bug

9.2.0.6

Really a great one

Not works even in 8.1.7

More to Explore

Analytics