For high performance spatial analysis, Oracle Spatial provides a general-purpose R-tree Spatial Index for any geometry type, and also a Composite B-tree Spatial Index (CBTREE index) solely for point data. These indexes are used in query execution to filter rows according to spatial predicates. CBTREE is recommended for indexing points as it is highly optimized for point geometries. The CBTREE index has been enhanced to allow inclusion of additional scalar attributes, including date and timestamp. Prior to this enhancement, spatial and non-spatial criteria would be independently evaluated over the entire dataset, with results merged to provide the result. For example, in a spatiotemporal query the spatial search would be performed over all time, and a temporal search would be performed across all space, and finally the results would be merged. With the enhanced CBTREE index, a single integrated spatial and temporal search is performed. The effect is significant performance gains.
As an example, we loaded Citibike NYC trip data* for the year 2023, which has 35M rows of point data, including trip start and end locations and timestamps. We searched for rides that started near Broadway south of Central Park on June 1 between 7-11am, a four hour period. For comparison purposes, we demonstrated solving the requirement with two approaches. First the legacy approach with two separate indexes, a solely spatial CBTREE index on start_geom (trip start location) and a separate ordinary B-Tree index on started_at (trip start timestamp). The second approach utilized a single enhanced spatiotemporal CBTREE index.
To begin, we created separate spatial and temporal indexes. The spatial index PARAMETERS specified below are a best practice for point data and are explained in the Spatial Developer’s Guide here. The PARALLEL parameter helps speed up index creation by spreading the workload across processors.
-- Create separate spatial and temporal indexes
CREATE INDEX citibike_tripdata_sidx ON citibike_tripdata(geom_start)
INDEXTYPE IS mdsys.spatial_index_v2
PARAMETERS('cbtree_index=true layer_gtype=point sdo_point_field_only=true') PARALLEL 8;
CREATE INDEX citibike_tripdata_tidx ON citibike_tripdata(started_at) PARALLEL 8;We then applied the best practice of updating table statistics.
-- Update table stats
EXEC dbms_stats.gather_table_stats('SANDBOX', 'CITIBIKE_TRIPDATA', degree => 8);When running queries to compare performance with different index types, we specified the query hint NO_RESULT_CACHE to ensure that results were freshly generated with every call, and not fetched from a previous result cache. This was only for performance testing purposes, and in a production environment result cache is typically beneficial. We performed this test in an Autonomous AI Database instance where, by default, query hints are ignored. Therefore, we altered our session to use (i.e., not ignore) hints. In addition, to isolate the performance impact of the different index types, we disabled parallel query.
-- Disable ignoring of hints
ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=FALSE;
ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;
-- Disable parallel query
ALTER SESSION DISABLE PARALLEL QUERY;We then ran the query for rides that started in the area of interest over a four hour period. Note that in this legacy approach, the CBTREE index and sdo_within_distance operator filter solely on the spatial criteria, and rides returned span the entire year. The time criteria is evaluated separately, over the entire spatial extent. The legacy approach then applies a join to reduce the spatial result to the desired four hour period.
-- Count rides
-- In a production environment remove the NO_RESULT_CACHE hint
SELECT /*+ NO_RESULT_CACHE */ count(*)
FROM citibike_tripdata
WHERE sdo_within_distance(
geom_start,
sdo_geometry(2002, 4326, NULL, sdo_elem_info_array(1, 2, 1),
sdo_ordinate_array(-73.9958982, 40.7265811, -73.9915495, 40.7317284, -73.990804, 40.7348668,
-73.9900999, 40.7351492, -73.9891473, 40.7365614, -73.9903484, 40.7370949,
-73.9888988, 40.7424296, -73.9885675, 40.7428062, -73.9891887, 40.7430886,
-73.9882361, 40.7484542, -73.9886917, 40.7486425, -73.9879462, 40.7494583,
-73.9873664, 40.7529723, -73.9864138, 40.7558273, -73.9869108, 40.7560783,
-73.9846743, 40.7592156, -73.9850884, 40.7594665, -73.9824378, 40.7642348,
-73.9816094, 40.7675912)),
'distance=200, unit=M')='TRUE'
AND started_at BETWEEN to_timestamp('2023-06-01 07:00:00.000','YYYY-MM-DD HH24:MI:SS.FF3')
AND to_timestamp('2023-06-01 11:00:00.000','YYYY-MM-DD HH24:MI:SS.FF3');
------------------------------------------------------------------------------------------------
COUNT(*)
1764
Elapsed: 4.5s
Over several runs, average query time was 4.5s. The execution plan verifies that the separate spatial and temporal indexes were used, and the predicate information shows two independent evaluations for the temporal and spatial criteria.
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 194 | 46702 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 194 | | |
| 2 | BITMAP CONVERSION COUNT | | 350 | 67900 | 46702 (1)| 00:00:02 |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | CITIBIKE_TRIPDATA_TIDX | 26281 | | 92 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 8 | SORT ORDER BY | | | | | |
|* 9 | DOMAIN INDEX (SEL: 1.331510 %)| CITIBIKE_TRIPDATA_SIDX | 26281 | | 46606 (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("STARTED_AT">=TIMESTAMP' 2023-06-01 07:00:00.000000000' AND "STARTED_AT"<=TIMESTAMP'
2023-06-01 11:00:00.000000000')
9 - access("MDSYS"."SDO_WITHIN_DISTANCE"("GEOM_START","MDSYS"."SDO_GEOMETRY"(2002,4326,NULL,"MDSYS"
."SDO_ELEM_INFO_ARRAY"(1,2,1),"MDSYS"."SDO_ORDINATE_ARRAY"((-73.9958982),40.7265811,(-73.9915495),40.7
317284,(-73.990804),40.7348668,(-73.9900999),40.7351492,(-73.9891473),40.7365614,(-73.9903484),40.7370
949,(-73.9888988),40.7424296,(-73.9885675),40.7428062,(-73.9891887),40.7430886,(-73.9882361),40.748454
2,(-73.9886917),40.7486425,(-73.9879462),40.7494583,(-73.9873664),40.7529723,(-73.9864138),40.7558273,
(-73.9869108),40.7560783,(-73.9846743),40.7592156,(-73.9850884),40.7594665,(-73.9824378),40.7642348,(-
73.9816094),40.7675912)),'distance=200, unit=M')=TRUE)
We then applied the enhanced CBTREE index approach. First, we dropped the indexes associated with the legacy approach, and then created a single spatiotemporal CBTREE index. In creating the new CBTREE index, observe the FILTER BY parameter which specifies the non-spatial (temporal) column to include in addition to the geometry.
-- Create a single spatiotemporal index
CREATE INDEX citibike_tripdata_stidx ON citibike_tripdata(geom_start)
INDEXTYPE IS mdsys.spatial_index_v2
FILTER BY started_at
PARAMETERS('cbtree_index=true layer_gtype=point sdo_point_field_only=true') PARALLEL 8;After updating table statistics, we reran the query to compare performance. Observe also the `DOMAIN_INDEX_FILTER` hint which instructs the optimizer to use the enhanced CBTREE index. It is recommended to always use this hint with the enhanced CBTREE.
-- Count rides
-- In a production environment remove the NO_RESULT_CACHE hint
SELECT /*+ NO_RESULT_CACHE DOMAIN_INDEX_FILTER(citibike_tripdata citibike_tripdata_stidx) */ count(*)
FROM citibike_tripdata
WHERE sdo_within_distance(
geom_start,
sdo_geometry(2002, 4326, NULL, sdo_elem_info_array(1, 2, 1),
sdo_ordinate_array(-73.9958982, 40.7265811, -73.9915495, 40.7317284, -73.990804, 40.7348668,
-73.9900999, 40.7351492, -73.9891473, 40.7365614, -73.9903484, 40.7370949,
-73.9888988, 40.7424296, -73.9885675, 40.7428062, -73.9891887, 40.7430886,
-73.9882361, 40.7484542, -73.9886917, 40.7486425, -73.9879462, 40.7494583,
-73.9873664, 40.7529723, -73.9864138, 40.7558273, -73.9869108, 40.7560783,
-73.9846743, 40.7592156, -73.9850884, 40.7594665, -73.9824378, 40.7642348,
-73.9816094, 40.7675912)),
'distance=200, unit=M')='TRUE'
AND started_at BETWEEN to_timestamp('2023-06-01 07:00:00.000','YYYY-MM-DD HH24:MI:SS.FF3')
AND to_timestamp('2023-06-01 11:00:00.000','YYYY-MM-DD HH24:MI:SS.FF3');
------------------------------------------------------------------------------------------------
COUNT(*)
1764
Elapsed: 0.7sOver several runs, average query time was 0.7s. The execution plan verifies that the combined spatiotemporal index was used, and the predicate information shows one integrated evaluation of the temporal and spatial criteria.
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 194 | 27222 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 194 | | |
| 2 | SORT CLUSTER BY ROWID | | 22684 | | 10075 (0)| 00:00:01 |
|* 3 | DOMAIN INDEX (SEL: 0.287838 %)| CITIBIKE_TRIPDATA_STIDX | 22684 | | 10075 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("MDSYS"."SDO_WITHIN_DISTANCE"("GEOM_START","MDSYS"."SDO_GEOMETRY"(2002,4326,NULL,"SDO_
ELEM_INFO_ARRAY"(1,2,1),"SDO_ORDINATE_ARRAY"((-73.9958982),40.7265811,(-73.9915495),40.7317284,(-73.
990804),40.7348668,(-73.9900999),40.7351492,(-73.9891473),40.7365614,(-73.9903484),40.7370949,(-73.9
888988),40.7424296,(-73.9885675),40.7428062,(-73.9891887),40.7430886,(-73.9882361),40.7484542,(-73.9
886917),40.7486425,(-73.9879462),40.7494583,(-73.9873664),40.7529723,(-73.9864138),40.7558273,(-73.9
869108),40.7560783,(-73.9846743),40.7592156,(-73.9850884),40.7594665,(-73.9824378),40.7642348,(-73.9
816094),40.7675912)),'distance=200, unit=M')=TRUE)
filter("STARTED_AT"<=TIMESTAMP' 2023-06-01 11:00:00.000000000' AND "STARTED_AT">=TIMESTAMP'
2023-06-01 07:00:00.000000000')Using the spatiotemporal CBTREE index improved query response time from 4.5s to 0.7s, or an improvement of over 6X. Performance improvement will vary based on data and query patterns, but 4-10X is typical. We invite you to explore the enhanced CBTREE with both temporal and other non-spatial fields, and enjoy the performance boost in your analyses.
For more information, please see Creating a Composite B-tree Spatial Index on Points in the Oracle AI Database Spatial Developer’s Guide.
* Attribution: Citi Bike System Data, provided by Lyft Bikes and Scooters, LLC. Use of this data does not imply affiliation with, approval by, endorsement by, or sponsorship from Lyft Bikes and Scooters, LLC, Citi Bike, or Citigroup Inc.