次の例では、Teradataシステム上でOracleターゲット コネクタによってサポートされているEXPLAIN SELECTおよびEXPLAIN INSERTコマンドを使用します。
Explain Select
explain select * from SIMBATERADATA.T_BASIC@oraclefs_active;
*** Help information returned. 34 rows.
*** Total elapsed time was 15 seconds.
Explanation
---------------------------------------------------------------------------
1) First, we do an all-AMPs RETRIEVE step executing table operator
TD_SYSFNLIB.QGINITIATORIMPORT in TD_MAP1 with a condition of (
"(1=1)").
< BEGIN EXPLAIN FOR REMOTE QUERY -->
Remote Queries:
1. EXPLAIN PLAN SET STATEMENT_ID = '00000000006b' FOR SELECT id
,c1 FROM SIMBATERADATA.T_BASIC
2. [Statement_ID=00000000006b, Plan_ID=375, Timestamp=26-APR-18,
Remarks=, Operation=SELECT STATEMENT, Options=, Object_Node=,
Object_Owner=, Object_Name=, Object_Alias=, Object_Instance=,
Object_Type=, Optimizer=ALL_ROWS, Search_Columns=, ID=0,
Parent_ID=, Depth=0, Position=3, Cost=3, Cardinality=9, Bytes=117,
Other_tag=, Partition_Start=, Partition_Stop=, Partition_ID=,
Distribution=, Cpu_Cost=37137, IO_Cost=3, Temp_Space=,
Access_Predicates=, Filter_Predicates=, Projection=, Time=1,
Qblock_name=, Statement_ID=00000000006b, Plan_ID=375,
Timestamp=26-APR-18, Remarks=, Operation=TABLE ACCESS,
Options=FULL, Object_Node=, Object_Owner=SIMBATERADATA,
Object_Name=T_BASIC, Object_Alias=T_BASIC@SEL$1,
Object_Instance=1, Object_Type=TABLE, Optimizer=ANALYZED,
Search_Columns=, ID=1, Parent_ID=0, Depth=1, Position=1, Cost=3,
Cardinality=9, Bytes=117, Other_tag=, Partition_Start=,
Partition_Stop=, Partition_ID=, Distribution=, Cpu_Cost=37137,
IO_Cost=3, Temp_Space=, Access_Predicates=, Filter_Predicates=,
Projection="ID"[NUMBER,22], "C1"[VARCHAR2,100], Time=1,
Qblock_name=SEL$1]
<-- END EXPLAIN FOR REMOTE QUERY >
The size of Spool 2 is estimated with no confidence to be 40,000
rows (6,440,000 bytes). The estimated time for this step is 0.07
seconds.
2) 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.07 seconds.
Explain Insert
EXPLAIN INSERT INTO SIMBATERADATA.T_BASIC@oraclefs_active(100, 'againthruQG');
*** Help information returned. 34 rows.
*** Total elapsed time was 15 seconds.
Explanation
---------------------------------------------------------------------------
1) First, we do an INSERT step into Spool 5.
2) Next, we do a single-AMP RETRIEVE step from Spool 5 (Last Use) by
way of an all-rows scan executing table operator
TD_SYSFNLIB.QGINITIATOREXPORT in TD_MAP1 with a condition of (
"(1=1)") into Spool 3 (used to materialize view, derived table,
table function or table operator T_BASIC) (all_amps), which is
built locally on that AMP.
< BEGIN EXPLAIN FOR REMOTE QUERY -->
Remote Queries:
1. EXPLAIN PLAN SET STATEMENT_ID = '00000000002c' FOR INSERT INTO
T_BASIC VALUES (?,?)
2. [Statement_ID=00000000002c, Plan_ID=401, Timestamp=14-MAY-18,
Remarks=, Operation=INSERT STATEMENT, Options=, Object_Node=,
Object_Owner=, Object_Name=, Object_Alias=, Object_Instance=,
Object_Type=, Optimizer=ALL_ROWS, Search_Columns=, ID=0,
Parent_ID=, Depth=0, Position=1, Cost=1, Cardinality=1, Bytes=13,
Other_tag=, Partition_Start=, Partition_Stop=, Partition_ID=,
Distribution=, Cpu_Cost=0, IO_Cost=1, Temp_Space=,
Access_Predicates=, Filter_Predicates=, Projection=, Time=1,
Qblock_name=,Statement_ID=00000000002c, Plan_ID=401,
Timestamp=14-MAY-18, Remarks=, Operation=LOAD TABLE CONVENTIONAL,
Options=, Object_Node=, Object_Owner=SIMBATERADATA,
Object_Name=T_BASIC, Object_Alias=, Object_Instance=,
Object_Type=, Optimizer=, Search_Columns=, ID=1, Parent_ID=0,
Depth=1, Position=1, Cost=, Cardinality=, Bytes=, Other_tag=,
Partition_Start=, Partition_Stop=, Partition_ID=, Distribution=,
Cpu_Cost=, IO_Cost=, Temp_Space=, Access_Predicates=,
Filter_Predicates=, Projection=, Time=, Qblock_name=INS$1]
<-- END EXPLAIN FOR REMOTE QUERY >
The size of Spool 3 is estimated with high confidence to be 1 row
(71 bytes). The estimated time for this step is 0.03 seconds.
3) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 3 (Last Use)
by way of an all-rows scan into Spool 6 (Last Use), which is built
locally on the AMPs. The size of Spool 6 (Last Use) is estimated
with high confidence to be 1 row (64,029 bytes). The estimated
time for this step is 0.08 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.