(edited)
The SDE.GDB_ITEMS_VW view in Oracle GDBs is relatively slow due to the calculated columns:
- sde.sdexmltotext(d1.xml_doc) AS definition,
- sde.sdexmltotext(d2.xml_doc) AS documentation,
- sde.sdexmltotext(d3.xml_doc) AS iteminfo,
Could Esri consider precomputing the calculated columns in that view — especially the DEFINITION field? I imagine there would be a few different ways it could be done:
- Materialized view
- Function-based index
- Trigger to populate a field in a table
- Other Oracle mechanisms?
We'd do it ourselves in our GDB, but we prefer not to modify the objects in the SDE owner...for multiple reasons, such as: it adds complexity to GDB upgrades.
I would think the materialized view option would be especially appealing to Esri. It would be fairly easy to convert the existing view to a materialized view. And the change wouldn't compromise any existing dependencies, since the view's name would stay the same. Esri could use the fast refresh/materialized view logs option...which would mean the materialized view would update in real time (which I imagine is necessary).
Similarly, function-based indexes would be a low-impact, easy, real-time option too.
