sde for DB2的空间索引扩展

0
分享 2015-12-07
在oracle数据库中存在着Domain索引,称为域索引,实际上就是应用程序自定义索引,在DB2中也有类似的对象,为index extension对象,
在DB2中使用db2look命令查看某个空间表的定义的时候,其表的定义信息通常如下:
CREATE TABLE "SDE "."STREETS" (
"OBJECTID_1" INTEGER NOT NULL ,
"OBJECTID" DECIMAL(9,0) ,
"L_F_ADD" DECIMAL(9,0) ,
"L_T_ADD" DECIMAL(9,0) ,
"R_F_ADD" DECIMAL(9,0) ,
"R_T_ADD" DECIMAL(9,0) ,
"PREFIX" VARCHAR(2) ,
"PRETYPE" VARCHAR(20) ,
"NAME" VARCHAR(40) ,
"TYPE" VARCHAR(20) ,
"SUFFIX" VARCHAR(2) ,
"PREFIX1" VARCHAR(2) ,
"PRETYPE1" VARCHAR(20) ,
"NAME1" VARCHAR(40) ,
"TYPE1" VARCHAR(20) ,
"SUFFIX1" VARCHAR(2) ,
"PREFIX2" VARCHAR(2) ,
"PRETYPE2" VARCHAR(20) ,
"NAME2" VARCHAR(40) ,
"TYPE2" VARCHAR(20) ,
"SUFFIX2" VARCHAR(2) ,
"PREFIX3" VARCHAR(2) ,
"PRETYPE3" VARCHAR(20) ,
"NAME3" VARCHAR(40) ,
"TYPE3" VARCHAR(20) ,
"SUFFIX3" VARCHAR(2) ,
"PREFIX4" VARCHAR(2) ,
"PRETYPE4" VARCHAR(20) ,
"NAME4" VARCHAR(40) ,
"TYPE4" VARCHAR(20) ,
"SUFFIX4" VARCHAR(2) ,
"PREFIX5" VARCHAR(2) ,
"PRETYPE5" VARCHAR(20) ,
"NAME5" VARCHAR(40) ,
"TYPE5" VARCHAR(20) ,
"SUFFIX5" VARCHAR(2) ,
"SHIELD" VARCHAR(1) ,
"HWY_NUM" VARCHAR(5) ,
"FCC" VARCHAR(3) ,
"ACC" VARCHAR(1) ,
"TOLL" VARCHAR(1) ,
"SPEED" DECIMAL(9,0) ,
"ONE_WAY" VARCHAR(2) ,
"F_ZLEV" DECIMAL(9,0) ,
"T_ZLEV" DECIMAL(9,0) ,
"POSTAL_L" VARCHAR(5) ,
"POSTAL_R" VARCHAR(5) ,
"GEONAME_L" VARCHAR(40) ,
"GEONAME_R" VARCHAR(40) ,
"STATE_L" VARCHAR(2) ,
"STATE_R" VARCHAR(10) ,
"STATE_NAME" VARCHAR(40) ,
"COUNTRY" VARCHAR(3) ,
"SHAPE" "DB2GSE "."ST_MULTILINESTRING" )
COMPRESS YES
IN "USERSPACE1" ;






-- DDL Statements for indexes on Table "SDE "."STREETS"

CREATE INDEX "SDE "."A6_IX1" ON "SDE "."STREETS"
("SHAPE" )

COMPRESS NO
EXTEND USING "DB2GSE "."SPATIAL_INDEX" (.110000e-1,.000000e0,.000000e0) DISALLOW REVERSE SCANS;
其中A6_IX1为一个创建在表streets,SHAPE列上的索引,其所使用索引的类型为DB2GSE.SPATIAL_INDEX,那 DB2GSE.SPATIAL_INDEX
是什么东西,通过db2look 命令也是可以查到其定义:
CREATE INDEX EXTENSION DB2GSE.SPATIAL_INDEX( GS1 DOUBLE,GS2 DOUBLE,GS3 DOUBLE)

FROM SOURCE KEY(GEOMETRY DB2GSE.ST_GEOMETRY)
GENERATE KEY USING DB2GSE.GSEGRIDIDXKEYGEN(GEOMETRY..SRID,
GEOMETRY..XMIN,
GEOMETRY..XMAX,
GEOMETRY..YMIN,
GEOMETRY..YMAX,
GS1,GS2,GS3)

WITH TARGET KEY(SRSID INT, LVL INT,GX INT,GY INT,
XMIN DOUBLE,XMAX DOUBLE, YMAX DOUBLE,
YMAX DOUBLE)
SEARCH METHODS
WHEN CONTAINS(W DB2GSE.ST_GEOMETRY)

RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPROD(W..SRID, W..XMIN,W..XMAX, W..YMIN,W..YMAX,

GS1,GS2,GS3)
FILTER USING DB2GSE.GSEGRIDIDXFILTER(1, W..XMIN,W..XMAX,W..YMIN,
W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3)
WHEN WITHIN(W DB2GSE.ST_GEOMETRY)
RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPROD(W..SRID, W..XMIN,W..XMAX, W..YMIN,W..YMAX,
GS1,GS2,GS3) FILTER USING DB2GSE.GSEGRIDIDXFILTER(2, W..XMIN,W..XMAX,W..YMIN,
W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3)
WHEN EQUALS(W DB2GSE.ST_GEOMETRY)
RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPROD(W..SRID, W..XMIN,W..XMAX, W..YMIN,W..YMAX,
GS1,GS2,GS3) FILTER USING DB2GSE.GSEGRIDIDXFILTER(3, W..XMIN,W..XMAX,W..YMIN,
W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3)
WHEN INTERSECTS(W DB2GSE.ST_GEOMETRY)
RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPROD(W..SRID, W..XMIN,W..XMAX, W..YMIN,W..YMAX,
GS1,GS2,GS3) FILTER USING DB2GSE.GSEGRIDIDXFILTER(4, W..XMIN,W..XMAX,W..YMIN,
W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3)
WHEN INTERSECTS2(W DB2GSE.ST_GEOMETRY) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPROD(W..SRID, W..XMIN,W..XMAX,
W..YMIN,W..YMAX, GS1,GS2,GS3) FILTER USING CASE WHEN W..SRID BETWEEN 2000000000
AND 2000001000 THEN DB2GSE.GSEGRIDIDXFILTER(4, W..XMIN,W..XMAX,W..YMIN,
W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3) WHEN (W..YMIN > YMAX)
OR (W..YMAX < YMIN) OR (W..XMIN > XMAX) OR (W..XMAX < XMIN) THEN 0 ELSE
DB2GSE.GSEGRIDIDXFILTER(0, W..XMIN,W..XMAX,W..YMIN, W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY,
GS1,GS2,GS3) END
WHEN INTERSECTS3(W DB2GSE.ST_GEOMETRY) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPROD(W..SRID,
W..XMIN,W..XMAX, W..YMIN,W..YMAX, GS1,GS2,GS3) FILTER USING CASE WHEN W..SRID
BETWEEN 2000000000 AND 2000001000 THEN DB2GSE.GSEGRIDIDXFILTER(4, W..XMIN,W..XMAX,W..YMIN,
W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3) WHEN (W..YMIN > YMAX)
OR (W..YMAX < YMIN) OR (W..XMIN > XMAX) OR (W..XMAX < XMIN) THEN 0 ELSE
1 END
WHEN WINDOW(WXMN double,WYMN double,WXMX double,WYMX double,WSRID
int) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPWIND(WSRID, WXMN,WXMX, WYMN,WYMX,
GS1,GS2,GS3) FILTER USING CASE WHEN WSRID BETWEEN 2000000000 AND 2000001000
THEN DB2GSE.GSEGRIDIDXFILTWIND(4, WSRID, WXMN,WXMX,WYMN,WYMX, XMIN,XMAX,YMIN,YMAX,LVL,GX,GY,
GS1,GS2,GS3) WHEN (WYMN > YMAX) OR (WYMX < YMIN) OR (WXMN > XMAX) OR (WXMX
< XMIN) THEN 0 ELSE DB2GSE.GSEGRIDIDXFILTER(0, WXMN,WXMX,WYMN, WYMX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY,
GS1,GS2,GS3) END
WHEN WINDOW2(WXMN double,WYMN double,WXMX double,WYMX
double,WSRID int) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPWIND(WSRID, WXMN,WXMX,
WYMN,WYMX, GS1,GS2,GS3) FILTER USING CASE WHEN WSRID BETWEEN 2000000000
AND 2000001000 THEN DB2GSE.GSEGRIDIDXFILTWIND(4, WSRID, WXMN,WXMX,WYMN,WYMX,
XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3) WHEN (WYMN > YMAX) OR (WYMX
< YMIN) OR (WXMN > XMAX) OR (WXMX < XMIN) THEN 0 ELSE 1 END
WHEN IDXINTERSECTS(WXMN double,WYMN double,WXMX double,
WYMX double,WSRID int) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPWIND(WSRID,
WXMN,WXMX, WYMN,WYMX, GS1,GS2,GS3) WHEN ALLROWS(P int) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGALL(GS1,GS2,GS3)
FILTER USING DB2GSE.GSEGRIDIDXFILALL( XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3)
WHEN OVERLAP(W DB2GSE.ST_GEOMETRY) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPROD(W..SRID,
W..XMIN,W..XMAX, W..YMIN,W..YMAX, GS1,GS2,GS3) FILTER USING DB2GSE.GSEGRIDIDXFILTER(5,
W..XMIN,W..XMAX,W..YMIN, W..YMAX,XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3)
WHEN DISTANCE(C DB2GSE.ST_GEOMETRY,R DOUBLE) RANGE THROUGH DB2GSE.GSEGRIDIDXRNGPDIST(C..SRID,
C..XMIN,C..XMAX, C..YMIN,C..YMAX, R, GS1,GS2,GS3) FILTER USING DB2GSE.GSEGRIDIDXFILTDIST(
4, C..XMIN,C..XMAX,C..YMIN,C..YMAX, R, XMIN,XMAX,YMIN,YMAX,LVL,GX,GY, GS1,GS2,GS3);

从定义上可以看出,WHEN后面的这些method用到了索引。
文章来源:http://blog.csdn.net/liufeng1980423/article/details/6331464

0 个评论

要回复文章请先登录注册