Work with a partitioned st_geometry table in Oracle with ArcGIS

Summary

Working with a partitioned table in Oracle with a st_geometry attribute and spatial index requires going beyond the steps of just creating the partitioned table and local spatial indexes in the database. To provide the Oracle optimizer the ability to leverage the partitioning schema and each partition's local index, the application working with the data must set the partition key as a predicate filter in the SQL statement. By doing so, the optimizer can perform partition pruning and only access the relevant partitions based on the specified predicate filters. When only accessing the relevant partitions during the query, performance can be improved by performing less I/O operations.

Instructions provided describe how to use Oracle partitioning by demonstrating how to create a partitioned table, create local spatial indexes for each partition, create a system context and view, and how to set the context predicate values to leverage the table's partitions, by way of the context and view, in ArcGIS.

Procedure

1. Create an ArcSDE dbtune keyword that is used to define the feature classes partitioning schema. The keyword is then selected when creating the feature class in ArcCatalog.
   
   First, export the dbtune using the ArcSDE administration command 'sdedbtune'.
   
   

   Code:
   D:\>sdedbtune -o export -f dbtune.txt -u sde -p sde -i 5151
   
   ArcSDE 9.2 for Oracle10g Build 508 Thu Apr 17 12:23:18  2008
   Attribute        Administration Utility
   -----------------------------------------------------
   
           Successfully exported to file "dbtune.txt"
           from server ALEX

   
   Next, using a text editor, add the new keyword (the name of the keyword is PARTITION) to the dbutne.txt file. The b_storage attribute specifies the partition key, location_date, and each partition to create (jan, feb, mar, apr, may). The storage string ENABLE ROW MOVEMENT specifies when the partition key is updated; the row may be moved to the partition, which represents the new value for the row.
   
   

   Code:
   ##PARTITION
   B_STORAGE                  "PARTITION BY RANGE (location_date) 
                               (PARTITION jan VALUES LESS THAN ( to_date('02-01-2008','MM-DD-YYYY') )
                                STORAGE (INITIAL 128K) TABLESPACE january,
                                PARTITION feb VALUES LESS THAN ( to_date('03-01-2008','MM-DD-YYYY') )
                                STORAGE (INITIAL 128K) TABLESPACE february,
                                PARTITION mar VALUES LESS THAN ( to_date('04-01-2008','MM-DD-YYYY') )
                                STORAGE (INITIAL 128K) TABLESPACE march,
                                PARTITION apr VALUES LESS THAN ( to_date('05-01-2008','MM-DD-YYYY') )
                                STORAGE (INITIAL 128K) TABLESPACE april,
                                PARTITION may VALUES LESS THAN ( to_date('06-01-2008','MM-DD-YYYY') )
                                STORAGE (INITIAL 128K) TABLESPACE may)
                              ENABLE ROW MOVEMENT"
   GEOMETRY_STORAGE           "ST_GEOMETRY"
   UI_TEXT                    "User Interface text description for Partition"
   END

   
   Import the dbtune.txt file into the ArcSDE instance.
   
   

   Code:
   D:\>sdedbtune -o import -f dbtune.txt -u sde -p sde -i 5151 -N
   ArcSDE 9.2 for Oracle10g Build 508 Thu Apr 17 12:23:18  2008
   Attribute        Administration Utility
   -----------------------------------------------------
   
           Successfully imported from file "dbtune.txt"

   
   
   

   Note:
   Prior to creating the feature class, each tablespace specified in the dbtune keyword must exist.

2. Create the feature class in ArcCatalog. When creating the new feature class, the user must specify an attribute named location_date, which is a type of DATE (this attribute is the partition key), and select the configuration keyword PARTITION.
   
   A feature class using SQL can be created and the storage clause can be specified for each partition.
   
   

   Code:
   SQL> CREATE TABLE service_vehicles
     2  (objectid       NUMBER(38) NOT NULL,
     3   vehicle_id     NUMBER(38),
     4   location_date  DATE NOT NULL,
     5   shape          sde.st_geometry)
     6  PARTITION BY RANGE (location_date) 
     7   (PARTITION jan VALUES LESS THAN ( to_date('02-01-2008','MM-DD-YYYY') )
     8    STORAGE (INITIAL 128K) TABLESPACE january,
     9    PARTITION feb VALUES LESS THAN ( to_date('03-01-2008','MM-DD-YYYY') )
    10    STORAGE (INITIAL 128K) TABLESPACE february,
    11    PARTITION mar VALUES LESS THAN ( to_date('04-01-2008','MM-DD-YYYY') )
    12    STORAGE (INITIAL 128K) TABLESPACE march,
    13    PARTITION apr VALUES LESS THAN ( to_date('05-01-2008','MM-DD-YYYY') )
    14    STORAGE (INITIAL 128K) TABLESPACE april,
    15    PARTITION may VALUES LESS THAN ( to_date('06-01-2008','MM-DD-YYYY') )
    16    STORAGE (INITIAL 128K) TABLESPACE may)
    17  ENABLE ROW MOVEMENT;
   
   Table created.

   
   Once the feature class is created, create the spatial index in SQL*Plus using the CREATE INDEX statement.
   
   

   Code:
   SQL> CREATE INDEX shape_idx ON service_vehicles (shape)
     2  INDEXTYPE IS sde.st_spatial_index
     3  PARAMETERS('st_srid=1 st_grids=1000') 
     4  LOCAL;
   
   Index created.

   
   The index must be created with SQL for the ability to create local spatial indexes on each partition. ArcGIS 9.2 does not provide the ability to set the LOCAL clause when creating a spatial index.
   
   

   Note:
   When creating the spatial index, a valid spatial reference st_srid value must be set in the parameters clause.
   
   For the following example, rows are inserted into the table using SQL. The rows are written to the various partitions based on the value for location_date (the partition key).
   
   

   Code:
   SQL> INSERT INTO service_vehicles VALUES
     2  (1, 99, TO_DATE('01-12-2008','MM-DD-YYYY'), sde.st_geometry('point (10 10)', 1));
   
   1 row created.
   
   SQL> INSERT INTO service_vehicles VALUES
     2  (1, 99, TO_DATE('01-20-2008','MM-DD-YYYY'), sde.st_geometry('point (10 12)', 1));
   
   1 row created.
   
   SQL> INSERT INTO service_vehicles VALUES
     2  (1, 99, TO_DATE('02-03-2008','MM-DD-YYYY'), sde.st_geometry('point (8 14)', 1));
   
   1 row created.
   
   SQL> INSERT INTO service_vehicles VALUES
     2  (1, 99, TO_DATE('03-10-2008','MM-DD-YYYY'), sde.st_geometry('point (10 12)', 1));
   
   1 row created.
   
   SQL> INSERT INTO service_vehicles VALUES
     2  (1, 99, TO_DATE('04-01-2008','MM-DD-YYYY'), sde.st_geometry('point (10 12)', 1));
   
   1 row created.
   
   SQL> INSERT INTO service_vehicles VALUES
     2  (1, 99, TO_DATE('05-25-2008','MM-DD-YYYY'), sde.st_geometry('point (10 12)', 1));
   
   1 row created.
   
   SQL> INSERT INTO service_vehicles VALUES
     2  (1, 99, TO_DATE('05-25-2008','MM-DD-YYYY'), sde.st_geometry('point (10 14)', 1));
   
   1 row created.
   
   SQL> COMMIT;
   
   Commit complete.

   
   
3. This step creates an Oracle context, which is used by a view (created in the next step) for setting the location_date attribute's predicate filter. This is an important step because without the Oracle context applied to the view, ArcGIS would not be able to leverage the partitioning benefits provided by the Oracle optimizer's partition pruning capabilities.
   
   The Oracle package and context can be created in any schema (this example creates the objects in the SDE administrator schema). First, create the package definition.
   
   

   Code:
   SQL> CREATE OR REPLACE PACKAGE date_ctx_pkg
     2    AS
     3
     4    PROCEDURE set_val (ctx_name IN VARCHAR2, p_name IN VARCHAR2, p_val IN VARCHAR2);
     5
     6    FUNCTION sys_ctx_string (ctx_name IN VARCHAR2, p_name IN VARCHAR2) RETURN VARCHAR2;
     7
     8   END date_ctx_pkg;
     9  /
   
   Package created.

   
   Create the package body.
   
   

   Code:
   SQL> CREATE OR REPLACE PACKAGE BODY date_ctx_pkg
     2    IS
     3
     4    PROCEDURE set_val (ctx_name IN VARCHAR2, p_name IN VARCHAR2, p_val IN VARCHAR2)
     5      IS
     6
     7        BEGIN
     8          dbms_session.set_context (ctx_name, p_name, p_val);
     9        END;
    10
    11    FUNCTION sys_ctx_string (ctx_name IN VARCHAR2, p_name IN VARCHAR2) RETURN VARCHAR2
    12      IS
    13
    14        BEGIN
    15          RETURN 'sys_context ( ''' || ctx_name || ''', ''' || p_name || ''' )';
    16        END;
    17
    18    END date_ctx_pkg;
    19   /
   
   Package body created.

   
   Next, grant EXECUTE privileges on the package to the PUBLIC role.
   
   

   Code:
   SQL> GRANT EXECUTE ON date_ctx_pkg TO PUBLIC;
   
   Grant succeeded.

   
   The last step is to create two Oracle contexts that use the date_ctx_pkg package created previously. The first context, start_time_ctx, sets the start time and the second context, end_time_ctx, sets the end time for the predicate filters.
   
   

   Code:
   SQL> CREATE OR REPLACE CONTEXT start_time_ctx USING date_ctx_pkg;
   
   Context created.
   
   SQL> CREATE OR REPLACE CONTEXT end_time_ctx USING date_ctx_pkg;
   
   Context created.

   
   
4. Create the ArcSDE view, which is used to access the service_vehicles table with two predicate filters on the location_date attribute. The first filter sets the minimum location_date and the second filter defines the maximum location_date.
   
   If the service_vehicles table was created using SQL and not through ArcCatalog, the table must first be registered with ArcSDE using the 'sdelayer -o register' command. This step is not required if the feature class is created with ArcCatalog because the layer is automatically registered when created.
   
   

   Code:
   D:\>sdelayer -o register -l service_vehicles,shape -e p -R 1 -C objectid,sde -t
   st_geometry -u tomb -p tomb -i 5151
   
   ArcSDE 9.2 for Oracle10g Build 508 Thu Apr 17 12:23:18  2008
   Layer    Administration Utility
   -----------------------------------------------------
   Successfully Created Layer.

   
   Once the table is registered with ArcSDE, a view can be created using the ArcSDE command 'sdetable -o create_view'.
   
   

   Code:
   D:\>sdetable -o create_view -T service_vehicle_view -t service_vehicles 
   -c objectid,vehicle_id,location_date,shape
   -w "location_date >= to_date(sys_context ('start_time_ctx', 'ID'),'MM-DD-YYYY')
   AND location_date <= to_date(sys_context ('end_time_ctx', 'ID'),'MM-DD-YYYY')"
   -u tomb -p tomb -i 5151
   
   ArcSDE 9.2 for Oracle10g Build 508 Thu Apr 17 12:23:18  2008
   Attribute        Administration Utility
   -----------------------------------------------------
   Successfully created view service_vehicle_view.

   
   The view definition includes the argument (-w), which sets a WHERE clause in the view's definition. In this case, the WHERE clause contains two predicate filters for the location_date that are used to set the context value for the location_date attribute value.
   
   

   Note:
   When executing an ArcSDE administration command, there are no carriage returns between arguments. The carriage returns are added in this example for ease of reading.
   
   Optionally, a view can be created with SQL containing the same WHERE clause definition.
   
   

   Code:
   SQL> CREATE OR REPLACE VIEW ser_veh_view AS 
     2   SELECT * 
     3   FROM service_vehicles
     4   WHERE location_date >= TO_DATE(sys_context('start_time_ctx', 'ID'),'MM-DD-YYYY')
     5   AND location_date <= TO_DATE(sys_context ('end_time_ctx', 'ID'),'MM-DD-YYYY');
   
   View created.

   
   Executing a query against the view returns no rows because the values for the two contexts, start_time_ctx and end_time_ctx, are currently set to NULL.
   
   

   Code:
   SQL> SELECT objectid FROM ser_veh_view;
   
   no rows selected

   
   The next step describes how to set the values for the context.
5. The last step is to set the context values for the start_time_ctx and end_time_ctx predicates.
   
   Using the ser_veh_view view created in step 4, it is possible to test setting the context values with SQL in SQL*Plus. In this example, the start_time_ctx context value is set to January 1, 2008 and the end_time_ctx context value to January 31, 2008.
   
   

   Code:
   SQL> EXECUTE sde.date_ctx_pkg.set_val('start_time_ctx','id','01-01-2008');
   
   PL/SQL procedure successfully completed.
   
   SQL> EXECUTE sde.date_ctx_pkg.set_val('end_time_ctx','id','01-31-2008');
   
   PL/SQL procedure successfully completed.

   
   Now that the context values are defined, it is possible to execute a SELECT statement against the ser_veh_view to return the rows that contain a location_date greater than January 1, 2008 and a location_date less than January 31, 2008.
   
   

   Code:
   SQL> SELECT objectid FROM ser_veh_view;
   
     OBJECTID
   ----------
            1
            2

   
   In SQL*Plus, it is also possible to view what values a context has been set to by executing the following query:
   
   

   Code:
   SQL> SELECT sys_context('start_time_ctx','id') start_time FROM dual;
   
   START_TIME
   ----------
   01-01-2008

   
   Finally, when using ArcGIS and working with the registered service_vehicles_view (the layer added to ArcMap), to set the context value, the user must build their own command and execute SQL to set the start_time_ctx and end_time_ctx values. For example, the IWorkspace::ExecuteSQL method can be executed and the following string can be passed:
   
   

   Code:
   BEGIN sde.date_ctx_pkg.set_val('start_time_ctx','id','01-01-2008'); END;

   
   and
   
   

   Code:
   BEGIN sde.date_ctx_pkg.set_val('end_time_ctx','id','01-31-2008'); END;

   
   When spatial or non-spatial queries are executed, the only two candidate rows in the view are those that are between the start_time_ctx value and end_time_ctx value.