Community
Select to view content in your preferred language

doubts query network utilities

86
1
Thursday
VirgilioPalmi
by Esri Contributor
Esri Contributor
Thursday

I was analyzing this query,
SELECT  ASSETGROUP,  OBJECTID,  1 SHAPE, WATERLINE.SHAPE.points,WATERLINE.SHAPE.numpts,WATERLINE.SHAPE.entity,WATERLINE.SHAPE.minx,WATERLINE.SHAPE.miny,WATERLINE.SHAPE.maxx,WATERLINE.SHAPE.maxy,WATERLINE.rowid as rowid$    FROM WATER.WaterLine WATERLINE  ​​WHERE WATERLINE.rowid IN  (SELECT MB_.RID$ FROM  (SELECT /*+ LEADING(A) USE_NL(B) OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */ B.ROWID AS RID$,B.SHAPE,ROW_NUMBER() OVER(PARTITION BY B.OBJECTID ORDER BY gdb_from_date DESC) rn, gdb_is_delete FROM (SELECT /*+ LEADING(WATER.WATERLINE) */ DISTINCT OBJECTID FROM WATER.WATERLINE  ​​WHERE SDE.ST_EnvIntersects(WATER.WATERLINE.SHAPE, :1, :2, :3, :4) = 1   ) A, WATER.WATERLINE B WHERE A.OBJECTID = B.OBJECTID AND ((gdb_branch_id = 0  AND gdb_from_date <= :specific_moment )  )) MB_ WHERE rn = 1 AND gdb_is_delete = 0 AND SDE.ST_EnvIntersects(MB_.SHAPE, :1, :2, :3, :4) = 1)   ORDER BY OBJECTID ASC

and I found two critical issues that I didn't quite understand.
1. gdb_from_date <= :specific_moment Why is this being asked? The database is severely affected in resolution for almost 20 hours (I had to forcefully abort the query)

2. /*+ LEADING(A) USE_NL(B) OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
These hints appear to date back to ArcGIS 10.x.
In Oracle 19c/21c, they often degrade the execution plan, especially with joins on large tables.

 

The database is Oracle 21C.

 

Thanks
Virgilio

0 Kudos
1 Reply
RobertKrisher
by Esri Regular Contributor
Esri Regular Contributor
Thursday

If you're experiencing slow draw times, please submit a case with support. That query indicates that you're attempting to draw all the features in the water lines layer for the current moment, which should perform well under reasonable circumstances.

You can find a presentation explaining a generic form of the query to answer your first question here: Version Management with ArcGIS - Esri Videos: GIS, Events, ArcGIS Products & Industries. The short answer is that moment is used to ensure that you're looking at the data as it existing for the current moment that the client is using for viewing the data.

0 Kudos