index Rnnn_SDE_ROWID_UK

[EricVacher]

Hi everyboby.

When I register a table and a layer with the following commands :

sdetable -o register -t EDMC_GEOMT_REGN_ADMNS -c IDE_GEOMT_REGN_ADMNS -C USER -k SDO -i sde:oracle11g:lbl01-scan.mtq.min.certif/OL1220.WORLD -u schema_user -p password

sdelayer -o register -l EDMC_GEOMT_REGN_ADMNS,GEO_REGN_ADMNS -t SDO_GEOMETRY -C IDE_GEOMT_REGN_ADMNS -e na+ -k SDO -R 2 -i sde:oracle11g:lbl01-scan.mtq.min.certif/OL1220.WORLD -u schema_user -p password

The existing PK index on IDE_GEOMT_REGN_ADMNS column is dropped and an Rnnn_SDE_ROWID_UK index is created for the same column (IDE_GEOMT_REGN_ADMNS).

This append only for 5 tables. All others tables with same kind of geometry stay unchanged. The PK index is still there after registration.

What can produce this effect (drop of the PK and creation of a UK) ?

Thanks for your help.

[DanPatterson_Retired]

You might want to pick a place and add some tags to facilitate getting an answer GeoNet Community Structure

[EricVacher]

Hi Dan.

thank's for your remark. It's my first try with GeoNet. I added two tags and placed my question into Data Managment community.
regards,

[VinceAngelo]

'sdelayer -o register' will register the table with ArcSDE, so your 'sdetable -o register' is moot.

Back in the day, the presence of a PK would crash registration.  If you need a PK, only create it after registration is complete.  The unique key is created, but disabled, so that it doesn't hurt performance.

- V

[EricVacher]

Hi Vince.

Some informations about the context to clarify the situation.

there is 20 tables with spatial data (SDO_GEOMETRY) in our oracle 12c database. We used the ArcMap and ArcCatalog tools (10.3.1 version), but without the SDE schemas objects (no SDE user installed, no TABLE_REGISTRY, LAYERS, etc).

Some functions used by our spatial analyst returns errors, like buffer with more than 100 items, export data to local gdb, and so on. But not every time. Sometime it works, sometime not.

The ESRI support only answer to resolve this situation is :

   Install the SDE schema and register your tables and layers.

I'm in charge of validating if this solution solve our bugs.

So, after installation of the SDE reporitory, I had to register the spatial tables and views.

As many of the functions I'm accustomed to use with SDE tools, like sdetable, sdelayers, sdemigrate, ... , did not have an complet equivalent in ArcTools, ESRI allow (and support) using 10.2.2 sde command instead of ArcTools.

We don't want to mix ArcPy command and SDE command in our production process.

So, until ESRI give us the same functions in ArcPy and in SDE commands, we decided to use only SDE command tools.

that's why I use the sdetable - register.

Why did you say it's moot ?

When I apply the sdelayer - register, the existing PK (1 column) is replaced by an Rnnn_SDE_ROWID_UK, with the same column.

About the Rnnn_SDE_ROWID_UK created, it's not disabled.

And why did this substitution is done on only 5 tables ?

Why it is not done on all our spatial tables ?

As most of our registrated tables did not have the PK replaced by a UK index, I want to avoid this substitution on the 5 one.

Hope this will help to obtain a explanation.

regards,

Eric

[VinceAngelo]

Invoking 'sdelayer -o register' also registers the table with ArcSDE.  If you add ",USER" after the '-C IDE_GEOMT_REGN_ADMNS' in the 'sdelayer' command, you can comment out the 'sdetable' call entirely.  That's not to say having it there hurts anything, but it shouldn't be necessary either.

I've never had good luck with PKs or UKs before registration, so my installation scripts:

• Use SQL to create tables without any special constraints
• Use SQL to build spatial indexes
• Use ArcSDE command-line tools to register tables
• Use SQL to ALTER tables for required constraints
• Use 'sdelayer' to place the layer in load-only I/O mode (if an ArcSDE-enabled app will be doing inserts, otherwise just drop the spatial index)
• Load the data (SQL or ArcGIS client, as appropriate)
• Use 'sdelayer' to place the layer in normal-I/O mode (if it was placed in load-only I/O mode)
• Use SQL to build the remaining indexes

There's no way I can diagnose intermittent  behavior via GoeNet.  Tech Support is the best place to address this.

- V

[EricVacher · Accepted solution]

Thank's Vince.

You didn't directly answer to my problem, but you pointed me in the right direction.

when I had to register a spatial table, I have always used the combination of sdetable and sdelayer.

It's because we also register our descriptive table into the ESRI repository (SDE schema), with the sdetable command alone.

So, from time to time, the combination of those 2 commands (sdetable and sdelayer) is replacing the PK by a Rxxx_SDE_ROWID_UK, at  the sdelayer command executing time.

If I only use the sdelayer command to register my spatial table, PK remain unchanged (no Rxxx_SDE_ROWID_UK index created).

an unknown action must occured under the hood of these command when they are combined.

The correct sdelayer command I use now is :

*=================================*

sdelayer -o register -l EDMC_GEOMT_REGN_ADMNS,GEO_REGN_ADMNS -t SDO_GEOMETRY -C IDE_GEOMT_REGN_ADMNS,USER -e na+ -k SDO -R 2 -i sde:oracle11g:lbl01-scan.mtq.min.certif/OL1220.WORLD -u schema_user -p password

No more sdetable - o register need before sdelayer.