Skip to Content
avatar image
Former Member

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

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 ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jun 08, 2016 at 03:05 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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