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.
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 ?