Surrogate Key Generation
We have a requirement to generate the surrogate key for huge table in Vertica. We tried with multiple options like IDENTITY , AUTO_INCREMENT and SEQUENCE NUMBER. These options are not working with our requirement. Its working in first session. If we again try to load in new session, its starting with new value (like session1 - starting with 1, session2 - starting with 500001 and session3 - starting with 1000001. We don't want like this. We want continue the number (like Max(Number)+1) in every load. We also tried with Row_Number() over() + Max(Number) . But INSERT-SELECT is giving performance issues with this option. Can you please suggest is there any better way in vertica to load the data with surrogate key.