DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: sequence id

  1. #1
    Join Date
    Mar 2001
    Posts
    38
    Hello,
    Can anybody give me any thoughts on .......
    I have table called a,b,c have a column objectid which is
    unique.i have create a sequence having values1,2......
    these particular tables are being loaded manually.Rest
    of the table are being loaded from the application ,in which
    they have written some java classes where they first multiply
    seqid with 1000. then they increment by 1 with counter until
    it reach to 1999 and then they call database for next id.
    this objectid should remain unique across database.
    what should i create so that it will use the same logic as appl.
    procedure......... and how

    thanks,
    el472@hotmail.com

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Some more explanation would be helpful. If this is a mult-user app, how can the application be making up its own IDs?

    - Chris

  3. #3
    Join Date
    Mar 2001
    Posts
    38
    By doing that there will be lesser round trip to database for
    sequence id.For A,B,C lookup tables are only going to
    be loaded directly like using sqlplus,sqlloader etc....
    so what i did was i create a procedure...

    create or replace PROCEDURE test_seq is
    SEQ_ID NUMBER;
    SEQ_MAX NUMBER;
    NEXT_ID NUMBER;
    begin
    SELECT ID_NUM,ID_MAX INTO SEQ_ID ,SEQ_MAX FROM ID_TABLE;
    IF ( SEQ_ID = SEQ_MAX) THEN
    SELECT IDGEN_SEQ . NEXTVAL INTO NEXT_ID FROM DUAL;
    SEQ_MAX := NEXT_ID + 999;
    ELSE
    NEXT_ID := SEQ_ID + 1;
    END IF;
    update ID_TABLE
    SET ID_NUM := NEXT_ID;
    AND ID_MAX := SEQ_MAX ;
    end;

    id_table will have only one row e.g
    id_num,id_max
    1000,1999

    so when it reach to 1999 it will look in idgen_seq get the next
    seq no.and i am calling this proc. in trigger

    by doing so object_id will remain unique across database.

    am I doing Right!!!!!!!!!
    el472@hotmail.com

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I'm sorry, I'm definitely not following this. I still don't understand how they can be making up their own IDs.

    Let me back up...
    Most applications are built in one of the following structures:

    Fat Client: Multiple clients all contain the application logic.
    Thin Client / Multiple Application Servers
    - Client app has little intelligens - all work done in application servers
    Thin Client / Single Application Server
    Fat Server - Mainframe-type apps

    Now, for either of the first 2, it would not seem possible for the application to make its own ids as there are multiple copies of the application.
    I'm assuming this is not a mainframe-type app.

    This leaves us with a single application server, which is the only one where your scenario would work easily.

    Is this the setup you have?

    If so, then one would also assume that the application sever is in relatively close proximity to the database server - why shouldn't it be? Therefore, round trips for things as insignificant as Sequences is not something to worry about. Therefore, the only setup that would support this scenario is actually the one that needs it the least, so why do it?

    Now, to the problem itself, which I still don't really get. What does "object_id will remain unique across database." mean? You said it in both posts. Do you mean across multiple tables? Do you mean across multiple databases? Or are you talking about a single table in a single database, in which case, what is wrong with using a sequence?

    What is the application actually doing with this ID - specifically?

    Sorry for all the questions, but I'm really lost on this one.

    - Chris




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width