怎样在ArcSDE中操作Oracle分区表2

发布网友 发布时间：2022-04-11 19:00

我来回答

共1个回答

热心网友 时间：2022-04-11 20:29

通过上面的文章咱们可能得出，假设心愿Oracle分区后，索引也停止相应的分区，需求在DBTUNE减少相应的要害字ST_INDEX_PARTITION_LOCALB_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">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">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">SQL> select objectid,rowid from zd where objectid=1999 or objectid=2000; OBJECTID ROWID ---------- ------------------ 1999 AAASttAAGAAABX2AAI 2000 AAAStuAAFAAAACOAAA sql">SQL> select sp_id from s11cus_part1 where rownum<2; SP_ID ------------------ AAASttAAGAAABXcAAO sql">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的空间索引的当地区索引只反对范畴分区和堆组织表，不反对哈希分区或许索引组织表。最近有用户问怎样操作SQL语句之间创建ArcSDE的索引并且这些索引页停止分区，大家可能参考以下命令假设用户已经定义好相干的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假设需求删除索引会报如下舛误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; 索引已创建。假设用户的索引想存储在其余磁盘的表空间，也可能操作如下命令：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.