Dear All,
I am new to SAS world and have little experience in Oracle. Based on my oracle experience it is better to use sequence for any number (key) field as the sequence saves the current value and next value.
When I see below options in "Surrogate key generator" (DI studio)
1) increment from maximum value in target table
2)increment from specified value
3)increment based on another table
My system loads millions and millions of data daily. What I read from option 1 as "select max value from dim_tab" before proceeding the number generation. isn't it performance issue when I have millions (approx 100 million records)?
@cmurugesan wrote:
@LaurieF thanks Lauri you brought up a good point. yes, it is indexed key column but still referring sequence is a faster way than going to table/index.
it would be great referring sequence if we want to load input files in parallel to the same target table. it is surprising to me why SAS not having an option to refer sequence for key generator.
Most DIS transformations are first written to work with SAS files (SAS tables). Database specific behaviour for such transformations is mostly only partially implemented. I guess adding more database specific options would over-complicate the transformations.
Like you I'd wish there would come more database specific transformations already out of the box but.... You can always implement your own reusable custom transformations.
If this is about loading from Oracle to Oracle table then I'd most certainly go for a custom transformation which generates explicit pass-through SQL and there you can use any Oracle specific syntax like calling a sequence object.
You could also have Oracle populate your id column directly either by calling the sequence object via an after insert trigger or beginning with Oracle 12c by using an identity column.
https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1
If there's no index on the column, yes: it'll do a table scan. If you've got an index on it though, it should be quite responsive. I'd be surprised if it'd take more than a few seconds then.
@LaurieF thanks Lauri you brought up a good point. yes, it is indexed key column but still referring sequence is a faster way than going to table/index.
it would be great referring sequence if we want to load input files in parallel to the same target table. it is surprising to me why SAS not having an option to refer sequence for key generator.
@cmurugesan wrote:
@LaurieF thanks Lauri you brought up a good point. yes, it is indexed key column but still referring sequence is a faster way than going to table/index.
it would be great referring sequence if we want to load input files in parallel to the same target table. it is surprising to me why SAS not having an option to refer sequence for key generator.
Most DIS transformations are first written to work with SAS files (SAS tables). Database specific behaviour for such transformations is mostly only partially implemented. I guess adding more database specific options would over-complicate the transformations.
Like you I'd wish there would come more database specific transformations already out of the box but.... You can always implement your own reusable custom transformations.
If this is about loading from Oracle to Oracle table then I'd most certainly go for a custom transformation which generates explicit pass-through SQL and there you can use any Oracle specific syntax like calling a sequence object.
You could also have Oracle populate your id column directly either by calling the sequence object via an after insert trigger or beginning with Oracle 12c by using an identity column.
https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.