topic Re: Rebuild Indexes and Analyze arcpy vs. Oracle in Geodatabase Questions

Rebuild Indexes and Analyze arcpy vs. Oracle

forestknutsen1 — Mon, 12 Nov 2018 22:54:58 GMT

We are trying to improve our geodatabase health. So we would like to:

Rebuild Indexes—Help | ArcGIS for Desktop

Update database statistics—Help | ArcGIS Desktop

after a compress with arcpy.

According to our DBA oracle is already doing this as needed. Using estimate statistics and rebuild indexes. As I understand it oracle preforms an "analysis" per table to see if the estimate statistics and/or rebuild indexes is needed for any given table.

So, my questions are:

1) Is the rebuild indexes arcpy function the same as calling oracle's rebuild index?

2) Is the analyze arcpy function the same as calling oracle's estimate statistics?

3) Can we just go with what oracle has in place or is it recommended that we update indexes and statistics with arcpy after a compress?

Re: Rebuild Indexes and Analyze arcpy vs. Oracle

shan_sarkar — Tue, 13 Nov 2018 05:22:48 GMT

For arcgis there are two types of indexes namely Spatial index and Attribute index. As far as I am aware and correct me if I am wrong takes care of the attribute index only.

I have tested this by creating an attribute index from Oracle SQL Developer and it was visible in the same table/feature class when viewed from ArcCatalog and vice versa.

For building/rebuilding Spatial index I would suggest to use Rebuild Indexes—Help | ArcGIS Desktop in a python script and automate the process when you compress the DB.

I will go ahead and follow this post just to see if I missed something.

I hope this helps!

~Shan

Re: Rebuild Indexes and Analyze arcpy vs. Oracle

JoshuaBixby — Tue, 13 Nov 2018 14:41:54 GMT

Without diving into the weeds, the biggest difference is that Oracle has no clue there is such a thing called an enterprise geodatabase, so it is impossible for Oracle to update geodatabase metadata related to indexes. In short, use Esri tools to update geodatabases unless there are outside factors that make it impossible or impractical.

Re: Rebuild Indexes and Analyze arcpy vs. Oracle

forestknutsen1 — Tue, 13 Nov 2018 17:13:40 GMT

Thanks Shan

Re: Rebuild Indexes and Analyze arcpy vs. Oracle

forestknutsen1 — Tue, 13 Nov 2018 17:21:03 GMT

Thanks Joshua. So, I am guessing that you would feel the same way about analyze/statistics...

There is no reason why we cannot implement the python. It is just the that the DBA and the senior GIS analyst programmer feel that esri's python functions are redundant.

Re: Rebuild Indexes and Analyze arcpy vs. Oracle

JoshuaBixby — Tue, 13 Nov 2018 17:25:48 GMT

Whether Esri or other software, empirical data is always better than guessing. The DBA and senior GIS analyst programmer can prove it, one way or another, but running SQL traces on the database you execute the commands. The Esri functions obviously use Oracle functions under the hood, but the question is what else are the doing with geodatabase system tables that Oracle would not do.

Re: Rebuild Indexes and Analyze arcpy vs. Oracle

forestknutsen1 — Tue, 13 Nov 2018 17:58:49 GMT

But Esri must have an opinion as well...

Re: Rebuild Indexes and Analyze arcpy vs. Oracle

forestknutsen1 — Mon, 07 Jan 2019 23:02:52 GMT

So, I did talk to esri about this one. They say the arcpy tools just hits the oracle tools. The arcpy tools are not doing anything extra or special. However, with that said the still recommend running the tools via arcpy. I never got any supporting documentation for why this is.

We did end up implementing the arcpy tools based on esri recommendation.