Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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 ?

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question