S,F,I numbers as related to business table

[LorindaGilbert]

Hi All,

I need to check some indices on some tables in SQL server SDE.  I have the S number for the table that needs to be reindexed, however, I can't find the magic decoder ring for determining the business table name in order to perform the reindex.

I know that the table number in the sde.table_registry gives me the adds and deletes tables, but my experience in the past was that the s, f, and i table numbers weren't the same as the adds and deletes.

Can someone point me in the right direction?

Thanks a bunch!

Lorinda

[MarceloMarques]

To find the Delta Tables and the "i" table we use the registration_id in the sde.table_registry.
To find the  S tables we use the layer_id in the sde.layers table.

A few things to mention.

B table - base table

F table - features table - store the geometries (shape).

               the new versions of the geodatabase do not have a F table anymore

               the shape column is now stored in the base table 

A table - adds table - traditional versioning

D table - deletes table - traditional versioning

S table - spatial index table

There is no esri st_geometry spatial type in SQL Server. We use the native SQL Server Geometry & Geography Spatial Types with the Geodatabase.

SQL Server Geodatabases with the Geometry spatial type you will find an "i" table but there is no "S" spatial index table.

The SQL Server Geodatabase Featureclass has a Base Table, "i" table, Delta Tables (traditional versioning) - A - Adds, D - Deletes, if geodatabase archiving is enabled then there will be another table with the name <base table name>_H. 

"i" table is used to store the increment for the ObjectID, this was done before SQL Server had sequences.

Oracle esri st_geometry spatial type you will find an S table. Hence the Oracle Geodatabase featureclass has a Base Table, Delta Tables (traditional versioning) - A - Adds, D - Deletes, and the S - spatial index table, if geodatabase archiving is enabled then there will be another table with the name <base table name>_H. The H is for History. There is no "i" table.

Oracle Geodatabase does not have "i" table because in Oracle we use sequences for the ObjectID.

Oracle Geodatabases also support the native Oracle SDO Spatial datatype.

PostgreSQL Geodatabases has the esri st_geometry spatial type as well and it also supports the PostGIS Spatial Type.

[MarceloMarques]

--SQL Server Geodatabase Example:

use edit
go

select * from sde.sde_table_registry order by owner, table_name, registration_id;
go

--766 BUILDINGS GIS OBJECTID NULL 16399 1584119243 DEFAULTS 1 Buildings_evw NULL

-- geodatabase registration_id = 766 <== !!!

select count(*) from GIS.BUILDINGS;
go
--2,500,538

select * from GIS.BUILDINGS;
go

--base table and delta tables ( traditional versioning )

exec sp_columns @table_name = 'BUILDINGS', @table_owner = 'GIS';
go
exec sp_columns @table_name = 'A766', @table_owner = 'GIS';
go
exec sp_columns @table_name = 'D766', @table_owner = 'GIS';
go
exec sp_columns @table_name = 'i766', @table_owner = 'GIS';
go

select * from GIS.BUILDINGS;
select * from GIS.A766;
select * from GIS.D766;

select * from GIS.i766;
--2 2500543 -1 2500541

select max(objectid) from GIS.BUILDINGS;
--2500540
select max(objectid) from GIS.A766;

select * from sde.sde_layers order by owner, table_name, layer_id;

--546 NULL BUILDINGS GIS SHAPE ...

--geodatabase layer_id = 546

--base table indexes

EXEC sp_helpindex N'GIS.BUILDINGS';
GO

/*
R766_pk clustered, unique, primary key located on EDIT_BINDEX OBJECTID
*/ --does not list the spatial index, weird!!!

SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name, sys.indexes.name as index_name,
sys.indexes.is_unique, sys.indexes.is_primary_key
FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id
AND sys.tables.object_id = sys.columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id)
AND sys.tables.name = 'buildings'
/*
605453431 BUILDINGS OBJECTID R766_pk 1 1
605453431 BUILDINGS SHAPE S546_idx 0 0                  <<== spatial index
*/

--"i" table indexes

EXEC sp_helpindex N'GIS.i766';
GO

/*
i766_pk clustered, unique, primary key located on EDIT_FDATA id_type, num_ids, base_id
*/

--adds table indexes

EXEC sp_helpindex N'GIS.A766';
GO

/*
a766_rowid_ix1 clustered, unique, primary key located on EDIT_AINDEX OBJECTID, SDE_STATE_ID
a766_state_ix2 nonclustered located on EDIT_AINDEX SDE_STATE_ID
*/

--deletes table indexes

EXEC sp_helpindex N'GIS.D766';
GO

/*
d766_idx2 clustered located on EDIT_DINDEX DELETED_AT
d766_pk nonclustered, unique, primary key located on EDIT_DINDEX SDE_STATE_ID, SDE_DELETES_ROW_ID, DELETED_AT
*/

[MarceloMarques]

Oracle Geodatabase Example:

select * from sde.table_registry order by 1,3,2;

--10 FIRESTATIONS AUX OBJECTID 16399 1398299376 GIS_AUX_OWNER 1 FIRESTATIONS_EVW

-- geodatabase registration_id = 10

--base table and delta tables ( traditional versioning )
describe aux.firestations;
describe aux.A10;
describe aux.D10;

select * from aux.firestations;
select * from aux.A10;
select * from aux.D10;

select * from sde.layers order by 1,2;

--5 AUX FIRESTATIONS SHAPE 71303299 0 3514.99746330281 0 0 2306975.502928 706427.937928006 2333039.252928 739136.557927996 1398299376 GIS_AUX_OWNER 1 3 0

--geodatabase layer_id = 5

--spatial index table
describe aux.S5_IDX$;
select * from aux.S5_IDX$;

--spatial index table indexes

select * from all_indexes
where lower(owner) = 'aux' and lower(table_name) = 's5_idx$';

/*
AUX S5$_IX2 NORMAL AUX S5_IDX$ TABLE NONUNIQUE DISABLED GIS_AUX_SDATA 2 255 131072 131072 1 2147483645 0 10 YES 0 1 69 1 1 1 VALID 69 69 07-JAN-2024 06:04:04 4 1 NO N N Y DEFAULT DEFAULT DEFAULT NO 100 YES NO YES NO VISIBLE YES NO FULL NO NO

AUX S5$_IX1 IOT - TOP AUX S5_IDX$ TABLE UNIQUE DISABLED GIS_AUX_SDATA 4 255 131072 131072 1 2147483645 0 5 0 0 YES 0 1 69 1 1 0 VALID 69 69 07-JAN-2024 06:04:04 1 1 NO N N Y DEFAULT DEFAULT DEFAULT NO 0 YES NO NO NO VISIBLE YES NO FULL NO YES
*/

--base table indexes

select * from all_indexes
where lower(owner) = 'aux' and lower(table_name) = 'firestations';

/*
AUX R10_SDE_ROWID_UK NORMAL AUX FIRESTATIONS TABLE UNIQUE DISABLED GIS_AUX_BINDEX 4 255 131072 131072 1 2147483645 0 0 NO 0 1 69 1 1 1 VALID 69 69 07-JAN-2024 06:04:04 4 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES NO FULL NO NO

AUX A5_IX1 DOMAIN AUX FIRESTATIONS TABLE NONUNIQUE DISABLED 0 0 0 YES VALID 07-JAN-2024 06:04:04 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO SDE ST_SPATIAL_INDEX ST_GRIDS= 3514.99746330281 ST_SRID = 300002 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4 TABLESPACE GIS_AUX_SDATA NO VALID VALID NO NO NO VISIBLE USER_MANAGED YES NO FULL NO NO
*/

--adds table indexes

select * from all_indexes
where lower(owner) = 'aux' and lower(table_name) = 'a10';

/*
AUX A10_STATEID_IX1 NORMAL AUX A10 TABLE NONUNIQUE DISABLED GIS_AUX_AINDEX 4 255 131072 131072 1 2147483645 0 0 NO 0 0 0 0 0 0 VALID 0 0 07-JAN-2024 06:04:03 4 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES NO FULL NO NO

AUX A10_PK NORMAL AUX A10 TABLE UNIQUE DISABLED GIS_AUX_AINDEX 4 255 131072 131072 1 2147483645 0 0 NO 0 0 0 0 0 0 VALID 0 0 07-JAN-2024 06:04:03 4 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES NO FULL NO YES

AUX A5_IX1_A DOMAIN AUX A10 TABLE NONUNIQUE DISABLED 0 0 0 YES VALID 07-JAN-2024 06:04:03 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO SDE ST_SPATIAL_INDEX ST_GRIDS= 3514.99746330281 ST_SRID = 300002 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4 TABLESPACE GIS_AUX_SDATA NO VALID VALID NO NO NO VISIBLE USER_MANAGED YES NO FULL NO NO
*/

--deletes table indexes

select * from all_indexes
where lower(owner) = 'aux' and lower(table_name) = 'd10';

/*
AUX D10_IDX1 NORMAL AUX D10 TABLE NONUNIQUE DISABLED GIS_AUX_DINDEX 4 255 131072 131072 1 2147483645 0 0 NO 0 0 0 0 0 0 VALID 0 0 07-JAN-2024 06:04:04 4 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES NO FULL NO NO

AUX D10_PK NORMAL AUX D10 TABLE UNIQUE DISABLED GIS_AUX_DINDEX 4 255 131072 131072 1 2147483645 0 0 NO 0 0 0 0 0 0 VALID 0 0 07-JAN-2024 06:04:04 4 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES NO FULL NO YES
*/

[soofigadgets]

---

@LorindaGilbert wrote:
Hi All,

I need to check some indices on some tables in SQL server SDE.  I have the S number for the table that needs to be reindexed, however, I can't find the magic decoder ring for determining the business table name in order to perform the reindex.

I know that the table number in the sde.table_registry gives me the adds and deletes tables, but my experience in the past was that the s, f, and i table numbers weren't the same as the adds and deletes.

Can someone point me in the right direction?

Thanks a bunch!

Lorinda

---

To find the business table name associated with the S number in SQL Server SDE, you can check the SDE.TABLE_REGISTRY. While the S, F, and I table numbers might not directly correspond to adds and deletes, the SDE.TABLE_REGISTRY should contain information about the relationships between these tables.

You can query the SDE.TABLE_REGISTRY using the S number to find the associated business table name. Here's an example query:

sqlCopy code

SELECT TABLE_NAME FROM SDE.TABLE_REGISTRY WHERE REGISTRATION_ID = 'YourSNumber';

Replace 'YourSNumber' with the actual S number you have. This should provide you with the business table name associated with the given S number, helping you perform the reindex on the correct table.

Best of luck!

[soofigadgets]

In the context of ArcSDE (Spatial Database Engine), S, F, and I numbers are identifiers associated with different types of tables:

1. S Number: Represents the state tables, storing the current state of spatial data.

2. F Number: Signifies the adds and deletes tables, keeping track of changes made to the spatial data.

3. I Number: Corresponds to the spatial index tables, which optimize spatial queries for faster retrieval.

These numbers are crucial in managing spatial data within the geodatabase, allowing for efficient tracking of changes and optimization of spatial queries. The relationship to a business table is established through the Registration_ID in the SDE.TABLE_REGISTRY, connecting the S, F, and I tables to their associated business table.

[soofigadgets]

---

@LorindaGilbert wrote:

Hi All,

I need to check some indices on some tables in SQL server SDE.  I have the S number for the table that needs to be reindexed, however, I can't find the magic decoder ring for determining the business table name in order to perform the reindex.

I know that the table number in the sde.table_registry gives me the adds and deletes tables, but my experience in the past was that the s, f, and i table numbers weren't the same as the adds and deletes.

Can someone point me in the right direction?

Thanks a bunch!

Lorinda

---

In the context of ArcSDE (Spatial Database Engine), S, F, and I numbers are identifiers associated with different types of tables:

1. S Number: Represents the state tables, storing the current state of spatial data.

2. F Number: Signifies the adds and deletes tables, keeping track of changes made to the spatial data.

3. I Number: Corresponds to the spatial index tables, which optimize spatial queries for faster retrieval.

These numbers are crucial in managing spatial data within the geodatabase, allowing for efficient tracking of changes and optimization of spatial queries. The relationship to a business table is established through the Registration_ID in the SDE.TABLE_REGISTRY, connecting the S, F, and I tables to their associated business table.