topic Re: Max limit for ObjectId - any way of resetting it? in Data Management Questions

Max limit for ObjectId - any way of resetting it?

JonSmith1 — Wed, 05 Oct 2011 09:29:09 GMT

Hi,

I have an old database (oracle 10) using arcsde 9.2 and it has stopped allowing rows to be inserted. On inspection I have noticed that that objectid field is at 2^32 -1 limit. Is there a way of resetting the objectid (or the respective sequence) back? Thanks.

Jon.

Re: Max limit for ObjectId - any way of resetting it?

VinceAngelo — Wed, 05 Oct 2011 10:19:45 GMT

You had two billion features pass through without ever once truncating the table?

Oracle will not allow you to reset a sequence. Before we can suggest viable options,
you need to describe that table -- Is it versioned? Part of a topology? What operations
occur? What are the min, max, and mean objectid values? How many features in
the table?

- V

Re: Max limit for ObjectId - any way of resetting it?

JonSmith1 — Wed, 05 Oct 2011 20:35:45 GMT

Hi,

Thanks for reply. We have deleted data based on the age of the data - but no we haven't truncated it.

> Oracle will not allow you to reset a sequence.

What would be the consequences of dropping and recreating the sequence? Could we also change the structure of the index so that it cycles rather than stop once it gets to 2^32.

> Before we can suggest viable options, you need to describe that table --

> Is it versioned?

No.

> Part of a topology?

No.

> What are the min, max, and mean objectid values?

Not sure about the min. I think an overflow was being reported (i.e. -2^32-1). The max value was 2^32 -1. Not sure about the mean objectid.

> How many features in the table?

Around 200 million.

Jon.

Re: Max limit for ObjectId - any way of resetting it?

VinceAngelo — Wed, 05 Oct 2011 23:04:54 GMT

The main problem with drop-and-replace on the sequence is that the sequence reader is probably
going to use MAX(objectid) to trash your effort. 200M features is a lot of features to reload, so you
might look at creating a NEWIDS table that pairs the orginal OBJECTID with "rownum as new_oid",
then UPDATE using that driver table. THEN you can replace the sequence.

For the future, you might consider using partitions to manage the aging process, with LOCAL indexes
to keep them valid when you lop off the aged out fragment (the "delete" is instantanous, and you can
organize the temporal chunks to reduce spatial fragmentation and swap partitions instantaneously
as well -- nothing quite like a highly performanant very large table!).

- V

BTW: The limit on a 32-bit integer is 2^31-1 -- -2^31 is the signed minimum and 2^32-1 is the
unsigned maximum.

Re: Max limit for ObjectId - any way of resetting it?

JonSmith1 — Mon, 10 Oct 2011 11:58:15 GMT

Hi Vangelo,

>   The main problem with drop-and-replace on the sequence is that the sequence reader is probably
>    going to use MAX(objectid) to trash your effort. 2

Damn. I was hoping it wouldn't do something like that. Oh well.

> so you
>    might look at creating a NEWIDS table that pairs the orginal OBJECTID with "rownum as new_oid",
>    then UPDATE using that driver table. THEN you can replace the sequence.

Thanks. I am pursuing this idea. My one (maybe two) follow-up question is that the table (lets call it B1) also has S1 and F1 tables dependencies with a LOCATION field that ties the three tables together. I guessing some time in the near future the same problem that happened with the OBJECTID will also happen with the LOCATION field. So I am further guessing that the same method will apply for fixing this as well. I'm not exactly sure the sequence that applies to this field though? Is there a way of tying the sequence to the field that it affects?

>    For the future, you might consider using partitions to manage the aging process, with LOCAL indexes
>    to keep them valid when you lop off the aged out fragment (the "delete" is instantanous, and you can
>    organize the temporal chunks to reduce spatial fragmentation and swap partitions instantaneously
>    as well -- nothing quite like a highly performanant very large table!).

Heh. This was going to be the last item I was going to be working on for the project but then the customers thought it was in a good enough state as it was and decided to stop the project before I got around to working through how this was done. Well that was several years ago - I did say it was quite an old system. http://forums.arcgis.com/images/icons/icon10.png

However I wasn't sure how to handle the two tables (F1 and S1) that came off of it in Oracle 10g. I will raise that question in another post though as I'd be interested to know how this could be handled.

Jon.

Re: Max limit for ObjectId - any way of resetting it?

VinceAngelo — Mon, 10 Oct 2011 12:30:51 GMT

You could use the same procedure for hacking the SHAPE and FID columns in the business and
'Fn' tables in an SDELOB storage layer (Sn could be fixed by placing the layer in LOAD_ONLY I/O
mode), but this is much riskier. You might be better off reloading the table with ST_GEOMETRY
storage (which would eliminate the Fn/Sn issue) -- some of my colleagues used 'asc2sde' to
load a 340 million row table in only 22 hours, so the reload in the new format might take less
than a day.

- V