EXPLAIN Statement

• EXPLAIN statement allows you to preview various results to the same query without excuting the query.
• The EXPLAIN statement in front of any SQL statement generates a full parsed and optimized request that is not executed.
• It returns estimated rows involved, relative time, and what index the optimizer use.

• Example of the usage

EXPLAIN select b.department_name
 from customer_service.department a, customer_service.department b
 where a.budget_amount > b.budget_amount and
a.department_name = 'research and development';
 

*** Help information returned. 21 rows.
 *** Total elapsed time was 1 second.

Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct customer_service."pseudo table" for read  on a
      RowHash to prevent global deadlock for customer_service.b.
  2) Next, we lock customer_service.b for read.
  3) We do a two-AMP RETRIEVE step from customer_service.a by way of
     unique index # 4 "customer_service.a.department_name = 'research
     and development'" with no residual conditions into Spool 2, which
     is duplicated on all AMPs.  The size of Spool 2 is estimated with
     high confidence to be 80 to 6,400 rows.  The estimated time for
     this step is 0.21 seconds.
  4) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
     all-rows scan, which is joined to customer_service.b.  Spool 2 and
     customer_service.b are joined using a product join, with a join
     condition of ("Spool_2.budget_amount >
     customer_service.b.budget_amount").  The result goes into Spool 1,
     which is built locally on the AMPs.  The size of Spool 1 is
     estimated with no confidence to be 80 rows.  The estimated time
     for this step is 0.20 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.40 seconds.