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
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.