1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Party_id in hz_parties missing values

Discussion in 'Oracle Financials' started by Oracle Tech, May 12, 2016.

  1. Oracle Tech

    Oracle Tech Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Hi,

    We have noticed that in HZ_PARTIES table, the party_id columns are not in sequence.
    We are curious to know why values are missing in between.

    TCA

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The column is most likely populated with a sequence, which will generate gaps due to failed inserts, transaction rollbacks and unused cached sequence values. This is not unusual and should not be a cause for concern.
     
  3. Oracle Tech

    Oracle Tech Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Thanks David, I agree this is not a area of concern as we have no issues with any data or transactions. But client is looking for a more Oracle Standard answer that it is an intended functionality.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The Oracle documentation for sequences clearly states that using sequences does not guarantee that there will be no gaps. My guess is that the sequence is created with a cache of 20; the first call to get the next sequence value populates 20 values for the calling session; each additional session gets another 20 cached values after that first call. To illustrate this:

    Session 1 1,2,3,4,5,65,7,8,9,10.11,12,13,14,15,16,17,18,19,20
    Session 2 21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40
    ...

    As each session calls for the next value it's returned from the local session cache; if an insert is rolled back that sequence value is lost so if sequence value 3 was inserted and then rolled back and the insert was attempted again the values in the table would be;

    1
    2
    4

    since 3 no longer exists in the cache. If a session terminates before using all of its cached values those as also lost so if Session 1 used values to 13 and then disconnected all of the remaining values (14-20) are lost. This is the way sequences work, when cached, and why gaps occur when using a sequence. Again, this is standard Oracle behavior.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Read here to see the Caution from Oracle on using sequences:

    https://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CNCPT611

    It states what I just told you, but as an 'official' statement from the Oracle documentation, which should satisfy your client.
     
  6. Oracle Tech

    Oracle Tech Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore