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