Spatial Extenders index usage

[peter.prib]

Hi,

I am having trouble getting DB2 to select an index I have created for a
spatial location.

Table definition

create table gps_paf_address
( gnaf_address gnaf_address not null
,GNF_PID varchar(015) not null

,location st_point not null

) in data4k1 index in index4k1 long in long4k1
;
spatial index

CREATE INDEX "GPS "."GPS_PAF_ADDR_S2" ON "GPS
"."GPS_PAF_ADDRESS"
("LOCATION" )
EXTEND USING "DB2GSE "."SPATIAL_INDEX" (0.0001, 0,
0) ;
Tried this no success

select
cast(db2gse.st_x(location)as char(15)) as GNF_LONG

, cast(db2gse.st_y(location)as char(15)) as GNF_LAT
, db2gse.st_distance(db2gse.st_point( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_address addr
where
db2gse.st_distance(db2gse.st_point('POINT( -33.71492167 151.1091933
)' ,1),location ) < 0.002 selectivity 0.0000001
Tried this no success

select
cast(db2gse.st_x(location)as char(15)) as GNF_LONG

, cast(db2gse.st_y(location)as char(15)) as GNF_LAT
, db2gse.st_distance(db2gse.st_point( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_address addr
where
db2gse.ST_Contains(
db2gse.ST_Polygon('polygon(( -33.714 151.10
, -33.714 151.12
, -33.715 151.12
, -33.715 151.10
, -33.714 151.10
))',1),
location
) = 1
selectivity 0.0000001
I have performed a Runstats and the grid size has the following stats

[/home/db2inst1] : TATISTICS FOR COLUMN gps.gps_paf_address(location) \
<

USING GRID SIZES (0.0001) SHOW HISTOGRAM WITH 10 BUCKETS"

Number of Rows: 3895220
Number of non-empty Geometries: 3895220
Number of empty Geometries: 0
Number of null values: 0

Extent covered by data:
Minimum X: -37.375902
Maximum X: -28.165696
Minimum Y: 141.011252
Maximum Y: 153.636718

Grid Level 1
------------

Grid Size : 0.0001
Number of Geometries : 3895220
Number of Index Entries : 3895220

Number of occupied Grid Cells : 2326459
Index Entry/Geometry ratio : 1.000000
Geometry/Grid Cell ratio : 1.674313
Maximum number of Geometries per Grid Cell: 2473
Minimum number of Geometries per Grid Cell: 1

Index Entries : 1 2 3 4 10
--------------- ------ ------ ------ ------ ------
Absolute : 3895220 0 0 0 0
Percentage (%): 100.00 0.00 0.00 0.00 0.00

Is there some other action I need to perform or what have I done wrong.
DB2 V8 fixpak 10

[peter.prib]

Found the problem, appears to be a bug in DB2. The optimiser seems to
be sensitive to columns in the answer set. I left out some columns
from the select not to confuse matters. These appear to have been
important. One was a transform function against a structured data
type. When I removed this reference it operated successfully. Not
sure why the columns returned (and function operating on columns)
should stop the optimiser using the index.

Hi,

I am having trouble getting DB2 to select an index I have created for a
spatial location.

Table definition

create table gps_paf_address
( gnaf_address gnaf_address not null
,GNF_PID varchar(015) not null

,location st_point not null

) in data4k1 index in index4k1 long in long4k1
;
spatial index

CREATE INDEX "GPS "."GPS_PAF_ADDR_S2" ON "GPS
"."GPS_PAF_ADDRESS"
("LOCATION" )
EXTEND USING "DB2GSE "."SPATIAL_INDEX" (0.0001, 0,
0) ;
Tried this no success

select
cast(db2gse.st_x(location)as char(15)) as GNF_LONG

, cast(db2gse.st_y(location)as char(15)) as GNF_LAT
, db2gse.st_distance(db2gse.st_point( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_address addr
where
db2gse.st_distance(db2gse.st_point('POINT( -33.71492167 151.1091933
)' ,1),location ) < 0.002 selectivity 0.0000001
Tried this no success

select
cast(db2gse.st_x(location)as char(15)) as GNF_LONG

, cast(db2gse.st_y(location)as char(15)) as GNF_LAT
, db2gse.st_distance(db2gse.st_point( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_address addr
where
db2gse.ST_Contains(
db2gse.ST_Polygon('polygon(( -33.714 151.10
, -33.714 151.12
, -33.715 151.12
, -33.715 151.10
, -33.714 151.10
))',1),
location
) = 1
selectivity 0.0000001
I have performed a Runstats and the grid size has the following stats

[/home/db2inst1] : TATISTICS FOR COLUMN gps.gps_paf_address(location) \
<

USING GRID SIZES (0.0001) SHOW HISTOGRAM WITH 10 BUCKETS"

Number of Rows: 3895220
Number of non-empty Geometries: 3895220
Number of empty Geometries: 0
Number of null values: 0

Extent covered by data:
Minimum X: -37.375902
Maximum X: -28.165696
Minimum Y: 141.011252
Maximum Y: 153.636718

Grid Level 1
------------

Grid Size : 0.0001
Number of Geometries : 3895220
Number of Index Entries : 3895220

Number of occupied Grid Cells : 2326459
Index Entry/Geometry ratio : 1.000000
Geometry/Grid Cell ratio : 1.674313
Maximum number of Geometries per Grid Cell: 2473
Minimum number of Geometries per Grid Cell: 1

Index Entries : 1 2 3 4 10
--------------- ------ ------ ------ ------ ------
Absolute : 3895220 0 0 0 0
Percentage (%): 100.00 0.00 0.00 0.00 0.00

Is there some other action I need to perform or what have I done wrong.
DB2 V8 fixpak 10

[Knut Stolze]

pe********@salmat.com.au wrote:

Found the problem, appears to be a bug in DB2. The optimiser seems to
be sensitive to columns in the answer set. I left out some columns
from the select not to confuse matters. These appear to have been
important. One was a transform function against a structured data
type. When I removed this reference it operated successfully. Not
sure why the columns returned (and function operating on columns)
should stop the optimiser using the index.

You may want to have a look here if you're interested in spatial extender
performance tuning:

http://tinyurl.com/aqztc

As for the question why the index was not used originally with the
additional columns, it would be good to have a look at the query plan. I
imagine that the plan is quite different...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany