How surrogate key generator works with (option: increment from maximum value in target table)

[cmurugesan]

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

[LaurieF]

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.

[cmurugesan]

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

[Patrick · Accepted solution]

---

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

---

@cmurugesan 

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

[cmurugesan]

Thanks @Patrick @LaurieF 

The online help in DI Studio provides a bit more information: