How it works...

English text will be generated, showing the path of the query. Different queries have different keywords. It is the user who needs to understand based on the plan as it is optimized or not by looking at the keywords.

Pseudo table: Understand it like a conductor of a music concert. One of the AMPs from the all-AMP operation is declared as a conductor, who control other AMPs and acts as a gatekeeper. This helps in preventing the deadlocks when two users issue the same locks:

1. We lock a distinct test01."pseudo table" for write on a RowHash to prevent global deadlock for test01.webclicks:
   • All-AMPs retrieve: A full table scan is done and all the blocks on the tables are retrieved.
2. We do an all-AMPs RETRIEVE step from au.az_clicks by way of an all-rows scan with no residual conditions into Spool 1 (all_AMP):
   • No residual conditions: The condition in the where clause has been applied to the rows.
   • Way of the unique primary index: A single-AMP retrieve and UPI is used to access the row. web_page, being the following UPI column.
3. We do a single-AMP RETRIEVE step from oz.web_clicks by way of the unique primary index "oz.web_clicks.web_page = 70", with no residual conditions. The estimated time for this step is 0.04 seconds:
   • By way of index #4: Rows are read using the Secondary Index – the number from the HELP INDEX. If SI is unique then by way of unique index.
4. We do a two-AMP RETRIEVE step from oz.web_clicks by way of unique index #4, "oz.web_clicks.adv_number = 10", with no residual conditions. The estimated time for this step is 0.01 seconds. The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.01 seconds:
   • Eliminating duplicate rows: Eliminating duplicates from SPOOL files. Retrieving only unique values, normally, as a result of DISTINCT, GROUP BY or a subquery. 

1. Redistributed by hash code to all AMPs. The result rows are put into Spool 1 (all_AMPs), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field 1, eliminating duplicate rows. The size is estimated with index join confidence to be 1,732 rows (155,045 bytes), which is duplicated on all AMPs or is redistributed by hash code.
   • Duplicating data from the table (should be a smaller one) in preparation for a join: If you see a large table getting duplicated, try collecting stats.
2. We do an all-AMPs RETRIEVE step from Spool 10 by way of an all-rows scan into Spool 3 (all_AMPs), compressed columns allowed, which is duplicated on all AMPs:
   • STAT function: A query with any order analytical functions such as TOP, RANK, OVER, and so on will result in a STAT function in the explain plan. Usually, you will see them at the end of the explain plan. 

1. We do an all-AMPs STAT FUNCTION step from test01.tbl1 by way of an all-rows scan with no residual conditions into Spool 2, which is built locally on the AMPs. The result rows are put into Spool 1 (group_AMPs), which is built locally on the AMPs:
   • Unmatched data types: A TRANSLATE function in the explain plan identifies un-matched data types.
2. Spool 5 is left as outer joined using a merge join, with a join condition of ("(TR_DATE <= END_DT) AND ((TRANSLATE((ACCT_NUM )USING LATIN_TO_UNICODE)(FLOAT, FORMAT '-9.99999999999999E-999')) (ACCT_NUMBER ))"):
   • Why are Translations bad? The Translate function, by itself, can be quite expensive when large volumes of data are involved. Demographics/collect stats cannot be used when the data types are not matched; the optimizer may not produce good estimates.