Is it absolutely necessary to separate user data from system tables

165
5
08-28-2022 09:16 PM
yockee
by
Occasional Contributor

Hi,

I am wondering the reason of separating user data tablespace from SDE system tablespace. I know its better to separate them to avoid SDE tablespace from filling up. But, what if the SDE tablespace has space autoincrement  turned on ?

How does the separation of tablespace relate to database backup ? Would it be easier to backup and restore when they are separated or otherwise ? 

 

Need your advice

0 Kudos
5 Replies
George_Thompson
Esri Frequent Contributor

It is not necessary, but recommended to separate the system tables (SDE schema items) and data owner(s) tables (i.e., DATA / GIS / etc.)

In terms of backup / restore, as long as you export the SDE schema as part of the backup and import the SDE schema FIRST then the other data schemas, then it does not really matter.

I could see how having only one tablespace would make it easier. So ultimately it is your (or the DBA's call).

--- George T.
0 Kudos
yockee
by
Occasional Contributor

So, if the reason of separating tablespace is based on avoiding the system table to fill up, then it is not exactly correct because the tablespace, where system table resides, can be set to automatically increment its size. Correct ?

0 Kudos
George_Thompson
Esri Frequent Contributor

It is not based on avoiding the tablespace to fill up, I think it has to do with a "separation of powers" and limiting potential impact to the separate schemas.

Here is a link to some good Geodatabase admin information; https://community.esri.com/t5/mapping-blog/pro-3-x-database-guide-books-for-mapping-amp/ba-p/1190601

It may not be the exact extensions that you use but is still good information. 

--- George T.
0 Kudos
VinceAngelo
Esri Esteemed Contributor

Fragmentation is the thing that kills database performance. By mixing "system" data and "user" data, you increase fragmentation.  The auto-increment on the SDE tablespace should be small, while the auto-increment on user data should be large (again, to avoid fragmentation).

The same principle should be applied to OS disks (C: for Microsoft, D:,E:,... for  applications; / & usr for Linux, /dataN for applications).

You don't have to conform to best practice, but you don't have to have optimal performance, either.

- V

0 Kudos
MarceloMarques
Esri Regular Contributor

yes, the sde geodatabase repository tables must be stored in a separate tablespace from the data owner gis data, this is a database administration best practices and helps with datafiles contention and fragmentation and with maintenance tasks such as gather statistics and rebuild indexes, and to go further you shall separate tables and indexes to get even more benefits in these areas. If you are interested read my database best practices guide books in the link below, it is full of best practices and template scripts that you can use.
Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

| Marcelo Marques | Principal Product Engineer | Esri |
| OCP - Oracle Certified Professional |
0 Kudos