Branch Version Sql Views
The following queries show examples of how to create materialized views on branch versioned utility network for various DBMS. For additional information about the reasoning behind this approach, please refer to the Branch Versioning and SQL Esri blog post.
PostgreSQL
Create Materialized Views
DROP MATERIALIZED VIEW IF EXISTS unadmin.device_bvw;
CREATE MATERIALIZED VIEW unadmin.device_bvw
AS
WITH t0_ AS
(SELECT objectid, globalid, assetgroup, assettype, assetid, shape
FROM
(SELECT objectid, globalid, assetgroup, assettype, assetid, shape, Row_number() over (PARTITION BY objectid ORDER BY gdb_from_date DESC) rn_, gdb_is_delete
FROM unadmin.electricdevice
WHERE (gdb_branch_id = 0)) a
WHERE rn_ = 1
AND gdb_is_delete = 0 )
SELECT objectid, globalid, assetgroup, assettype, assetid, shape
FROM t0_
WITH DATA;
CREATE UNIQUE INDEX IF NOT EXISTS device_bvw_uuid
ON unadmin.device_bvw USING btree
(globalid COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS device_bvw_spat_idx
ON unadmin.device_bvw USING gist
(shape)
TABLESPACE pg_default;
Refresh Views
Once you’ve created the materialized view(s), you should create and schedule a function in your database to refresh them periodically. How often you call this function and refresh your views depend on the volume of edits you make and how long it takes to refresh the views. A common practice is to refresh the views once or twice a day, but for very large datasets or datasets that require a lot of additional processing you may only refresh the views once a week.
CREATE OR REPLACE FUNCTION
unadmin.refresh_bvw()
RETURNS VOID
LANGUAGE 'plpgsql'
AS $$
DECLARE
BEGIN
REFRESH MATERIALIZED VIEW unadmin.assembly_bvw;
REFRESH MATERIALIZED VIEW unadmin.device_bvw;
REFRESH MATERIALIZED VIEW unadmin.junction_bvw;
REFRESH MATERIALIZED VIEW unadmin.line_bvw;
REFRESH MATERIALIZED VIEW unadmin.structureboundary_bvw;
REFRESH MATERIALIZED VIEW unadmin.structurejunction_bvw;
REFRESH MATERIALIZED VIEW unadmin.structureline_bvw;
REFRESH MATERIALIZED VIEW unadmin.associations_bvw;
REFRESH MATERIALIZED VIEW unadmin.subnetworks_bvw;
END;$$
ALTER FUNCTION unadmin.refresh_bvw()
OWNER TO unadmin;
Oracle
Create Materialized Views
You should consult with a DBA before specifying any parameters in your create index statement, otherwise the defaults for the database are used.
If you include a geometry in your view you need to specify the spatial grid size and spatial reference id (SRID) of the base table when creating the spatial index on the shape field. In the example below I have included a statement that you can use to query this information.
GRANT CREATE MATERIALIZED VIEW TO UNADMIN;
CREATE MATERIALIZED VIEW DEVICE_BVW
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT OBJECTID, GLOBALID, ASSETGROUP, ASSETTYPE, ASSETID, SHAPE
FROM UNADMIN.ELECTRICDEVICE
WHERE GDB_ARCHIVE_OID IN
(SELECT GDB_ARCHIVE_OID
FROM
(SELECT GDB_ARCHIVE_OID, ROW_NUMBER() OVER(PARTITION BY objectid ORDER BY gdb_from_date DESC) rn, gdb_is_delete
FROM UNADMIN.ELECTRICDEVICE
WHERE (gdb_branch_id = 0)) br__
WHERE br__.rn = 1
AND br__.gdb_is_delete = 0);
CREATE UNIQUE INDEX DEVICE_BVW_UUID ON DEVICE_BVW (GLOBALID) ;
--Get the underlying grid size and SRID from the base table
SELECT A.GRID.GRID1, A.GRID.GRID2, A.GRID.GRID3,A.SRID
FROM SDE.ST_GEOMETRY_INDEX
WHERE OWNER = 'UNADMIN' AND TABLE_NAME = 'ELECTRICDEVICE';
--Create a spatial index
CREATE INDEX DEVICE_BVW_SA_IDX
ON UNADMIN.ELECTRICDEVICE (“SHAPE”)
INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_grids=<Grid Sizes Here> st_srid=<Your ID Here>');
Refresh Views
To keep your views up to date, you should create a materialized view refresh group for these views and set it to execute on a regular schedule to ensure the data is refreshed often enough to meet your reporting requirements but not so often it places a burden on your system. You can learn more about refresh groups by reading Oracle’s online help for DBMS Refresh.
Oracle 21C and later have methods for automatically refreshing materialized views. However, if you are using an older release of oracle you will want to schedule a refresh group that handles refreshing your materialized views.
BEGIN
DBMS_REFRESH.make(
name => 'UNADMIN.BVW_REFRESH',
list => '',
next_date => SYSDATE,
interval => '/*12:Hours*/ SYSDATE + 12/24',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/
BEGIN
DBMS_REFRESH.add(
name => 'UNADMIN.BVW_REFRESH',
list => 'UNADMIN.ASSEMBLY_BVW,UNADMIN.DEVICE_BVW,UNADMIN.JUNCTION_BVW,UNADMIN.STRUCTUREBOUNDARY_BVW,UNADMIN.STRUCTUREJUNCTION_BVWUNADMIN.STRUCTURELINE_BVW',
lax => TRUE);
END;
/
Sql Server
Create Reporting Table
Even though SQL Server does have an implementation for materialized views (called indexed views) it is recommended that you do not use indexed views to materialize the results of queries. You can read more about these restrictions in Microsoft’s online help. Instead, you should write a stored procedure that will recreate tables and indices for your queries.
The following statement to create a stored procedure that will create or refresh a reporting table for electric devices in the utility network for a SQL server database. Because we are doing this all in a single stored procedure, we also include the statements for creating the indices.
CREATE OR ALTER PROCEDURE [unadmin].[CreateDevice_BVW]
AS BEGIN
BEGIN TRY
BEGIN TRANSACTION
/* DECLARE PROCEDURE VARIABLES */
DECLARE @minx NUMERIC(38,8);
DECLARE @miny NUMERIC(38,8);
DECLARE @maxx NUMERIC(38,8);
DECLARE @maxy NUMERIC(38,8);
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
/* DROP THE TABLE IF ALREADY EXISTS */
DROP TABLE IF EXISTS [unadmin].[Device_BVW];
/* CREATE THE TABLE BASED FROM DEFAULT RECORDS OF THE BRANCH VERSIONED TABLE */
SELECT OBJECTID, GlobalID, Shape
INTO [unadmin].[Device_BVW]
FROM [unadmin].[Device]
WHERE GDB_ARCHIVE_OID IN (
SELECT GDB_ARCHIVE_OID
FROM (
SELECT GDB_ARCHIVE_OID, ROW_NUMBER() OVER(PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) rn, GDB_IS_DELETE
FROM [unadmin].[ Device]
WHERE (gdb_branch_id = 0)) br__
WHERE br__.rn = 1
AND br__.GDB_IS_DELETE = 0);
/* ADD INDEXES */
ALTER TABLE [unadmin].[Device_BVW]
ADD CONSTRAINT [Device_BVW_pk] PRIMARY KEY CLUSTERED ([OBJECTID] ASC);
CREATE UNIQUE NONCLUSTERED INDEX [DEVICE_BVW_UUID]
ON [unadmin].[Device_BVW] ([GLOBALID] ASC);
--You only need to do this if your resulting table needs a shape column.
--This will construct a spatial index using the extent of the original table
WITH
envelope AS (SELECT shape.STEnvelope() AS envelope FROM [unadmin].[ Device]),
corners AS (SELECT envelope.STPointN(1) AS point FROM envelope
UNION ALL SELECT envelope.STPointN(3) FROM envelope)
SELECT @minx=min(point.STX), @miny=min(point.STY), @maxx=max(point.STX), @maxy=max(point.STY) FROM corners;
/* GENERATE & EXECUTE THE SQL FOR CREATING THE SPATIAL INDEX */
DECLARE @sql NVARCHAR(2000) = 'CREATE SPATIAL INDEX [Device_BVW_spidx] ON [unadmin].[ Device_BVW] (Shape)
USING GEOMETRY_AUTO_GRID WITH (BOUNDING_BOX =('+CAST(@minx AS NVARCHAR(20))+','
+CAST(@miny AS NVARCHAR(20))+','
+CAST(@maxx AS NVARCHAR(20))+','
+CAST(@maxy AS NVARCHAR(20))+'))';
EXECUTE (@sql);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
/* IF ERROR THEN ROLLBACK TRANSACTION AND RETURN ERROR */
ROLLBACK TRANSACTION
SELECT @ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure: %s, Line: %d, ' + 'Message: '+ ERROR_MESSAGE();
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine);
END CATCH
END
/* RUN THE STORED PROCEDURE
EXECUTE gisowner.CreateTAX_BVW
*/
Refresh Views
To refresh the tables, you should execute the stored procedure(s) created on a regular schedule to ensure the data is refreshed often enough to meet your reporting requirements but not so often it places a burden on your system. Below you can find an example script.
exec UNADMIN.CreateAssembly_BVW;
exec UNADMIN.CreateDevice_BVW;
exec UNADMIN.CreateJunction_BVW;
exec UNADMIN.CreateLine_BVW;
exec UNADMIN.CreateStructureBoundary_BVW;
exec UNADMIN.CreateStructureJunction_BVW;
exec UNADMIN.CreateStructureLine_BVW;
exec UNADMIN.CreateAssociations_BVW;
exec UNADMIN.CreateSubnetworks_BVW;
SAP HANA
Create Reporting Table
Because SAP Hana doesn’t have materialized views the recommended approach is to use a stored procedure to create a column table that stores the current state of the data in default. The following stored procedure will persist the current state of the table from the default version, then create indices on the global id and object id columns.
/*CREATE STORED PROCEDURE*/
CREATE OR REPLACE PROCEDURE UNADMIN.CREATE_DEVICE_BVW
(OUT sql_code INT,
OUT sql_msg NVARCHAR(512))
LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
sql_code = ::SQL_ERROR_CODE;
sql_msg = ::SQL_ERROR_MESSAGE;
RESIGNAL;
END;
sql_code = 0;
sql_msg = null;
IF EXISTS (SELECT 1 FROM M_TABLES WHERE SCHEMA_NAME = 'UNADMIN' AND TABLE_NAME='DEVICE_BVW') THEN
DROP TABLE UNADMIN.DEVICE_BVW;
END IF;
CREATE COLUMN TABLE UNADMIN.DEVICE_BVW AS (
WITH T0_ AS
(SELECT OBJECTID, GLOBALID, ASSETGROUP, ASSETTYPE, ASSETID, SHAPE
FROM
(SELECT OBJECTID, GLOBALID, ASSETGROUP, ASSETTYPE, ASSETID, SHAPE, ROW_NUMBER() OVER (PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) RN_, GDB_IS_DELETE
FROM UNADMIN.Device
WHERE (GDB_BRANCH_ID = 0)) A
WHERE RN_ = 1
AND GDB_IS_DELETE = 0 )
SELECT OBJECTID, GLOBALID, SHAPE
FROM T0_
);
CREATE UNIQUE INDEX DEVICE_BVW_UUID ON UNADMIN.DEVICE_BVW ( GLOBALID ASC);
CREATE INDEX DEVICE_BVW_PK ON UNADMIN.DEVICE_BVW ( OBJECTID ASC );
END;
Refresh Reporting Table
To refresh the table, you just call the stored procedure associated with any tables you want to refresh.
CALL UNADMIN.CREATE_Assembly_BVW(? ,?);
CALL UNADMIN.CREATE_Device_BVW(? ,?);
CALL UNADMIN.CREATE_Junction_BVW(? ,?);
CALL UNADMIN.CREATE_Line_BVW(? ,?);
CALL UNADMIN.CREATE_StructureBundary_BVW(? ,?);
CALL UNADMIN.CREATE_StructureJunction_BVW(? ,?);
CALL UNADMIN.CREATE_StructureLine_BVW(? ,?);
CALL UNADMIN.CREATE_Associations_BVW(? ,?);
CALL UNADMIN.CREATE_Subnetwork_BVW(? ,?);
