最近好多用户对Oracle的分区表很感兴趣,也希望分区表也能应用的ArcSDE,询问该技术在ArcSDE是否可行,答案是肯定的。
不过因为在Oracle中分区表的对象基本上就是一个同名表,但是在ArcSDE中就有些不同了,因为如果进行版本编辑有A表、D表等,所以建议如果对ArcSDE的某个要素类进行分区表的存储,建议该要素类为只读状态,也就是说修改的可能性小一点,这样做分区表才更有意义,而且分区表的存储一般针对某个表,可能这个表有千万或者上亿条记录,而且经常对这个表进行读操作,那么我们就可以根据某个字段将这些表进行分段存储。具体分区表的分段方式我就不介绍了。
怎么分区,按照什么分区,这就看用户在业务上怎么对这个表进行应用了。
1:没有任何的章法可循,没有固定条件查询,可以按照ObjectID进行分区
2:有些用户对日期感兴趣,对日期字段进行分区
3:有些用户对行政区感兴趣,对行政区编码进行分区
4:有些用户对地类感兴趣,可以将地位按一级编码进行分区
...............
所以说,怎么分区,还是按业务进行考虑
那么在ArcSDE中关于存储的配置文件在SDEHOME/etc/dbtune.sde里面,用户也可以使用dbtune命令将这些信息导出,然后修改,然后再导入。
##PARTITION
A_INDEX_RASTER "PCTFREE 0 INITRANS 4 NOLOGGING "
XMLDB_INDEX_TAG "YES"
XMLDB_INDEX_FULLTEXT "NO"
XML_IDX_TEXT_UPDATE_METHOD "NONE"
XML_IDX_TEXT_UPDATE_MEMORY ""
XML_IDX_TEXT_TAG_STORAGE ""
XML_IDX_STORAGE "PCTFREE 0 INITRANS 4"
XML_IDX_INDEX_TEXT ""
XML_IDX_INDEX_TAG "PCTFREE 0 INITRANS 4 NOLOGGING "
XML_IDX_INDEX_STRING "PCTFREE 0 INITRANS 4 NOLOGGING "
XML_IDX_INDEX_PK "PCTFREE 0 INITRANS 4 NOLOGGING "
XML_IDX_INDEX_ID "PCTFREE 0 INITRANS 4 NOLOGGING "
XML_IDX_INDEX_DOUBLE "PCTFREE 0 INITRANS 4 NOLOGGING "
XML_DOC_VAL_LOB_STORAGE "NOCACHE NOLOGGING CHUNK 4K PCTVERSION 5 DISABLE STORAGE IN ROW"
XML_DOC_UNCOMPRESSED_TYPE "CLOB"
XML_DOC_STORAGE "PCTFREE 0 INITRANS 4"
XML_DOC_MODE "COMPRESSED"
XML_DOC_LOB_STORAGE "NOCACHE NOLOGGING CHUNK 4K PCTVERSION 5 DISABLE STORAGE IN ROW"
XML_DOC_INDEX "PCTFREE 0 INITRANS 4 NOLOGGING "
XML_COLUMN_STORAGE "SDE_XML"
UI_TEXT "User Interface text for DEFAULTS"
ST_GEOM_LOB_STORAGE " STORE AS ( ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE) "
S_STORAGE "PCTFREE 0 INITRANS 4"
S_INDEX_ALL "PCTFREE 0 INITRANS 4 NOLOGGING "
RASTER_STORAGE "BLOB"
RAS_STORAGE "PCTFREE 0 INITRANS 4"
RAS_INDEX_ID "PCTFREE 0 INITRANS 4 NOLOGGING "
GEOMETRY_STORAGE "ST_GEOMETRY"
D_STORAGE "PCTFREE 0 INITRANS 4"
D_INDEX_STATE_ROWID "PCTFREE 0 INITRANS 4 NOLOGGING "
D_INDEX_DELETED_AT "PCTFREE 0 INITRANS 4 NOLOGGING "
BND_STORAGE "PCTFREE 0 INITRANS 4"
BND_INDEX_ID "PCTFREE 0 INITRANS 4 NOLOGGING "
BND_INDEX_COMPOSITE "PCTFREE 0 INITRANS 4 NOLOGGING "
BLK_STORAGE "PCTFREE 0 INITRANS 4 LOB(BLOCK_DATA) STORE AS ( ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE) "
BLK_INDEX_COMPOSITE "PCTFREE 0 INITRANS 4 NOLOGGING "
B_STORAGE "PCTFREE 0 INITRANS 4 PARTITION BY RANGE (OBJECTID) ( PARTITION CUS_PART1 VALUES LESS THAN (50000) TABLESPACE ESRI, PARTITION CUS_PART2 VALUES LESS THAN (100000) TABLESPACE ESRI2 ) "
B_INDEX_XML "PCTFREE 0 INITRANS 4 NOLOGGING "
B_INDEX_USER "PCTFREE 0 INITRANS 4 NOLOGGING "
B_INDEX_TO_DATE "PCTFREE 0 INITRANS 4 NOLOGGING "
B_INDEX_ROWID "PCTFREE 0 INITRANS 4 NOLOGGING "
B_INDEX_RASTER "PCTFREE 0 INITRANS 4 NOLOGGING "
AUX_STORAGE "PCTFREE 0 INITRANS 4"
AUX_INDEX_COMPOSITE "PCTFREE 0 INITRANS 4 NOLOGGING "
ATTRIBUTE_BINARY "BLOB"
A_STORAGE "PCTFREE 0 INITRANS 4"
A_INDEX_XML "PCTFREE 0 INITRANS 4 NOLOGGING "
A_INDEX_USER "PCTFREE 0 INITRANS 4 NOLOGGING "
A_INDEX_STATEID "PCTFREE 0 INITRANS 4 NOLOGGING "
A_INDEX_SHAPE "PCTFREE 0 INITRANS 4 NOLOGGING "
A_INDEX_ROWID "PCTFREE 0 INITRANS 4 NOLOGGING "
END
这是我建立了一个分区表的关键字,重点看一下
"PCTFREE 0 INITRANS 4 PARTITION BY RANGE (OBJECTID) ( PARTITION CUS_PART1 VALUES LESS THAN (50000) TABLESPACE ESRI, PARTITION CUS_PART2 VALUES LESS THAN (100000) TABLESPACE ESRI2 ) "
这一段就是根据ObjectID,将ObjectID小于50000的存储在ESRI表空间,将ObjectID小于100000的存储到ESRI2表空间里面。然后我将最上面的信息加载到从sdedbtune导出来的文档中,然后导入
C:\Users\Administrator>sdedbtune -o import -f c:\1.txt -i sde:oracle11g:orcl -s lish -u sde -p sde -q
Import DBTUNE Table. Are you sure? (Y/N): y
Successfully imported from file "c:\1.txt"
然后我们使用ArcCatalog导入数据,记得选择上面的关键字“PARTITION“。
导入之后我们进行一下验证
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ----------------------
BIN$SVcacMx9QjSugD7de5WsPw==$0 CUS_PART2 SDE
BIN$SVcacMx9QjSugD7de5WsPw==$0 CUS_PART1 ESRI
BIN$WZM7+2pGTTKIYNXmkOSLzw==$0 CUS_PART1 ESRI
BIN$WZM7+2pGTTKIYNXmkOSLzw==$0 CUS_PART2 SDE
BIN$x6yBgW00SgyGPOTa1odkbQ==$0 CUS_PART1 ESRI
BIN$x6yBgW00SgyGPOTa1odkbQ==$0 CUS_PART2 SDE
CCC CUS_PART2 ESRI2
CCC CUS_PART1 ESRI
已选择8行。
SQL> select objectid,rowid from ccc where objectid=1 or objectid=33333 or objectid=49999 or objectid =50001 or objectid=88888 or objectid=66666;
OBJECTID ROWID
---------- ------------------
1 AAASe0AAHAAAFiFAAA
33333 AAASe0AAHAAAF87AAJ
49999 AAASe0AAHAAAGLbAAQ
50001 AAASe1AAIAAAACFAAB
66666 AAASe1AAIAAAAQWAAK
88888 AAASe1AAIAAAAgMAAU
已选择6行。
我们从ROWID也可以看到在50000之前存储的”Se0“表空间,50000以后为”Se1“表空间。
##PARTITION
B_STORAGE "PARTITION BY RANGE (location_date)
(PARTITION jan VALUES LESS THAN ( to_date('02-01-2008','MM-DD-YYYY') )
STORAGE (INITIAL 128K) TABLESPACE january,
PARTITION feb VALUES LESS THAN ( to_date('03-01-2008','MM-DD-YYYY') )
STORAGE (INITIAL 128K) TABLESPACE february,
PARTITION mar VALUES LESS THAN ( to_date('04-01-2008','MM-DD-YYYY') )
STORAGE (INITIAL 128K) TABLESPACE march,
PARTITION apr VALUES LESS THAN ( to_date('05-01-2008','MM-DD-YYYY') )
STORAGE (INITIAL 128K) TABLESPACE april,
PARTITION may VALUES LESS THAN ( to_date('06-01-2008','MM-DD-YYYY') )
STORAGE (INITIAL 128K) TABLESPACE may)
ENABLE ROW MOVEMENT"
GEOMETRY_STORAGE "ST_GEOMETRY"
UI_TEXT "User Interface text description for Partition"
END
如果创建包含 ST_Geometry 列的分区表,则还需对空间索引进行分区。分区方法有如下两种:全局和局部。默认情况下,将对已分区业务表创建全局分区索引。而要创建局部分区索引,则必须在 CREATE INDEX 语句的末尾添加关键字 LOCAL。为使 ArcGIS 能够在空间索引的 CREATE INDEX 语句末尾添加 LOCAL,请在 DEFAULTS 关键字下将 ST_INDEX_PARTITION_LOCAL 设置为 TRUE。
没有的话可以添加上去。
导入之后我们可以查看,多了两个空间索引表,建立多少个分区就产生多少个索引表,索引表以分区名称区分
C:\Users\Administrator>sqlplus test/test@orcl
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 7 16:53:26 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc scus_part1_px$;
名称 是否为空? 类型
----------------------------------------- -------- --------------------------
GX NOT NULL NUMBER(38)
GY NOT NULL NUMBER(38)
MINX NOT NULL NUMBER(38)
MINY NOT NULL NUMBER(38)
MAXX NOT NULL NUMBER(38)
MAXY NOT NULL NUMBER(38)
SP_ID NOT NULL ROWID
SQL> desc scus_part2_px$;
名称 是否为空? 类型
----------------------------------------- -------- --------------------------
GX NOT NULL NUMBER(38)
GY NOT NULL NUMBER(38)
MINX NOT NULL NUMBER(38)
MINY NOT NULL NUMBER(38)
MAXX NOT NULL NUMBER(38)
MAXY NOT NULL NUMBER(38)
SP_ID NOT NULL ROWID
但如果未对包含 ST_Geometry 列的业务表进行分区,则将 ST_INDEX_PARTITION_LOCAL 设置为 TRUE 时会收到以下错误消息:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
------------------------------------------------------------------------------------------------------