Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
How to generate global SEQUENCE ? |
Sun, Aug 12 2012 10:30 AM | Permanent Link |
ae1080 | How to generate common SEQUENCE numbers a cross tables? in oracle i can use sequence but in elevatedb it is associated with specific table .
|
Sun, Aug 12 2012 10:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | ae1080
You'll need to handle it manually. Create a small table just to hold the next (or last) number, add a before post trigger to each table and in there retrieve the next value, set your field and update the small table. Roy Lambert [Team Elevate] |
Tue, Aug 14 2012 4:54 PM | Permanent Link |
Barry | Roy,
This brings up an interesting point. Does the user need speed or ids with no gaps in the sequence? 1) update the NextId table inside of a transaction for the other tables (slower but no sequence gaps), 2) get the NextId number as quickly as possible (faster but allows for sequence gaps) 1) If the NextId table is updated within a transaction of the post to the other tables, then the NextId can be rolled back if the transaction of the other tables fails, and the NextId number can be reused by someone else. But I suspect since other tables are involved, the transaction time will go up considerably and may lock others out of the NextId table. 2) If this option is used, the NextId is immediately returned to the calling routine and if the transaction for the other tables are rolled back, then the NextId number is discarded and it produces a gap in the sequence. I would likely opt for #2 unless the user is willing to sacrifice speed for no gaps in the sequence. What is your personal preference, or is there another solution? Barry |
Wed, Aug 15 2012 4:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
Its not only speed but multi-user operation that would potentially suffer with a transaction. The point is that transactions should be as fast, and maybe faster than other operations BUT whilst that transaction is in force no-one else could do anything that required a new ID fetching because that table would be locked. Roy Lambert |
Wed, Sep 5 2012 8:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Barry,
<< I would likely opt for #2 unless the user is willing to sacrifice speed for no gaps in the sequence. >> That is definitely the best solution, otherwise the ID table could become a hot-spot in the database. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |