EXPLAIN

EXPLAIN command returns the execution plan of parsing engine in English. It can be used with any SQL statement except on another EXPLAIN command. When a query is preceded with EXPLAIN command, the execution plan of the Parsing Engine is returned to the user instead of AMPs

Some Explain plans are:

  • Full Table Scan (FTS) – each and every row of the table is accessed (Example when there is no where condition)
  • Unique Primary Index – When the rows are accessed using Unique Primary Index, then it is one AMP operation
  • Unique Secondary Index – When the rows are accessed using Unique Secondary Index, it’s a two amp operation.

For more information refer primary vs secondary index

Command example:

EXPLAIN (provide select statement here with where condition, etc)

More details from the Explain plan

  • (Last Use) – A spool file is no longer needed and will be released when this step completes.
  • with no residual conditions – All applicable conditions have been applied to the rows.
  • END TRANSACTION – Transaction locks are released, and changes are committed.
  • eliminating duplicate rows -Duplicate rows only exist in spool files, not set tables. Doing a DISTINCT operation.
  • by way of a traversal of index #n extracting row ids only – A spool file is built containing the Row IDs found in a secondary index (index #n)
  • we do a SMS (set manipulation step) – Combining rows using a UNION, MINUS, or INTERSECT operator.
  • which is redistributed by hash code to all AMPs. –  Redistributing data in preparation for a join.
  • which is duplicated on all AMPs. – Duplicating data from the smaller table (in terms of SPOOL) in preparation for a join.
  • (one_AMP) or (group_AMPs) – Indicates one AMP or subset of AMPs will be used instead of all AMPs.

Leave a Reply

Your email address will not be published. Required fields are marked *