Convert a geometric network's storage from Oracle LONG RAW to LOB

Summary

Instructions provided are for converting a geometric network's storage from Oracle LONG RAW to LOB. When working with a geometric network, the client application must retrieve the logical network's topological pages. The pages store properties of the logical network, such as connectivity, flow direction, weight values, to and from junction elements.

When the geometric network is versioned to allow the network to be edited, ArcSDE must execute versioned queries to retrieve the version's correct representation of the logical network's page. The following query is an example of one such query retrieving a logical network page for the junctions table:

SELECT OID, PAGENUMBER, PAGEBLOB, V_#.UNION_ROWID, V_#.SDE_STATE_ID FROM (SELECT b.OID, b.PAGENUMBER, b.PAGEBLOB, b.rowid union_rowid, 0 SDE_STATE_ID FROM owner.N_#_JDesc b WHERE b.OID NOT IN (SELECT /*+ HASH_AJ INDEX(D# D#_PK) */SDE_DELETES_ROW_ID FROM owner.D# WHERE DELETED_AT IN(SELECT /*+ USE_HASH (l) INDEX(l LINEAGES_PK) */l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name AND l.lineage_id <= :lineage_id)) UNION ALL SELECT /*+ INDEX(A#_PK) */ a.OID, a.PAGENUMBER, a.PAGEBLOB, a.rowid union_rowid, a.SDE_STATE_ID FROM owner.A# a, SDE.state_lineages SL WHERE (a.OID, a.SDE_STATE_ID) NOT IN(SELECT /*+ HASH_AJ INDEX(D# D#_PK)*/ SDE_DELETES_ROW_ID, SDE_STATE_ID FROM owner.D# WHERE DELETED_AT IN (SELECT /*+ USE_HASH (l) INDEX(l LINEAGES_PK) */ l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name AND l.lineage_id <= :lineage_id)) AND a.SDE_STATE_ID = SL.lineage_id AND SL.lineage_name = :lineage_name AND SL.lineage_id <= :lineage_id) V_# where (PageNumber = 230)

When Oracle executes the statement, the query must fetch the LONG RAW page from each modified state for the version's lineage. The cost of fetching the page can be an expensive step during the execution of the query. The logical network's pages are 16K in size, therefore the more times the page must be retrieved during the query, the longer the query takes to execute.

To avoid the performance overhead of fetching the logical network page multiple times within the execution plan of the statement, ESRI recommends you re-create your geometric network and set the appropriate keyword parameter ATTRIBUTE_BINARY to SDEBLOB in the dbtune.

When the logical network uses Oracle LOBs and the statement is executed, the query only fetches the last representation of the binary column, reducing the physical i/o during the operation and improving performance.

Procedure

1. To rebuild the geometric network, all existing versions that edited feature classes participating in the geometric network must be reconciled and posted to the DEFAULT version, and deleted. Perform this step only if the changes need to be preserved for each version. Then compress the geodatabase.
2. Unregister as versioned the geometric network dataset. If warned there are still outstanding edits for the dataset, verify all versions were reconciled, posted and deleted.
3. Update the ArcSDE dbtune to use LOBS as the attribute binary storage type. See the ArcSDE documentation for the exact steps for changing parameters in the dbtune.
4. Recreate the geometric network.