Explain Plan

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.

Information Known to Optimizer

• The estimated sized of data sets.
• Confidence level of data size estimates.
• Estimated processing Times.
• Locks placed on database object.
• Join techniques used to resolve the query plan.
• The order in which tables are joined and aggregations are performed.
• Whether row are accessed from source tables by primary index value by secondary index lookup by partition or by full- table scan.

Pusdo Lock
We know that each AMP holds a portion of a table. We also know that when a Full Table Scan is performed that each AMP will read their portion of the table. Now suppose that two different users wants to place multiple locks on the same table and one user gets one lock and the other user gets another lock. Both user requires lock made by other user and have to wait for indefinite time to acquire that lock because actually both the users are waiting for each other to release lock. This is called DEADLOCK.

When a user does an All-AMP operation Teradata will assign a single AMP to command the other AMPs to lock the table. We can call this AMP as the “Gatekeeper” AMP. This AMP will always be responsible for locking that particular table on all AMPs. Now all the users running an all AMP query on the table have to report to this “Gatekeeper” AMP for getting permission on locks.

The “Gatekeeper” AMP never plays favorites and performs the locking on a First Come First Serve basis. The first user to run the query will get the lock. The others will have to wait. In this way Teradata prevents the deadlock situation when an all AMP operation is made in the query

Teradata selects this “Gatekeeper” AMP by hashing the tablename used in the select query and then matching the hash value in the hash map. The AMP number which it gets from hash map is assigned as “Gatekeeper” AMP.

Optimizer Facts

• Cost-based Optimizer - looks for lowest cost plan

• Does not store plan - dynamically regenerates

• As data demographics change, so may plan

• Will only assign cost to steps for which there are choices

• Assigns confidence factors on row estimates

• Mature, large-table, decision-support optimization

EXPLAIN SELECT department_name
,last_name
,first_name
FROM employee INNER JOIN department
ON employee.employee_number =
department.manager_employee_number;