on 06-08-2016 7:19 AM
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 ?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.