Hi, We have the below explain plan for inserting into a global temp table. i have few questions on the same. Kindly help me understanding the same.
1) what does "pseudo table" mean ? is this a temp table create for global temp table ? 2) what does DBC.TVM,DBC.DBase mean ? 3) i do not see any insert into the global temprary table. can someone explain how data is inerted into global temporary table
1) First, we lock a distinct AB28880."pseudo table" for exclusive use on a RowHash to prevent global deadlock for AB28880.sample_MACRO. 2) Next, we lock AB28880.sample_MACRO for exclusive use. 3) We lock DBC.TVM for write on a RowHash. 4) We execute the following steps in parallel. 1) We do a single-AMP ABORT test from DBC.DBase by way of the unique primary index. 2) We do a single-AMP ABORT test from DBC.TVM by way of the unique primary index. 3) We do a single-AMP DELETE from DBC.TVFields by way of the primary index with no residual conditions. 4) We do a single-AMP RETRIEVE step from DBC.TVM by way of the unique primary index with no residual conditions into Spool 1 (group_amps), which is redistributed by hash code to all AMPs. 5) We do a single-AMP DELETE from DBC.TVM by way of the unique primary index with no residual conditions. 5) We do a group-AMP MERGE into DBC.TVM from Spool 1 (Last Use). 6) We spoil the parser's dictionary cache for the table. 7) 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.
Psuedo tables are dummy tables that sit on all AMPs.Pseudo tables store the Table ID Hash codes of database objects which are involved in ALL-Amp operations.Whenever a ALL-AMP operation is performed on a database object, a Row-Hash lock is placed on psuedo table to prevent the deadlock situation(if we have another user submitted another request on same object).
Looking at the explain,looks like you are performing a DDL change through a macro.DBC.TVM and DBC.Dbase are dictionary tables that store the meta data about the database objects Tables/views/macros. Whenever a database object is created/deleted/modified these dictionary tables are updated.
Learning to understand the EXPLAIN output is like learning a foriegn language (example: Japanese...assuming you aren't Japanese!).
And the task becomes relatively easy if you start EXPLAINing small SQL's before progressing to the big ones. The SQL you tried to EXPLAIN must have left you cross eyed...
If you are trying to learn the art of interpreting this language, I recommend you read the standard reference manuals as to how SQLs are processed internally. After you do this you could experiment and observe the EXPLAIN output for SQL's similar to the ones below.