GDB_BRANCH_ID How to manage indexes?

[Oiligriv]

Hello
With Utility Network and Oracle DB we noticed that some queries are particularly slow, during BranchVersioning queries the GDB_BRANCH_ID field is used on which there is no native index, and it is impossible via ArcGIS Pro to run the index on that field.
This information was extracted via Oracle Central Advisor.
all oracle suggestions are mainly related to
- Consider running the Access Advisor to improve the physical schema design or creating the recommended index.WATER.WATERLINE("ASSETGROUP", "GDB_BRANCH_ID")WATER.WATERLINE("OBJECTID", "GDB_FROM_DATE" DESC )
- Consider accepting the recommended SQL profile to use parallel execution for this statement.
- Consider accepting the recommended SQL profile

I would not like to apply indexes via Oracle, but I guess it makes more sense that they are managed by ArcGIS Pro or natively.

do you have any ideas?

@gpalombi_wt 

Thanks
Virgilio

[Thomas_Brown]

Good afternoon Virgilio,

Oracle advisor will at times present some interesting options to help the optimizer with execution plans. May I ask, what was the query you were executing (possibly a simple map/spatial query with a subtype layer)?

I suspect a subtype layer because of the predicate filter that would be included would be where assetgroup in (values - based on which subtype layer was enabled at the time).

We really don't want to use an index on the assetgroup attribute - the selectivity is rather low. The access path should be driven by the spatial index first, then the gdb_ct1 index for performing the analytic and finally the gdb_ct3 index for retrieving the final result form the table itself.

Note: we are about to release the next round of Enterprise geodatabase/utility network patches where we often include important performance optimizations. In this release, you will need to run the geodatabase upgrade that will modify the gdb_ct1 index adding the gdb_is_delete attribute. By doing so will reduce a step in the execution plan for obtaining the gdb_is_delete value which is an important filter in the branch version query.

Now, back to the original statement and assetgroup - I would recommend dropping that index. The latest releases of Pro when creating a utility network will not index the assetgroup and assettype attributes (as they had no selectivity for the queries the application will execute).

Thanks.
Thomas Brown Esri