通过上面的文章我们可以得出,如果希望Oracle分区后,索引也进行相应的分区,需要在DBTUNE添加相应的关键字ST_INDEX_PARTITION_LOCAL
B_STORAGE "PCTFREE 0 INITRANS 4 PARTITION BY RANGE (OBJECTID) ( PARTITION CUS_PART1 VALUES LESS THAN (2000) TABLESPACE SDE, PARTITION CUS_PART2 VALUES LESS THAN (6000) TABLESPACE ESRI ) "
ST_INDEX_PARTITION_LOCAL "TRUE"
这样从新导入到ArcSDE里面,然后导入数据数据的索引就存储在和数据相应的表空间里面了
依照上面的信息,将数据导入进ArcSDE,可以看到Sde用户新增了一个关于分区索引的管理表
SQL> desc st_partition_index
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER NVARCHAR2(32)
TABLE_NAME NVARCHAR2(32)
COLUMN_NAME NVARCHAR2(32)
GEOM_ID NUMBER(38)
PARTITION_NAME NVARCHAR2(32)
GRID SP_GRID_INFO
VERSION NUMBER(38)
STATUS NVARCHAR2(10)
UNIQUENESS VARCHAR2(9)
DISTINCT_KEYS NUMBER
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
CLUSTERING_FACTOR NUMBER
DENSITY NUMBER
NUM_ROWS NUMBER
NUM_NULLS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
USER_STATS VARCHAR2(3)
MINX NUMBER
MINY NUMBER
MAXX NUMBER
MAXY NUMBER
我们查看这个表里面的信息
SQL> select table_name,Partition_name,Geom_id,column_name from st_partition_index;
TABLE_NAME PARTITION_NAME GEOM_ID COLUMN_NAME
--------------------------------------------- ------------------------------------------
ZD CUS_PART1 11 SHAPE
ZD CUS_PART2 11 SHAPE
查询一下相关的信息是否在对应的表空间里面
SQL> select objectid,rowid from zd where objectid=1999 or objectid=2000;
OBJECTID ROWID
---------- ------------------
1999 AAASttAAGAAABX2AAI
2000 AAAStuAAFAAAACOAAA
SQL> select sp_id from s11cus_part1 where rownum<2;
SP_ID
------------------
AAASttAAGAAABXcAAO
SQL> select sp_id from s11cus_part2 where rownum<2;
SP_ID
------------------
AAAStuAAFAAAADnAAH
我们可以看到AAAStt和AAAStu是在不同的表空间里面。
关于空间索引分区Esri提供了如下的帮助
An ST_Geometry st_spatial_index domain index can be built to have discrete index partitions that correspond to each partition for range-partitioned tables. This type of index is called a local domain index, as opposed to a global domain index, which has no index partitions.
A local domain index is equally partitioned with the underlying table. For each partition: all keys in a local domain index refer to rows stored in its corresponding table partition.
St_spatial_index local domain indexes can be created only for range-partitioned, heap-organized tables. Local domain indexes cannot be built for hash-partitioned tables or Indexed Organized Tables.
通过第三段我们可以看出,Esri的空间索引的本地域索引只支持范围分区和堆组织表,不支持哈希分区或者索引组织表。
如果用户已经定义好相关的DBTUNE文件,而且也添加了关于ST_INDEX_PARTITION_LOCAL,创建索引可以自动按照关键字分配的存储。
如果需要手动创建,创建索引可以使用如下命令
SQL> CREATE INDEX st_shape_index
2 ON parcels (shape)
3 INDEXTYPE IS sde.st_spatial_index
4 PARAMETERS('st_srid=1 st_grids=280')
5 LOCAL;
Index created.
在创建过程中,如果你的ST-SRID设置的不对,会提示如下错误
SQL> CREATE INDEX st_zd_shape_index ON ZD (shape) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_srid=5 st_grids=280')
LOCAL;
CREATE INDEX st_zd_shape_index ON ZD (shape) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_srid=5 st_grids=280') LOCA
L
*
第 1 行出现错误:
ORA-29855: 执行 ODCIINDEXCREATE 例行程序时出错
ORA-20083: Parameter ST_SRID 5 does not exist in ST_SPATIAL_REFERENCES table.
ORA-06512: 在 "SDE.SPX_UTIL", line 938
ORA-06512: 在 "SDE.ST_DOMAIN_METHODS", line 1312
如果需要删除索引会报如下错误
那么查看相应的表对比ArcCatalog创建的索引信息基本一致!
SQL> drop index ST_SHAPE_INDEX;
drop index ST_SHAPE_INDEX
*
第 1 行出现错误:
ORA-29952: 无法对标记为 LOADING 的域索引分区发出 DDL 命令
使用如下方法即可删除
SQL> drop index ST_SHAPE_INDEX force;
索引已删除。
其实不需要查看ST_SPATIAL_REFERENCES表,我们只需要查看
st_geometry_columns表的要素类对应的SRID即可
SQL> select SRID from st_geometry_columns where table_name='ZD';
SRID
----------
300002
设置好相应的SRID再次创建
SQL> CREATE INDEX st_zd_shape_index ON ZD (shape) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_srid=300002 st_grids=
280') LOCAL;
索引已创建。
那么查看相应的表对比ArcCatalog创建的索引信息基本一致!
有个小问题,我发现ArcCatalog的索引信息并没有同步过来,也就是在桌面索引框看到的还没有创建索引,不知道是否是个bug!
如果用户的索引想存储在其他磁盘的表空间,也可以使用如下命令:
-------------------------------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
------------------------------------------------------------------------------------------------------
SQL> CREATE INDEX st_shape_index
2 ON parcels (shape)
3 INDEXTYPE IS sde.st_spatial_index
4 LOCAL (PARTITION quarter1 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q1_idx
5 STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
6 PARTITION quarter2 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q2_idx
7 STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
8 PARTITION quarter3 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q3_idx
9 STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
10 PARTITION quarter4 PARAMETERS('st_srid=1 st_grids=280 TABLESPACE q4_idx
11 STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'),
12 PARTITION qunknown PARAMETERS('st_srid=1 st_grids=280 TABLESPACE qunknown_idx
13 STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0)'));
Index created.
-------------------------------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
------------------------------------------------------------------------------------------------------