ArcSDE vs. Oracle Spatial 8

I. 空间索引

对于空间数据中几何对象的索引显然不能套用数据库现有的索引类型，其不确定的数据结构和数据操作方法使现有的索引并不能适用，因此无论是Oracle Spatial还是ArcSDE都是采用域索引（Domain Index[1]
）来实现，简单地说就是创建一个数据库中原来没有的新的索引类型。

· Oracle Spatial

1. 索引类型SPATIAL_INDEX

Oracle Spatial中的空间索引极其重要，没建空间索引的空间表就像断了腿的兔子，不但跑不起来，甚至可能比乌龟还慢。

让我们从索引的创建入手来看一下Oracle Spatial的空间索引机制：

SQL> create index idx_test_index_geom on spatial.test_index(geom) indextype is mdsys.spatial_index;

索引已创建。

如果你的数据是用shp2sdo等工具导入的，那么直接就可以创建索引；如果是自己创建的空间表，那么必然会遇到ORA-13203错误，因为Oracle Spatial在创建空间索引时会读取USER_SDO_GEOM_METADATA视图中相关的信息，如果没有就会报错，因此，对于自己创建的空间表，又需要创建索引的话（这简直是一句废话，没索引的残废空间表有什么用），就一定要在这个视图插入这个空间表的相关信息：

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)));

这里我们告诉Oracle Spatial，我的这个空间表名字叫“TEST_INDEX”，几何字段名字叫“GEOM”，X、Y方向上的坐标范围都是1到100000，容差是1。顺便看一下SDO_DIM_ELEMENT的定义，其中几个属性的含义在下面写了点注释：

CREATE OR REPLACE

TYPE SDO_DIM_ELEMENT AS OBJECT (

SDO_DIMNAME VARCHAR(64), –坐标维的名称

SDO_LB NUMBER, –坐标下界

SDO_UB NUMBER, –坐标上界

SDO_TOLERANCE NUMBER ) –坐标容差

好了，再把视线转回到空间索引上，上面我们在TEST_INDEX表的GEOM字段上创建了基于R树[2]
空间索引IDX_TEST_INDEX_GEOM，这个空间索引的索引类型是MDSYS.SPATIAL_INDEX，这个肯定比较关键，下面我们看一下这个索引类型的定义：

CREATE OR REPLACE INDEXTYPE “MDSYS”.”SPATIAL_INDEX” FOR

“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, NUMBER, NUMBER),

“MDSYS”.”SDO_INT_RELATE” (“MDSYS”.”SDO_GEOMETRY”, “MDSYS”.”SDO_GEOMETRY”, ROWID, VARCHAR2, VARCHAR2, VARCHAR2, NUMBER, NUMBER, 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

是不是翻页翻得有点不耐烦？之所以把这个空间索引所有的内容贴上来，是想看看在Oracle Spatial中到底哪些操作支持空间索引，这里发现一个很关键的问题是：为什么没有OGC的操作？如果在Oracle Spatial中使用一个OGC_INTERSECTS操作会有什么后果？下面就此进行了一个测试：

首先新建一个几何字段为MDSYS.ST_GEOMETRY类型的空间表名为“TEST_INDEX_ST”，插入10万条记录，并创建空间索引：

SQL> desc test_index_st

名称 是否为空? 类型

—————————————————————– ——– ——————————————–

GEOM MDSYS.ST_GEOMETRY

SQL> select INDEX_NAME from user_indexes where table_name=’TEST_INDEX_ST’;

INDEX_NAME

——————————————————————————————

IDX_TEST_INDEX_ST_GEOM

SYS_IL0000079131C00009$$

SYS_IL0000079131C00008$$

如果使用上面SPATIAL_INDEX中包含的操作如SDO_ANYINTERACT，设定空间过滤条件取其中1条记录，通过执行计划可以发现，查询会走空间索引：

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,1,1),SDO_ORDINATE_ARRAY(10000,10000))))=’TRUE’;

GEOM(GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES))

————————————————————————————————————————

ST_GEOMETRY(SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(10000, 10000)))

已用时间: 00: 00: 00.01

执行计划

———————————————————-

Plan hash value: 2758081403

——————————————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

——————————————————————————————————

| 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 | | | | |

——————————————————————————————————

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,1,1),”SDO_ORDINATE_ARRAY”(10000,10000))))=’TRUE’)

但是如果是OGC_INTERSECTS操作，同样的过滤条件，取其中的一条记录，你会发现Oracle会进行全表扫描：

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,1,1),SDO_ORDINATE_ARRAY(10000,10000))))=1;

GEOM(GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES))

————————————————————————————————————————

ST_GEOMETRY(SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(10000, 10000)))

已用时间: 00: 04: 01.04

执行计划

———————————————————-

Plan hash value: 1104487544

———————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

———————————————————————————–

| 0 | SELECT STATEMENT | | 1104 | 4270K| 439 (3)| 00:00:06 |

|* 1 | TABLE ACCESS FULL| TEST_INDEX_ST | 1104 | 4270K| 439 (3)| 00:00:06 |

———————————————————————————–

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,1,1),”SDO_ORDINATE_ARRAY”(10

000,10000))))=1)

对于OGC来说，这简直就是一个杯具。可见，有了空间索引的Oracle Spatial虽然不是断腿的兔子，但是在OGC操作的场景下，就像把Oracle Spatial这只兔子扔到水里和乌龟比游泳——而且，或许别人还不是乌龟，有可能是忍者神龟呢？

---

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

[2]
关于不同算法的空间索引见后续章节

转载自：https://blog.csdn.net/warrenwyf/article/details/5703061