What is the Best way to generate & guarantee sequential numbers in SAP IQ 16 for Large data sets.

[Former Member]

We have a task which requires Sequential Numbers to be assigned during Query Processing to each record. This Data is feed to the table. The Queries are complex and could have 20-50 Joins,functions,distinct  and can generate large data-sets depending on the Filter Parameters.

Query Body
Example

insert into t1 (p1,p2,p3,pn)

select col1,col3,col4,coln from t2 inner join t3 ..........

We have used  Rowid Function ,Identity Column or Auto Increment  default value, Row_Number() over (Order by col) to get the desired results.

Out of these Rowid and Default Auto Increment are having gaps in sequential Numbers generation . For example If the cardinality of the Output result  is 200000 records the maximum Sequence Number is 200220.

By use of Analytic Function without the Window Partition we were able to achieve the correct sequence Numbers. But Overall Query time increased by 35% . This was done in the test environment.

So Is the only way to get the correct sequence is to use Row_Number Analytical Function for large datasets that comes with the price of performance ?

[markmumy · Accepted solution]

I am assuming that what you want is a sequential number on the result set, yes?  So that the final output of the SELECT would be something like this:

1,val1,val2,val3

2,val1,val2,val3

3,val1,val2,val3

Is that's correct, you can simply use the NUMBER() function like this:

insert into t1 (p1,p2,p3,pn)

select NUMBER(), col1,col3,col4,coln from t2 inner join t3 ..........

This will include a sequential number in the final output to be loaded into the table t1.

Mark

[Former Member · Accepted solution]

Thanks Mark. That was helpful. But You see there are distinct also in my Queries , that does not directly work with Number(). In order to resolve that I prepared the statement differently like

insert into t1 (p1,p2,p3,pn)  select Number (),a.col1,a.co2,a.col3,a.col4 from (select distinct col1,col3,col4,coln from t2 inner join t3 .........) a.

But On executing the same it is crashing the IQ. Below is the snippet  from Message Log. Exception Thrown from dflib\dfo_cOrderBy.cxx:1903, Err# 0, tid 3 origtid 3 I. 06/09 11:47:20. 0000000019    O/S Err#: 0, ErrID: 9216 (df_Exception); SQLCode: -1006001, SQLState: 'QBA01', Severity: 23 I. 06/09 11:47:20. 0000000019 [20239]: IQ Internal error. Please report this to Sybase IQ support. -- (dflib\dfo_cOrderBy.cxx 1903)  -- (dflib\dfo_cOrderBy.cxx 1903) I. 06/09 11:47:20. 0000000019  I. 06/09 11:47:20. 0000000019 ************************************************** I. 06/09 11:47:20. 0000000019 ***  Sybase IQ Abort: I. 06/09 11:47:20. 0000000019 ***      From:  stcxtlib\st_server.cxx:744 I. 06/09 11:47:20. 0000000019 ***      PID: 9312 I. 06/09 11:47:20. 0000000019 ***      Message: Program Abort I. 06/09 11:47:20. 0000000019 ***      Thread: 10304  (TID: 3) This code works fine if I remove distinct from the set.

[markmumy · Accepted solution]

OK, that makes sense.  I didn't see a DISTINCT in the original query and that will certainly change things a bit.

As for the stack trace, you should open a case for it.  Can you post the entire statement?

Mark