ArcSDE vs. Oracle Spatial 8

I. Spatial Index

The existing index types of the database cannot be applied to the indexes of geometric objects in spatial data. The uncertain data structure and data operation methods make the existing indexes unsuitable, therefore, both Oracle Spatial and ArcSDE use domain indexes (domain index [1]
In short, it is to create a new index type that is not in the database.

· Oracle Spatial


1. Index type spatial_index

Spatial indexes in Oracle Spatial are extremely important. A Spatial table without spatial indexes is like a rabbit with broken legs. It may not only fail to run, but may even be slower than turtles.

Let's take a look at the spatial index mechanism of Oracle Spatial from the perspective of index creation:

SQL> Create index idx_test_index_geom on Spatial. test_index (Geom) indextype is MDSYS. spatial_index;

The index has been created.

If your data is imported using tools such as shp2sdo, then you can directly create an index; if you create a space table, it will inevitably encounter a ORA-13203 error, because Oracle Spatial reads the relevant information in the user_sdo_geom_metadata view when creating a spatial index, if not, an error is reported. Therefore, for a space table created by itself, if you need to create an index again (this is a nonsense, but there is no index in the disability space table), you must insert the related information of the spatial table in this view:

SQL> insert into MDSYS. user_sdo_geom_metadata (table_name, column_name, diminfo) values ('test _ Index', 'geom ',

Sdo_dim_array (

Sdo_dim_element ('x', 1,100000, 1 ),

Sdo_dim_element ('y', 1,100000, 1 )));

Here we will tell Oracle Spatial that the name of my spatial table is "test_index" and the geometric field name is "Geom". The coordinates in the X and Y directions are from 1 to 100000, the tolerance is 1. By the way, let's take a look at the definition of sdo_dim_element. The meanings of several attributes are annotated below:

Create or replace

Type sdo_dim_element as object (

Sdo_dimname varchar (64), -- Coordinate Dimension name

Sdo_lb number, -- lower bound Coordinate

Sdo_ub number, -- upper bound of coordinates

Sdo_tolerance number) -- coordinate tolerances

Now, we can turn the line of sight back to the spatial index. We created the r tree based on the Geom field in the test_index table [2].
Idx_test_index_geom of the spatial index. The index type of this spatial index is MDSYS. spatial_index. This must be critical. Let's take a look at the definition of this index type:

Create or replace indextype "MDSYS". "spatial_index"

"MDSYS". "locator_within_distance" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_anyinteract" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry") rewrite join,

"MDSYS". "sdo_anyinteract" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_anyinteract" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_anyinteract" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_anyinteract" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_anyinteract" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_anyinteract" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_contains" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_contains" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_contains" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_contains" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_contains" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_contains" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_contains" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_coveredby" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_coveredby" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_coveredby" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_coveredby" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_coveredby" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_coveredby" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_coveredby" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_covers" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_covers" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_covers" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_covers" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_covers" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_covers" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_covers" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_equal" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_equal" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_equal" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_equal" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_equal" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_equal" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_equal" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_filter" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2) rewrite join,

"MDSYS". "sdo_filter" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_filter" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_filter" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_filter" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_filter" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_filter" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_filter" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry") rewrite join,

"MDSYS". "sdo_inside" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_inside" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_inside" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_inside" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_inside" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_inside" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_inside" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_int2_filter" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_int2_relate" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_int_filter" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", rowid, varchar2, varchar2, varchar2, number ),

"MDSYS". "sdo_int_relate" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", rowid, varchar2, varchar2, varchar2, number ),

"MDSYS". "sdo_nn" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_nn" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_nn" ("MDSYS". "st_geometry", "MDSYS". "st_geometry", varchar2 ),

"MDSYS". "sdo_nn" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry", varchar2 ),

"MDSYS". "sdo_nn" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_nn" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_nn" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_nn" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_on" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_on" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_on" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_on" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_on" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_on" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_on" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlapbdydisjoint" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlapbdydisjoint" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_overlapbdydisjoint" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlapbdydisjoint" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_overlapbdydisjoint" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_overlapbdydisjoint" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_overlapbdydisjoint" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlapbdyintersect" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlapbdyintersect" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_overlapbdyintersect" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlapbdyintersect" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_overlapbdyintersect" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_overlapbdyintersect" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_overlapbdyintersect" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlaps" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlaps" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_overlaps" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_overlaps" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_overlaps" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_overlaps" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_overlaps" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_relate" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2) rewrite join,

"MDSYS". "sdo_relate" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_relate" ("MDSYS". "st_geometry", "MDSYS". "st_geometry", varchar2 ),

"MDSYS". "sdo_relate" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry", varchar2 ),

"MDSYS". "sdo_rtree_filter" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_rtree_relate" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_touch" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_touch" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_object_array "),

"MDSYS". "sdo_touch" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_touch" ("MDSYS". "sdo_topo_geometry", "MDSYS". "sdo_topo_geometry "),

"MDSYS". "sdo_touch" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry "),

"MDSYS". "sdo_touch" ("MDSYS". "st_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_touch" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry "),

"MDSYS". "sdo_within_distance" ("MDSYS". "sdo_geometry", "MDSYS". "sdo_geometry", varchar2) rewrite join,

"MDSYS". "sdo_within_distance" ("MDSYS". "st_geometry", "MDSYS". "sdo_geometry", varchar2 ),

"MDSYS". "sdo_within_distance" ("MDSYS". "st_geometry", "MDSYS". "st_geometry", varchar2 ),

"MDSYS". "sdo_within_distance" ("MDSYS". "sdo_geometry", "MDSYS". "st_geometry", varchar2)

Using "MDSYS". "sdo_index_method_10i"

With rebuild online

With order by "MDSYS". "sdo_nn_distance" (number ),

"MDSYS". "sdo_nn_distance" (number ),

"MDSYS". "sdo_nn_distance" (number)

With local range Partition

Is it a little impatient to flip pages? The reason why I pasted all the content of this spatial index is to see which operations in Oracle Spatial support spatial index. Here I found a key question: why is there no OGC operation? What are the consequences of using an ogc_intersects operation in Oracle Spatial? The following is a test:

Create a new space table named "test_index_st" with the geometric field MDSYS. st_geometry type, insert 0.1 million records, and create a spatial index:

SQL> DESC test_index_st

Is the name empty? Type

---------------------------------------------------------------------------------------------------------------------

Geom MDSYS. st_geometry

SQL> select index_name from user_indexes where table_name = 'test _ index_st ';

Index_name

Bytes ------------------------------------------------------------------------------------------

Idx_test_index_st_geom

Sys_il0000079131c00009 $

Sys_il0000079131c00008 $

If the above spatial_index contains operations such as sdo_anyinteract, set the space filter condition to retrieve one of the records. The execution plan shows that the query uses the space index:

SQL> set autot on

SQL> select * From test_index_st where sdo_anyinteract (Geom, st_geometry (sdo_geometry (2001, null, null, sdo_elem_info_array (, 1), Week () = 'true ';

Geom (sdo_gtype, sdo_srid, sdo_point (x, y, z), sdo_elem_info, sdo_ordinates ))

Bytes ------------------------------------------------------------------------------------------------------------------------

St_geometry (sdo_geometry (2001, null, null, sdo_elem_info_array (1, 1, 1), sdo_ordinate_array (10000,100 00 )))

Used time: 00: 00: 00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 2758081403

Bytes ------------------------------------------------------------------------------------------------------

| ID | operation | Name | rows | bytes | cost (% CPU) | time |

Bytes ------------------------------------------------------------------------------------------------------

| 0 | SELECT statement | 1104 | 4283k | 184 (0) | 00:00:03 |

| 1 | table access by index rowid | test_index_st | 1104 | 4283k | 184 (0) | 00:00:03 |

| * 2 | Domain index | idx_test_index_st_geom |

Bytes ------------------------------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):

---------------------------------------------------

2-access ("MDSYS". "sdo_anyinteract" ("Geom", "st_geometry" ("MDSYS". "sdo_geometry" (2001, null,

Null, "sdo_elem_info_array" (, 1), "sdo_ordinate_array" () = 'true ')

However, for the ogc_intersects operation, take one of the records based on the same filter condition, and you will find that Oracle will perform a full table scan:

SQL> set autot on

SQL> select * From test_index_st where MDSYS. ogc_intersects (Geom, st_geometry (sdo_geometry (2001, null, null, sdo_elem_info_array (, 1), Week () = 1;

Geom (sdo_gtype, sdo_srid, sdo_point (x, y, z), sdo_elem_info, sdo_ordinates ))

Bytes ------------------------------------------------------------------------------------------------------------------------

St_geometry (sdo_geometry (2001, null, null, sdo_elem_info_array (1, 1, 1), sdo_ordinate_array (10000,100 00 )))

Used time: 00: 04: 01.04

Execution Plan

----------------------------------------------------------

Plan hash value: 1104487544

Bytes -----------------------------------------------------------------------------------

| ID | operation | Name | rows | bytes | cost (% CPU) | time |

Bytes -----------------------------------------------------------------------------------

| 0 | SELECT statement | 1104 | 4270k | 439 (3) | 00:00:06 |

| * 1 | table access full | test_index_st | 1104 | 4270k | 439 (3) | 00:00:06 |

Bytes -----------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):

---------------------------------------------------

1-filter ("MDSYS". "ogc_intersects" ("Geom", "st_geometry" ("MDSYS". "sdo_ge

Ometry "(2001, null, null," sdo_elem_info_array "(, 1)," sdo_ordinate_array "(10

000,10000) = 1)

For OGC, this is simply a cup. It can be seen that Oracle Spatial with spatial indexes is not a rabbit with broken legs, but in the case of OGC operations, it is like throwing the Oracle Spatial rabbit into the water and swimming with turtles -- and, maybe others are not turtles. Maybe they are ninja turtles?

[1]
Http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_5013.htm

[2]
For spatial indexes of different algorithms, see the subsequent sections.