例: 地理空間単一テーブル述部 - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ 地理空間データ型

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
2019年3月
Language
日本語
Last Update
2019-10-29
dita:mapPath
ja-JP/swn1512082023009.ditamap
dita:ditavalPath
ja-JP/swn1512082023009.ditaval
dita:id
B035-1181
Product Category
Software
Teradata Vantage

次の例は、最適化ルーチンが地理空間インデックスを使用してデータ アクセスできる、地理空間単一テーブル述部の使用法を示しています。

CREATE TABLE StatT2
 (a INT,b CHAR(20000),sp ST_Geometry)
 INDEX(sp);

INSERT StatT2(1, 'Teradata01','Point(10 10 10)');


テーブルに挿入される行の数と地理空間データ自体の性質は、最適化ルーチンがインデックスを使用することを決めるかどうかで重要な役割を果たします。

SELECT a,b(format 'x(10)'), sp
FROM StatT2 
WHERE StatT2.sp.ST_WITHIN(CAST(
'POLYGON((0 0 0, 0 50 50, 50 50 50, 50 0 50, 0 0 0))' 
AS ST_Geometry)) = 1;

SELECT a,b (format 'x(10)'), sp
FROM StatT2
WHERE StatT2.sp.ST_WITHIN(NEW ST_GEOMETRY(
 'POLYGON((0 0 0, 0 50 50, 50 50 50, 50 0 50, 0 0 0))')) = 1;

SELECT a,b (format 'x(10)'), sp
FROM StatT2
WHERE StatT2.sp.ST_WITHIN(NEW ST_GEOMETRY(
 'POLYGON((0 0, 0 20, 20 20, 20 0, 0 0))').ST_INTERSECTION(
 'POLYGON((0 0, 0 15, 15 15, 15 0, 0 0))')) = 1;

次に、最適化ルーチンが地理空間インデックスを使用していることを示すEXPLAIN出力の例を示します。

Explanation
-----------------------------------------------------------------------
1) First, we lock JOSHUA.StatT2 for read on a reserved RowHash to
prevent global deadlock.
2) Next, we lock JOSHUA.StatT2 for read.
3) We do an all-AMPs SUM step to aggregate from JOSHUA.StatT2  by way 
 of Spatial index # 4  "Retrieving from CDT indexed column
JOSHUA.StatT2.sp via CDT key expression NEW ST_GEOMETRY (
'POLYGON((0 0, 0 20, 20 20, 20 0, 0 0))'(VARCHAR(64000), CHARACTER
SET LATIN, NOT CASESPECIFIC)).ST_INTERSECTION (CAST(('POLYGON((9
9, 9 18, 18 18, 9 9))'(VARCHAR(64000), CHARACTER SET LATIN, NOT
CASESPECIFIC)) AS SYSUDTLIB.ST_Geometry)).ST_MBR ()"with a
residual condition of ("(JOSHUA.StatT2.sp .ST_WITHIN (NEW
ST_GEOMETRY ('POLYGON((0 0, 0 20, 20 20, 20 0, 0
0))'(VARCHAR(64000), CHARACTER SET LATIN, NOT
CASESPECIFIC)).ST_INTERSECTION (CAST(('POLYGON((9 9, 9 18, 18 18,
9 9))'(VARCHAR(64000), CHARACTER SET LATIN, NOT CASESPECIFIC)) AS
SYSUDTLIB.ST_Geometry))))= 1"), and the grouping identifier in
field 1027. Aggregate Intermediate Results are computed globally,
then placed in Spool 3. The size of Spool 3 is estimated with no
confidence to be 3 rows (63 bytes). The estimated time for this
step is 0.07 seconds.
4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. Then we do a SORT to order Spool 1 by the sort key
in spool field1. The size of Spool 1 is estimated with no
confidence to be 3 rows (96 bytes). The estimated time for this
step is 0.04 seconds.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.11 seconds.