Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

# Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Previous << 1 .. 188 189 190 191 192 193 < 194 > 195 196 197 198 199 200 .. 241 >> Next

generates the response:

Explanation

1) First, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.employee.

2) Next, we lock PERSONNEL.employee for read.

3) We do an all-AMPs RETRIEVE step from PERSONNEL.employee by way of an all-rows scan with a condition of ("PERSONNEL.employee.Name = 'Smith t'") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 2 rows. The estimated time for

this step is 0.03 seconds.

-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0 hours and 0.03 seconds.

9-26

Teradata RDBMS for UNIX SQL Reference

EXPLAIN Modifier: Examples of Complex Queries

Example 3: SELECT with WHERE on Unique Secondary Index

Example 4: SELECT With WHERE Based on a Join

Assume that the Employee table has another column (SocSecNo), and that SocSecNo is defined as a unique secondary index. If the WHERE condition is based on this column, then the EXPLAIN modifier that precedes this request:

EXPLAIN SELECT Name, EmpNo FROM Employee WHERE SocSecNo = '123456789';

generates this response:

Explanation

1) First, we do a two-AMP RETRIEVE step from

PERSONNEL.Employee by way of unique index # 2 0 "PERSONNEL.Employee.socSecNo = 123456789" with no residual conditions. The estimated time for this step is 0.09 seconds.

-> The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.09 seconds.

Here, the WHERE clause defines an equality constraint that governs a join. The rows of the Department table are copied to a spool file for use in the join operation. An EXPLAIN modifier preceding the request, as follows:

EXPLAIN SELECT DeptName, Name FROM Employee, Department WHERE Employee.EmpNo = Department.MgrNo ;

produces the following response:

Explanation

1) First, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.department.

2) Next, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.employee.

3) We lock PERSONNEL.department for read, and we lock PERSONNEL.employee for read.

4) We do an all-AMPs RETRIEVE step from PERSONNEL.department by way of an all-rows scan with no residual conditions into Spool 2, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated to be 8 rows. The estimated time for this step is 0.11 seconds.

5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to PERSONNEL.employee. Spool 2 and PERSONNEL.employee are joined using a merge join, with a join condition of ("PERSONNEL.employee.EmpNo = Spool_2.MgrNo"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 8 rows. The estimated time

for this step is 0.07 seconds.

-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0 hours and 0.19 seconds.

Teradata RDBMS for UNIX SQL Reference

9-27

EXPLAIN Modifier: Examples of Complex Queries

Example 5: SELECT With WHERE Based on Subquery

Here, the constraint that governs the join is defined by a subquery, and the ORDER BY clause specifies a sorted result. If the request is preceded by the following EXPLAIN:

EXPLAIN SELECT Name, EmpNo FROM Employee

WHERE EmpNo IN (SELECT EmpNo FROM Charges)

ORDER BY Name ;

The result returned is:

Explanation

1) First, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.charges.

2) Next, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.employee.

3) We lock PERSONNEL.charges for read, and we lock PERSONNEL.employee