oracle查询注意点,Oracle_spatial的常见错误与注意事项

1、ORA-13226：没有空间索引接口将不被支持

当使用一个空间操作符时，如果没有使用空间索引导致该操作符不能被完成将会返回该错误。这可能会发生在当你使用的列上没有空间索引、或者优化器没有选择以所用为基础进行计算时。

解决办法：如果在空间操作符的列上没有索引，就创建一个索引。如果优化器没有选择空间索引，那么久显式地指定index或ordered以确保空间索引被使用。

例如：

select b.gwm_fid, b.gwm_fno, b.gwm_vno, a.gwm_tileid, b.districtid

from gs_tile_p2 a, VP_street_area b

where b.gwm_vno = 13001

and b.deletetag = 0

and b.gwm_status = 0

and sdo_relate(a.gwm_geometry, b.gwm_geometry, '' MASK = ANYINTERACT '') = '' TRUE '';

查询提示该错误，原因gs_tile_p2该表没有创建控件索引。

## 2、ORA-13203：读取USER_SDO_GEOM_METADATA视图失败

如果在USER_SDO_GEOM_METADATA视图中对没有任何元数据的表进行索引，就会返回该错误。

解决办法：在该视图中插入一条与空间层相关的记录。

比如：

CREATE INDEX INDEX_GS_TILE_P2 ON GS_TILE_P2(GWM_GEOMETRY)INDEXTYPE IS MDSYS.SPATIAL_INDEX;

提示ORA-13203错误，经查询发现没有在元数据中插入记录。

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)

VALUES ('GS_TILE_P2', 'GWM_GEOMETRY',

MDSYS.SDO_DIM_ARRAY

(MDSYS.SDO_DIM_ELEMENT('X', -5000000, -5000000, 0.000000050),

MDSYS.SDO_DIM_ELEMENT('Y', -5000000, -5000000, 0.000000050),

MDSYS.SDO_DIM_ELEMENT('Z', 0,0, 0.000000050)

),

NULL);

## 3、ORA-13365：层的SRID与几何体的SRID不符

该错误暗示着在一个表的几何体中的SRID与USER_SDO_GEOM_METADATA视图中相应的SRID值不符。

解决办法：将两者的SRID设为一致。

## 4、 ORA-13223:SDO_GEOM_METADATA

## 中的重复项

该错误表明，像USER_SDO_GEOM_METADATA视图中插入一条指定了数据对的新记录失败。表明该视图中已经含有数据对的记录。

解决办法：在向该视图插入新记录前，先删除含有数据对的记录。

## 5、ORA-13249，ORA-02289：不能撤销序列/表

当你撤销一个空间索引时可能引发该错误。如果相应的序列/表不存在，DROP INDEX语句会引发这些错误。 解决办法：在DROP INDEX语句中增加FORCE。

如：DROP INDEX customers_sidx FORCE;

## 6、ORA-13249：sdo_index_metadata表在有重复条目

当尝试创建一个空间索引，且此时还有失败的DROP INDEX语句残留元数据时，该错误将会引发。

解决办法：显式地清除MDSYS模式中SDO_INDEX_METADATA表内指定索引的元数据。

DELETE FROM SDO_INDEX_METADATA WHERE sdo_index_owner = ‘SPATIAL’ AND sdo_index_name = ‘CUSTOMERS_SIDX’;

## 7、ORA-13207：不正确地使用操作符

当指定的操作符使用 不正确时可能引发该错误。绝大多数情况下，当SDO_RELATE、SDO_NN或SDO_WITHIN_DISTANCE操作符被应用到三维或四维空间索引时(通过在CREATE INDEX语句中的字句中指定SDO_INDEX_DIMS参数创建索引)，可能引发该错误。

解决办法：在索引创建过程中，如果SDO_INDEX_DIMS参数被设置为大于2(默认操作符>值)时，可以使用的操作符只有SDO_FILTER。

## 8、ORA-13000：维数超出范围

当操作含有SDO_GTYPE值(在SDO_GEOMETRY对象中)小于10的几何体时，将会导致该错误。也许是因为早期的Oracle spatial版本中的SDO_GTYPE仅仅包含类型(T)信息。从Oracle9i开始，SDO_GEOMETRY中的SDO_GTYPE的格式为DOOT，其中D表明维度而T是类型信息。

解决办法：修改数据使其符合这一变化。另一种方法是使用SDO_MIGRATE.TO_CURRENT函数让Oracle spatial改变数据。

## 9、ORA-00904：…无效的标识符

select gwm_geometry.sdo_srid from p_street_area进行查询时就会提示该错误

这是因为引用对象的属性时没有为表指定一个别名。如下操作就可以正常查询：

select a.gwm_geometry.sdo_srid from p_street_area a;

## 10、ORA-00939：函数参数过多

当SDO_ORDINATES数组中插入一个超过1000个坐标的SDO_GEOMETRY时，可能引发该错误。

例如：

INSERT INTO sales_regions

VALUES

(1000,

sdo_geometry(2004,

8307,

NULL,

sdo_elem_info_array(1, 1, 1100),--1100个点

sdo_ordinate_array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1.. . --重复99次

1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1

)));

提示too many arguments for function

解决办法：这是一个sql级别的限制。可以通过创建一个保存该几何体的pl/sql变量来避免这一错误，之后将该变量绑定到insert sql语句中：

DECLARE

geom sdo_geometry;

BEGIN

geom:=

sdo_geometry

(2004,

8307,

NULL,

sdo_elem_info_array(1, 1, 1100),--1100个点

sdo_ordinate_array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1.. . --重复99次

1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1

)

);

execute immediate

'INSERT INTO sales_regions values (1000,:gm)' using geom;

END;

## 11、ORA-13030：sdo_geometry的维数无效或者ORA-13364：层维数与几何体维数不符

空间索引、元数据中的diminfo，数据表中sdo_geometry中的GTYPE值要保持一致。

如果空间索引是2维的，就无法将数据表中二维坐标转换为三维的，必须将该索引先drop，然后更新坐标为三维，最后创建空间索引。

# Oracle spatial的使用注意事项

## 1、数据建模和数据装载

### 1.1 始终验证数据的有效性

通过验证函数SDO_GEOM.VALIDATE_*执行验证。或者通过SDO_MIGRATE.TO_CURRENT函数修正任何无效多边形几何体的取向。

网络模型就应使用SDO_NET.VALIDATE_NETWORK函数来验证网络的有效性。

1.2 始终将二维点和三维点存储在SDO_POINT中

为了存储二维点和三维点应当一直使用SDO_GEOMETRY数据类型的SDO_POINT属性。把SDO_ELEM_INFO和SDO_ORDINATES属性设置为NULL。这样可以确保更少的存储要求和更快的存储速度。

1.3 使用TO_CURRENT修正多边形几何体的取向

Oracle spatial需要多边形几何体的边界是逆时针取向的。如果多边形为顺时针取向， 可以通过该函数改变它的取向。

1.4 使用SDO_UNION函数修正一个自交的多边形

对于一个自交的多边形进行验证时会提示多边形边界穿过自身的错误，因此通过该函数可以得到修正。

1.5 始终存储需要的维数或位数

一个三维的几何体，每个点就有三个坐标，而第三个点坐标往往是0值，如果将这些0值清除会省出很多空间。

另外，坐标值中的小数点位，根据需要来取，减少小数点位数可以减少存储空间，并提高几何体的输入输出性能。

2、 空间查询操作符的性能

2.1 指定LAYER_GTYPE参数

如果表仅包含单纯的点数据或线数据或面数据，在创建索引时指定LAYER_GTYPE参数的值，这样就可以提高查询。

3、 空间处理函数的性能

3.1 为存储函数指定DETERMINISTIC

3.2 SDO_AGGR_UNION使用分治法

分组进行并运算

4、 插入、删除、更新的性能

在一个表的一个或多个列上建立了空间索引，那么在该表上的插入、删除、更新操作将花费更长时间，因为索引要实时更新。

方法一、在修改大量记录之前先将索引撤销(修改表中超过30%的记录左右)，之后再创建索引

注：如果空间索引撤销，就不能执行空间操作符的操作

方法二、

在同一事务中，如果批量执行插入、删除或更新操作，就能够最小化空间索引的性能开销。如果想要在一个典型的事务中执行1000多个那样的操作(该建议适用于每个事务含有超过1000个的插入、删除或更新操作)，可以通过指定create INDEX语句的参数sdo_dml_batch_size微调以下性能。默认情况下，被设置为1000.当然如果批量修改的量大，也可以根据实际情况修改。

create index cola_markets_spatial_geo_idx on cola_markets(LOCATION)

indextype is mdsys.spatial_index

parameters ('sdo_dml_batch_size=5000');

sdo_dml_batch_size的值应该在1-10000之间，如果超过10000将会导致许多内存消耗，并不能带来明显的性能改善。

如果已经创建了空间索引，就能够为特定的空间索引手动地在MDSYS模式的SDO_INDEX_METADATA_TABLE表(USER_SDO_INDEX_METADATA和USER_SDO_INDEX_INFO字典视图)中修改这一参数。注意，不能修改该表中的其他参数，否则会导致使用空间操作符的操作失败。

5、 空间索引的扩展性和可操纵性

5.1使用表分区(和本地空间索引)

Oracle的表分区特性及本地空间索引的使用

表的分区：

CREATE TABLE weather_patterns

(

gid NUMBER,

geom SDO_GEOMETRY,

creation_date VARCHAR2(32)

)

PARTITION BY RANGE(creation_date)

(

PARTITION p1 VALUES LESS THAN ('2000-01-01') TABLESPACE tbs_3,

PARTITION p2 VALUES LESS THAN ('2001-01-01') TABLESPACE tbs_3,

PARTITION p3 VALUES LESS THAN ('2002-01-01') TABLESPACE tbs_3,

PARTITION p4 VALUES LESS THAN ('2003-01-01') TABLESPACE tbs_3,

PARTITION p5 VALUES LESS THAN ('2004-01-01') TABLESPACE tbs_3,

PARTITION jan VALUES LESS THAN ('2004-02-01'),

PARTITION feb VALUES LESS THAN ('2004-03-01'),

PARTITION current_month VALUES LESS THAN (MAXVALUE)

);

weather_patterns表基于creation_date列创建了一个分区表。前5个分区指定了表空间，后几个没有指定表空间默认被存储到当前表所在的表空间。

为表创建一个本地分区空间索引，即每个分区创建一个独立的索引(创建空间索引之前要在元数据表中插入相应的记录)：

CREATE INDEX weather_patterns_sidx on weather_patterns(geom)

INDEXTYPE IS mdsys.spatial_index LOCAL;

5.2 本地索引创建为UNUSABLE

CREATE INDEX weather_patterns_sidx on weather_patterns(geom)

INDEXTYPE IS mdsys.spatial_index LOCAL UNUSABLE;

指定为UNUSABLE后，该索引只是一个虚构的索引，任何空间查询操作符，表上的查询、插入、删除、更新操作或者指定的分区等，将引发一个错误，指出分区是UNUSABLE。

因此，处理前应当重建分区上的索引。

5.3为每个分区单独重建空间索引

如下：

ALTER INDEX weather_patterns_sidx REBUILD PARTITION p1;

同样，可以为每个分区分别重建本地索引。通过在多个SQL*PLUS会话中重建那些索引，可以实现并行操作。

分别重建本地索引将在分区索引创建上提供更多的控制权。如一个分区失败，整个索引并没有被标识为失败。即不必为所有的分区重建索引。相反，只需对创建失败的分区重建索引。

可以采用ALTER TABLE…REBUILD UNUSABLE INDEXES 命令一次性地为一个分区重建所有的UNUSABLE索引。

ALTER TABLE weather_patterns_sidx REBUILD PARTITION p1 UNUSABLE LOCAL INDEXES;

5.4在索引创建失败的分区上使用交换分区

重建空间索引可能因为某个原因导致失败，原因主要有分区指定的表空间空间不足或者在表分区的索引列中存在无效的几何体。如果增加表的大小后，再重新执行空间索引的重建操作，索引将被成功创建。但是如果分区中存在无效的几何体，重新执行索引的重建语句将不起作用。此时，将不能更新或删除与无效几何体相应的记录。Oracle对于那些操作或许会产生“分区被标记为faild/unusable”的错误。

为避免上述错误，在创建空间索引之前应该对数据进行验证。

如果出现了“索引失败”的情况，可通过交换分区的方法解决：

首先创建一个临时表tmp，结构与之一样。然后在tmp表中创建一个空间索引。然后执行如下分区操作：

ALTER TABLE weather_patterns EXCHANGE PARTITION current_month WITH TABLE tmp EXCLUDING INDEXES;

此时，tmp表将会有先前weather_patterns表p1分区的数据。由于tmp表不是一个分区表，所以可以在该表上执行常规的DML操作并更正表中无效几何体记录。修正好后，在执行上sql语句将正确数据放回p1分区中。

5.5对新数据使用含有索引的交换分区

如果每天有大量数据更新到current_month分区中，如何确保current_month分区中的数据实时更新？

方法一：采用批量的更新

方法二：创建一个临时表tmp，与上述5.4介绍的类似，同交换实现更新，只是在更新时也包括索引。

ALTER TABLE weather_patterns EXCHANGE PARTITION current_month WITH TABLE tmp INCLUDING INDEXES;

5.6拆分分区

ALTER TABLE weather_patterns

SPLIT PARTITION current_month AT ('2010-04-1') INTO

(

PARTITION march,

PARTITION current_month

);

5.7合并分区

MERGE PARTITION jan,PARTITION feb INTO PARTITION janfeb;

对一个分区重命名

ALTER INDEX weather_patterns_sidx RENAME PARTITION janfeb TO jan;