EXPLAIN - Teradata QueryGrid

Teradata QueryGridコネクタを使用して、リモート システムにクエリーを実行しそのリモート システムからのEXPLAINプランをリクエストできます。

例: セッションオーバーライド導入

この例では、セッション オーバーライド導入のEXPLAINプランを生成します。

< BEGIN EXPLAIN FOR REMOTE QUERY -->
 ----- BEGIN SESSION OVERRIDDEN PROPERTIES -----
 'linkBufferSize' = '1048576'
 'schemaName' = 'default'
 ----- END SESSION OVERRIDDEN PROPERTIES -----
     Remote Queries:
     1. CREATE TABLE
     qgremote.DEFAULT.TEMP_38b505c0_9fb4_4a3d_bdc8_000000000017_EXEC
...

例: TeradataからPrestoへのEXPLAIN SELECTの使用

この例では、リモートSELECTクエリーからEXPLAINプランを生成し、ローカルのTeradataシステムにおけるクエリー プランに結果を埋め込みます。

EXPLAIN SELECT * FROM p100@tdh111 WHERE c1 > 10;

結果:

Explanation
---------------------------------------------------------------------------
  1) First, we do an all-AMPs RETRIEVE step executing table operator
     TD_SYSFNLIB.QGINITIATORIMPORT with a condition of ("p100.C1 >= 11").
     < BEGIN EXPLAIN FOR REMOTE QUERY -->
     Remote Queries:
     1. EXPLAIN CREATE TABLE
     qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014e_EXEC AS
     SELECT c1 ,c2 ,c3 ,c4 ,c5 FROM "default"."p100" WHERE C1 >= 11
     [- Output[rows] => [rows:bigint]
     -
     TableCommit[qgremote:QGRemoteTransactionHandle{uuid=280273f9-403c-4cc7
     -b0b5-dc148b36285f}:com.teradata.querygrid.qgc.presto.QGRemoteInsertTa
     bleHandle@e32e808] => [rows:bigint]
     - Exchange[GATHER] => partialrows:bigint, fragment:varbinary
     - TableWriter => [partialrows:bigint, fragment:varbinary]
     c1 := c1
     c2 := c2
     c3 := c3
     c4 := c4
     c5 := c5
     - Exchange[REPARTITION] => c1:bigint, c2:varchar, c3:double,
     c4:boolean, c5:varbinary
     - ScanFilterAndProject[table = hive:hive:default:p100,
     originalConstraint = ("c1" >= 11), filterPredicate = ("c1" >= 11)]
     => [c1:bigint, c2:varchar, c3:double, c4:boolean, c5:varbinary]
     LAYOUT: hive
     c1 := HiveColumnHandle{clientId=hive, name=c1, hiveType=bigint,
     hiveColumnIndex=0, partitionKey=false}
     c2 := HiveColumnHandle{clientId=hive, name=c2, hiveType=string,
     hiveColumnIndex=1, partitionKey=false}
     c3 := HiveColumnHandle{clientId=hive, name=c3, hiveType=double,
     hiveColumnIndex=2, partitionKey=false}
     c4 := HiveColumnHandle{clientId=hive, name=c4, hiveType=boolean,
     hiveColumnIndex=3, partitionKey=false}
     c5 := HiveColumnHandle{clientId=hive, name=c5, hiveType=binary,
     hiveColumnIndex=4, partitionKey=false}
    ]
     <-- END EXPLAIN FOR REMOTE QUERY >
     The size of Spool 1 is estimated with low confidence to be 8 rows
     (33,096 bytes).  The estimated time for this step is 0.15 seconds.
  2) Next, we do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
     way of an all-rows scan with a condition of ("p100.C1 >= 11") into
     Spool 2 (group_amps), which is built locally on the AMPs.  The
     size of Spool 2 is estimated with low confidence to be 8 rows (
     98,840 bytes).  The estimated time for this step is 0.16 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 2 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.31 seconds.

例: TeradataからPrestoへのEXPLAIN INSERTの使用

この例では、リモートINSERTクエリーのEXPLAINプランを生成し、ローカルのTeradataシステムにおけるクエリー プランに結果を埋め込みます。

EXPLAIN INSERT INTO p100@tdh111 values(1,'abc',11.1, 1, 1);

結果:

Explanation
---------------------------------------------------------------------------
  1) First, we do an INSERT into Spool 4.
  2) Next, we do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
     way of an all-rows scan executing table operator
     TD_SYSFNLIB.QGINITIATOREXPORT with a condition of ("(1=1)") into
     Spool 2 (used to materialize view, derived table, table function
     or table operator p100) (all_amps), which is built locally on the
     AMPs.
     < BEGIN EXPLAIN FOR REMOTE QUERY -->
     Remote Queries:
     1. CREATE TABLE
     qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014f_EXEC AS
     SELECT * FROM "p100" WITH NO DATA
     2. EXPLAIN INSERT INTO "p100" SELECT * FROM
     qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014f_EXEC
     3. DROP TABLE IF EXISTS
     qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014f_EXEC
     [- Output[rows] => [rows:bigint]
     - TableCommit[hive:LegacyTransactionHandle{connectorId=hive,
     uuid=f15a3b4c-89a8-427d-8dcd-c147334ff2ca}:hive:default.p100] =>
     [rows:bigint]
     - Exchange[GATHER] => partialrows:bigint, fragment:varbinary
     - TableWriter => [partialrows:bigint, fragment:varbinary]
     c1 := c1
     c2 := c2
     c3 := c3
     c4 := c4
     c5 := c5
     - Exchange[REPARTITION] => c1:bigint, c2:varchar, c3:double,
     c4:boolean, c5:varbinary
     -
     TableScan[qgremote:default:temp_a1cd2570_d4ef_4b38_b3ed_00000000014f_e
     xec, originalConstraint = true] => [c1:bigint, c2:varchar,
     c3:double, c4:boolean, c5:varbinary]
     LAYOUT:
     com.teradata.querygrid.qgc.presto.QGRemoteTableLayoutHandle@6029ca45
     c1 := {name = c1, type = bigint}
     c2 := {name = c2, type = varchar}
     c3 := {name = c3, type = double}
     c4 := {name = c4, type = boolean}
     c5 := {name = c5, type = varbinary}
    ]
     <-- END EXPLAIN FOR REMOTE QUERY >
     The size of Spool 2 is estimated with high confidence to be 1 row
     (29 bytes).  The estimated time for this step is 0.03 seconds.
  3) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
     an all-rows scan into Spool 3 (Last Use), which is built locally
     on the AMPs.  The size of Spool 3 (Last Use) is estimated with
     high confidence to be 1 row (41 bytes).  The estimated time for
     this step is 0.16 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.