OBJECTID's assigned when appending to ArcSDE Database

[AdamTobin]

Hello,

I am appending a set of records from a File Geodatabase to a non-versioned SDE feature dataset.

Can anyone explain why the OBJECTID's assigned to these records by SDE are not sequential?

For example, the original SDE feature dataset has 139,661 features with OBJECTID's 1 - 139,661.  When I append 1,481 records from a File Geodatabase, SDE assigns OBJECTID's from 139,662 - 141,318 but with gaps between 139,664 - 139,841.

Is this something I should worry about?

Thanks,

Adam Tobin

[BenLin]

Hi Adam,

An objectID is a unique, not null integer column used to uniquely identify rows in tables in a geodatabase. ObjectIDs are limited to 32-bit values, which store a maximum value of 2,147,483,648.

ArcGIS/ArcSDE software will automatically append the ObjectID based on the next row id. In Your case, OBJECTID was maintained by SDE/Geodatabase, and your next row id (before appending the 1,481 rows) might be a number bigger than your total rows, like 139,841. That's why it appends after that number.

Here is a quick example, that I have a table BUILDINGS (registration_id = 40) owned by GIS, and I am using SQL to insert a new ST_GEOMETRY feature. In this case, I need to run a stored procedure to get the right OBJECTID from the next row id. The next row id number might be far larger than my total existing features because I had deleted a lot of features from BUILDINGS before. Please keep in mind that the ObjectIDs would NOT be re-useable, and it will keep growing until you delete the whole feature class and re-create the feature class again.

Example SQL code:
INSERT INTO GIS.BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('GIS', 40),
18907, 'BLD', 'RES', sde.st_geometry
('polygon (( 2219520.56768649 387051.66985716,
2219525.34823696 387079.52399077, 2219536.03133855 387077.71905252,
2219539.05578917 387095.47546386, 2219528.17754562 387097.32910505,
2219528.61661291 387099.81695550, 2219489.00622816 387106.54876471,
2219480.81097279 387058.40167483, 2219520.56768649 387051.66985716))',
17)
);

As long as you are using ArcGIS software dealing with the SDE feature class, you don't need to worry about the gaps between ObjectIDs.

For your reference, here are some help documents relate with this topic.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/What_is_an_objectID/006z00000009000000...
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Workflow_Using_SQL_with_existing_featu...

-Ben

[AdamTobin]

Hi Ben,

Your response has clarified the theory.  However, I'm still a little confused, as we have made no edits to the original SDE layer prior to the append so there is no obvious reason for the row_id to have incremented.  Also, the gaps occur randomly during the middle of a single append process, which I am running from ArcToolbox.

Do you have any explanations as to why this might be?

Thanks,

Adam

[ChrisBeaudette]

Hi Adam,

<snip>

In this case, I need to run a stored procedure to get the right OBJECTID from the next row id.

<snip>

Example SQL code:
INSERT INTO GIS.BUILDINGS
(objectid, fid, type, subtype, shape)
VALUES
(sde.version_user_ddl.next_row_id('GIS', 40),
18907, 'BLD', 'RES', sde.st_geometry
('polygon (( 2219520.56768649 387051.66985716,
2219525.34823696 387079.52399077, 2219536.03133855 387077.71905252,
2219539.05578917 387095.47546386, 2219528.17754562 387097.32910505,
2219528.61661291 387099.81695550, 2219489.00622816 387106.54876471,
2219480.81097279 387058.40167483, 2219520.56768649 387051.66985716))',
17)
);

As long as you are using ArcGIS software dealing with the SDE feature class, you don't need to worry about the gaps between ObjectIDs.

For your reference, here are some help documents relate with this topic.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/What_is_an_objectID/006z00000009000000...
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Workflow_Using_SQL_with_existing_featu...

-Ben

Thanks Ben, this is exactly what I was looking for.  Also in case anyone else was wondering, the referenced link has the SQL for how to get the registration_id from the table and owner names.

Additional question:  I'm using Oracle 10g (and eventually 11g) but the procedure "version_user_ddl.next_row_id" doesn't seem to exist.  If I run the command:

exec sde.version_user_ddl.next_row_id('SDE', 50);

Oracle returns:

Error starting at line 13 in command:
exec sde.version_user_ddl.next_row_id('SDE', 50)
Error report:
ORA-06550: line 1, column 7:
PLS-00221: 'NEXT_ROW_ID' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I don't see any like-named procedures in my SDE instance.  What should I be using for Oracle10g/11g?  Or is that a custom procedure?

[BenLin]

Hi Adam,

Ok, we can double check the next sequence before we append any new data to the feature class first, like following:

SELECT R40.nextval from dual;

This will give us the next objectid before we actually append any new rows to the feature class.

You may also refer to the KB article regarding "HowTo:  Alter an ArcSDE row_id sequence in Oracle"
http://support.esri.com/en/knowledgebase/techarticles/detail/31437

Regards,
Ben