Performance Analysis on Customer_service Database Using EXPLAIN Statement
 

• Simple Queries without Join
• Queries with Join
• Queries with Set operator
• Queries with Aggregation
• Queries with Nested Subqueries - Comparison with Flattened query
• Example of Parallel Processing - the Heart of Teradata Performance
• Summary and Conclusion

1. Simple Queries Without Join

• 1.1. Simple query with several row retrieval takes 4 steps:
   
  • Step 1) Lock  table.
  • Step 2) All AMP retrieve data from table to spool.
  • Step 3) End transaction.
  • Step 4) The contents of spool is sent back to user.
     
• 1.2. EXPLAIN statement in front of query returns the whole explanation how the query will be performed.

*** Help information returned. 13 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.customer.
  2) Next, we lock customer_service.customer for read.
  3) We do an all-AMPs RETRIEVE step from customer_service.customer by
     way of an all-rows scan with no residual conditions into Spool 1,
     which is built locally on the AMPs.  The size of Spool 1 is
     estimated with low confidence to be 80 rows.  The estimated time
     for this step is 0.15 seconds.
  4) 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.15 seconds.
 

• 1.3. Query with order by and condition :
  • Sorting doesn't make any difference in retrieving data from simple query. The procedure takes the same steps and time as simple query above. Since the sample database is small enough to handle all query within memory, we need to test this sorting in a bigger database with several disk I/Os to get the idea how much time sorting will take.
  • Since department_number column is not indexed, all-row scan is used to find matching rows.

  
  explain select employee_number
  from customer_service.employee
  where department_number = 401
  order by employee_number;

*** Help information returned. 15 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.employee.
  2) Next, we lock customer_service.employee for read.
  3) We do an all-AMPs RETRIEVE step from customer_service.employee by
     way of an all-rows scan with a condition of (
     "customer_service.employee.department_number = 401") into Spool 1,
     which is built locally on the AMPs.  Then we do a SORT to order
     Spool 1 by the sort key in spool field1.  The size of Spool 1 is
     estimated with no confidence to be 8 rows.  The estimated time for
     this step is 0.15 seconds.
  4) 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.15 seconds.
 
 

• 1.4. Simple query with one row retrieval for primary index : Optimizer uses only one AMP using primary index while all the queries above need all AMPs retrieval.
   

  explain select * from customer_service.employee where employee_number = 1010;

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

Explanation
------------------------------------------------------------------------
  1) First, we do a single-AMP RETRIEVE step from
     customer_service.employee by way of the unique primary index
     "customer_service.employee.employee_number = 1010" with no
     residual conditions.  The estimated time for this step is 0.03
     seconds.
  -> The row is sent directly back to the user as the result of
     statement 1.  The total estimated time is 0.03 seconds.
 

2. Queries With Join

• 2.1 A query with join takes 4 steps:
   
  • Step 1) Lock  tables.
  • Step 2) All AMP retrieve by row hash scan, and joined to the other table by merge join. The joined result is sent to spool.
  • Step 3) End transaction.
  • Step 4) The contents of spool os sent back to user.
     
• 2.2. EXPLAIN statement in front of query shows that join takes more time than simple query.
  • Before merge join, the optimizer does row hash match scan.
  • This hash partitioning of primary index ( customer_number) allows rows from Customer_service.customer table and Customer_service.location table to be placed at the same node, thus eliminates the need interconnect traffic and makes ease the join process.

*** Help information returned. 19 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.l.
  2) Next, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for customer_service.c.
  3) We lock customer_service.l for read, and we lock
     customer_service.c for read.
  4) We do an all-AMPs JOIN step from customer_service.l by way of a
  RowHash match scan with no residual conditions, which is joined to
     customer_service.c.  customer_service.l and customer_service.c are
     joined using a merge join, with a join condition of (
     "customer_service.c.customer_number =
     customer_service.l.customer_number").  The result goes into Spool
    1, which is built locally on the AMPs.  The size of Spool 1 is
     estimated with low confidence to be 80 rows.  The estimated time
     for this step is 0.18 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.18 seconds.
 
 

• 2.3. Query with join and condition
  • This query takes additional step and uses one more spool because of the condition d.budget_amount > 500000.
  • Customer_service.employee table is retrieved and redistributed by hash code to a spool 2.
  • The spool 2 is sorted to be used for join step. As the example above, join is done by merge join. With this additional step, the total time for this query slightly increased.

  
  explain select employee_number
  from customer_service.department d, customer_service.employee e
  where d.department_number=e.department_number and d.budget_amount > 500000;

  *** Help information returned. 25 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.e.
    2) Next, we lock a distinct customer_service."pseudo table" for read
       on a RowHash to prevent global deadlock for customer_service.d.
    3) We lock customer_service.e for read, and we lock
       customer_service.d for read.
    4) We do an all-AMPs RETRIEVE step from customer_service.e 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 with
       low confidence to be 80 rows.  The estimated time for this step is
       0.03 seconds.
    5) We do an all-AMPs JOIN step from customer_service.d by way of a
       RowHash match scan with a condition of (
       "customer_service.d.budget_amount > 500000.00"), which is joined
       to Spool 2 (Last Use).  customer_service.d and Spool 2 are joined
       using a merge join, with a join condition of (
       "customer_service.d.department_number = Spool_2.department_number").
       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 27 rows.
       The estimated time for this step is 0.20 seconds.
    6) 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.23 seconds.
   

•  2.4. Query with self-join(1) shows the use of unique secondary index and product join.

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.
 
 

• 2.5. Query with self-join(2) :
   
  • The difference in this example from the previous self join is the use of unique primary index.
  • As we can easily predict, using primary index reduce the performance time. (Compare step 3) of the two queries. This query takes 0.17 sec while the previous query takes 0.21 sec).
     
  explain select distinct e1.last_name, e1.first_name
  from customer_service.employee e1, customer_service.employee e2
  where e1.salary_amount > e2.salary_amount
  and e2.employee_number = 1010;

*** Help information returned. 23 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.e1.
  2) Next, we lock customer_service.e1 for read.
  3) We do a single-AMP RETRIEVE step from customer_service.e2 by way
     of the unique primary index "customer_service.e2.employee_number =
     1010" 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.17 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.e1.  Spool 2
     and customer_service.e1 are joined using a product join, with a
     join condition of ("customer_service.e1.salary_amount >
     Spool_2.salary_amount").  The result goes into Spool 1, which is
     redistributed by hash code to all AMPs.  Then we do a SORT to
     order Spool 1 by the sort key in spool field1 eliminating
     duplicate rows.  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.36 seconds.
 
 

• Example of multi join (1) - Here is one interesting example of query optimization. The optimizer uses parallel processing to reduce processiong time.

explain select e.last_name, e.first_name, ep.area_code, ep.phone
from customer_service.employee e, customer_service.employee_phone ep, customer_service.customer c
where e. employee_number = c. sales_employee_number
and e.employee_number = ep.employee_number;

 *** Help information returned. 37 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.ep.
  2) Next, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for customer_service.e.
  3) We lock a distinct customer_service."pseudo table" for read on a
     RowHash to prevent global deadlock for customer_service.c.
  4) We lock customer_service.ep for read, we lock customer_service.e
     for read, and we lock customer_service.c for read.
  5) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from customer_service.c 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 with low confidence to be 80 rows.  The
          estimated time for this step is 0.03 seconds.
       2) We do an all-AMPs JOIN step from customer_service.ep by way
          of a RowHash match scan with no residual conditions, which is
          joined to customer_service.e.  customer_service.ep and
          customer_service.e are joined using a merge join, with a join
          condition of ("customer_service.e.employee_number =
          customer_service.ep.employee_number").  The result goes into
          Spool 3, which is redistributed by hash code to all AMPs.
          Then we do a SORT to order Spool 3 by row hash.  The size of
          Spool 3 is estimated with low confidence to be 80 rows.  The
          estimated time for this step is 0.06 seconds.
  6) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 3 (Last Use).  Spool
     2 and Spool 3 are joined using a merge join, with a join condition
     of ("(Spool_3.employee_number = Spool_2.sales_employee_number) AND
     (Spool_2.sales_employee_number = Spool_3.employee_number)").  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 to 6,400
     rows.  The estimated time for this step is 0.20 seconds.
  7) 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.26 seconds.
 

• Example of multi join (2) - Not every muti join is processed in parallel. Optimizer doesn't use parallel processing in this example because customer_number in table customer_service.customer c, customer_service.location l is both primary index. This means that the join is done within the same node without the need of relocation of row, thus very effective.
   
  explain select c.customer_name, lp.area_code, lp.phone
  from customer_service.customer c, customer_service.location l,
  customer_service.location_phone lp
  where c.customer_number = l.customer_number and
  l.location_number = lp.location_number;

  
   *** Help information returned. 30 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.lp.
    2) Next, we lock a distinct customer_service."pseudo table" for read
       on a RowHash to prevent global deadlock for customer_service.l.
    3) We lock a distinct customer_service."pseudo table" for read on a
       RowHash to prevent global deadlock for customer_service.c.
    4) We lock customer_service.lp for read, we lock customer_service.l
       for read, and we lock customer_service.c for read.
    5) We do an all-AMPs JOIN step from customer_service.l by way of a
       RowHash match scan with no residual conditions, which is joined to
       customer_service.c.  customer_service.l and customer_service.c are
       joined using a merge join, with a join condition of (
       "customer_service.c.customer_number =
       customer_service.l.customer_number").  The result goes 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 with low confidence to be 80 rows.  The estimated time
       for this step is 0.06 seconds.
    6) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a
       RowHash match scan, which is joined to customer_service.lp.  Spool
       2 and customer_service.lp are joined using a merge join, with a
       join condition of ("Spool_2.location_number =
       customer_service.lp.location_number").  The result goes into Spool
       1, which is built locally on the AMPs.  The size of Spool 1 is
       estimated with index join confidence to be 6,400 rows.  The
       estimated time for this step is 0.19 seconds.
    7) 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.25 seconds.

3. Queries with Set operator

• 3.1. Query with set operator
  • The query performance for each part of the query separated by set operator is done independently, thus the performance time becomes the double of the each query.
  • Even though Teradata optimizer is super effective, it is user's share to avoid set operator as much as possible to get better performance. - see the other example query in 3.2 for comparison.

explain (select employee_number
from customer_service.employee
where department_number = 401)
UNION
(select employee_number
from customer_service.employee
where department_number = 403 );

*** 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.employee.
  2) Next, we lock customer_service.employee for read.
  3) We do an all-AMPs RETRIEVE step from customer_service.employee by
     way of an all-rows scan with a condition of (
     "customer_service.employee.department_number = 401") into Spool 1,
     which is built locally on the AMPs.  The size of Spool 1 is
     estimated with no confidence to be 8 rows.  The estimated time for
     this step is 0.15 seconds.
  4) We do an all-AMPs RETRIEVE step from customer_service.employee by
     way of an all-rows scan with a condition of (
     "customer_service.employee.department_number = 403") into Spool 1,
     which is built locally on the AMPs.  Then we do a SORT to order
     Spool 1 by the sort key in spool field1 eliminating duplicate rows.
     The size of Spool 1 is estimated with no confidence to be 8 rows.
     The estimated time for this step is 0.15 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.31 seconds.
 
 

• 3.2. Query without set operator : This query is just the same as the query in 3.1. with set operator. It's obvious that using set operator is not efficient when you compare the processing time.  Again, it's user's responsibilty to write an efficient query no matter how the system is good.
   

   explain select employee_number
   from customer_service.employee
   where department_number = 401 or department_number = 403
   order by employee_number;

 *** Help information returned. 16 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.employee.
  2) Next, we lock customer_service.employee for read.
  3) We do an all-AMPs RETRIEVE step from customer_service.employee by
     way of an all-rows scan with a condition of (
     "(customer_service.employee.department_number = 401) OR
     (customer_service.employee.department_number = 403)") into Spool 1,
     which is built locally on the AMPs.  Then we do a SORT to order
     Spool 1 by the sort key in spool field1.  The size of Spool 1 is
     estimated with no confidence to be 16 rows.  The estimated time
     for this step is 0.15 seconds.
  4) 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.15 seconds.

4. Queries with Aggregation
 

• 4.1. Plain Aggregation is doing almost as same as plain query. The performance time is just slightly increased for this sample database. Sorting is not a  big problem for Teradata, however, we can not jump into conclusion until we try a really big database with many disk I/Os.

explain select  sum(budget_amount), avg(budget_amount)
from customer_service.department;

*** Help information returned. 16 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.department.
  2) Next, we lock customer_service.department for read.
  3) We do a SUM step to aggregate from customer_service.department by
     way of an all-rows scan with no residual conditions.  Aggregate
     Intermediate Results are computed globally, then placed in Spool 2.
     The size of Spool 2 is estimated to be 1 row.
  4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
     an all-rows scan into Spool 1, which is built locally on the AMPs.
     The size of Spool 1 is estimated with high confidence to be 1 row.
     The estimated time for this step is 0.17 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.
 
 

• 4.2.  Aggregation with Group by

explain select d.department_name, max(salary_amount)
from customer_service.department d, customer_service.employee e
where d.department_number = e.department_number
group by d.department_name;

*** Help information returned. 32 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.e.
  2) Next, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for customer_service.d.
  3) We lock customer_service.e for read, and we lock
     customer_service.d for read.
  4) We do an all-AMPs RETRIEVE step from customer_service.e by way of
     an all-rows scan with no residual conditions into Spool 3, which
     is redistributed by hash code to all AMPs.  Then we do a SORT to
     order Spool 3 by row hash.  The size of Spool 3 is estimated with
     low confidence to be 80 rows.  The estimated time for this step is
    0.03 seconds.
  5) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a
     RowHash match scan, which is joined to customer_service.d.  Spool
     3 and customer_service.d are joined using a merge join, with a
     join condition of ("customer_service.d.department_number =
     Spool_3.department_number").  The result goes into Spool 2, which
     is built locally on the AMPs.  The size of Spool 2 is estimated
     with index join confidence to be 80 rows.  The estimated time for
     this step is 0.20 seconds.
  6) We do a SUM step to aggregate from Spool 2 (Last Use) by way of an
     all-rows scan, and the grouping identifier in field 2.  Aggregate
     Intermediate Results are computed globally, then placed in Spool 4.
     The size of Spool 4 is estimated to be 80 rows.
  7) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
     an all-rows scan into Spool 1, which is built locally on the AMPs.
     The size of Spool 1 is estimated with low confidence to be 80 rows.
     The estimated time for this step is 0.17 seconds.
  8) 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.
 
 

• 4.3. Aggregation with Having clause
   
  • Having clause doesn't add any performance time.

  
  explain select d.department_name, avg(salary_amount) a
  from customer_service.department d, customer_service.employee e
  where d.department_number = e.department_number
  group by d.department_name
  having a>30000;

*** Help information returned. 34 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.e.
  2) Next, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for customer_service.d.
  3) We lock customer_service.e for read, and we lock
     customer_service.d for read.
  4) We do an all-AMPs RETRIEVE step from customer_service.e by way of
     an all-rows scan with no residual conditions into Spool 3, which
     is redistributed by hash code to all AMPs.  Then we do a SORT to
     order Spool 3 by row hash.  The size of Spool 3 is estimated with
     low confidence to be 80 rows.  The estimated time for this step is
  0.03 seconds.
  5) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a
     RowHash match scan, which is joined to customer_service.d.  Spool
     3 and customer_service.d are joined using a merge join, with a
     join condition of ("customer_service.d.department_number =
     Spool_3.department_number").  The result goes into Spool 2, which
     is built locally on the AMPs.  The size of Spool 2 is estimated
     with index join confidence to be 80 rows.  The estimated time for
     this step is 0.20 seconds.
  6) We do a SUM step to aggregate from Spool 2 (Last Use) by way of an
     all-rows scan, and the grouping identifier in field 2.  Aggregate
     Intermediate Results are computed globally, then placed in Spool 4.
     The size of Spool 4 is estimated to be 80 rows.
  7) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
    an all-rows scan with a condition of ("((Spool_4.Field_3 )/
     (Spool_4.Field_4 ))> 3.00000000000000E 004") into Spool 1, which
     is built locally on the AMPs.  The size of Spool 1 is estimated
     with low confidence to be 80 rows.  The estimated time for this
     step is 0.17 seconds.
  8) 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.
 

5. Queries with Nested Subqueries - Comparison with Flattened query

• 5.1. Non correlated, Without Aggregation Subquery
   
  • The system handles the query as it is. In other words, if the query has a subquery, the optimizer performs the main query first and pause to get the subquery is done, and integrate the whole query afterwards.
  • For comparison, I posted the same query with flattened form ( without subquery) below.

  
  explain select customer_name
  from customer_service.customer
  where sales_employee_number in (select employee_number
                                                            from customer_service.employee
                                                            where hire_date > '77/03/01');

*** Help information returned. 28 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.employee.
  2) Next, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for
     customer_service.customer.
  3) We lock customer_service.employee for read, and we lock
     customer_service.customer for read.
  4) We do an all-AMPs RETRIEVE step from customer_service.customer 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 with low confidence to be 80 rows.  The estimated time
     for this step is 0.03 seconds.
  5) 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.employee with a
     condition of ("customer_service.employee.hire_date > DATE
     '1977-03-01'").  Spool 2 and customer_service.employee are joined
     using an inclusion merge join, with a join condition of (
     "Spool_2.sales_employee_number =
     customer_service.employee.employee_number").  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 27 rows.  The estimated time
     for this step is 0.20 seconds.
  6) 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.23 seconds.
 

• 5.2. Flattened form of the previous subquery
   
  • When compared the previous query, this flattened query takes less time.
  • Again, it is user's responsibility to make the performance effective by now using subquery as much as possible.

  
  explain select customer_name
  from customer_service.customer c, customer_service.employee e
  where c.sales_employee_number = e.employee_number
  and hire_date > '77/03/01';

*** Help information returned. 26 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.e.
2) Next, we lock a distinct customer_service."pseudo table" for read
   on a RowHash to prevent global deadlock for customer_service.c.
3) We lock customer_service.e for read, and we lock
   customer_service.c for read.
4) We do an all-AMPs RETRIEVE step from customer_service.c 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 with
   low confidence to be 80 rows.The estimated time for this step is
  0.03 seconds.
5) We do an all-AMPs JOIN step from customer_service.e by way of a
   RowHash match scan with a condition of (
   "customer_service.e.hire_date > DATE '1977-03-01'"), which is
   joined to Spool 2 (Last Use).customer_service.e and Spool 2 are
   joined using a merge join, with a join condition of (
   "Spool_2.sales_employee_number =
   customer_service.e.employee_number").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 27 rows.The estimated time
   for this step is 0.18 seconds.
6) 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.21 seconds.

6. Example of Parallel Processing - the Heart of Teradata Performance

• The most great feature of Teradata RDBMS is the parallelism.
• As query becomes complex and needs multiple tables, the more parallelism is used for efficient performance.
• Here is an example of how the system use parallel processing. In step 5) you can see the processes are independently performed in parallel.

explain select e.last_name, e.first_name, ep.area_code, ep.phone
from customer_service.employee e, customer_service.employee_phone ep,
customer_service.customer c
where e. employee_number = c. sales_employee_number
and e.employee_number = ep.employee_number
and c.customer_name = 'First American Bank';

*** Help information returned. 38 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.ep.
  2) Next, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for customer_service.e.
  3) We lock a distinct customer_service."pseudo table" for read on a
     RowHash to prevent global deadlock for customer_service.c.
  4) We lock customer_service.ep for read, we lock customer_service.e
     for read, and we lock customer_service.c for read.
  5) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from customer_service.c by
          way of an all-rows scan with a condition of (
          "customer_service.c.customer_name = 'First American Bank'")
          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 with no confidence to be 8 rows.  The
          estimated time for this step is 0.03 seconds.
       2) We do an all-AMPs JOIN step from customer_service.ep by way
          of a RowHash match scan with no residual conditions, which is
          joined to customer_service.e.  customer_service.ep and
          customer_service.e are joined using a merge join, with a join
          condition of ("customer_service.e.employee_number =
          customer_service.ep.employee_number").  The result goes into
          Spool 3, which is redistributed by hash code to all AMPs.
          Then we do a SORT to order Spool 3 by row hash.  The size of
          Spool 3 is estimated with low confidence to be 80 rows.  The
          estimated time for this step is 0.06 seconds.
  6) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 3 (Last Use).  Spool
     2 and Spool 3 are joined using a merge join, with a join condition
     of ("(Spool_3.employee_number = Spool_2.sales_employee_number) AND
     (Spool_2.sales_employee_number = Spool_3.employee_number)").  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 8 to 640
     rows.  The estimated time for this step is 0.20 seconds.
  7) 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.26 seconds.
 

• When one row retrieval is required, one AMP is used. Otherwise all  AMPs retrieve data from cache or disk.

• The most great feature of Teradata RDBMS is the parallelism. As query becomes complex and needs multiple tables, the more parallelism is used for efficient performance.

• The most common procedure is

• Step 1) Lock  table.
• Step 2) All AMP retrieve data from table to spool.
• Step 3) End transaction.
• Step 4) The contents of spool is sent back to user.

• Query with join

• A query with join usually takes 4 steps:
   
  • Step 1) Lock  tables.
  • Step 2) All AMP retrieve by row hash scan, and joined to the other table by merge join. The joined result is sent to spool.
  • Step 3) End transaction.
  • Step 4) The contents of spool os sent back to user.
     
• When the two tables are indexed with primary index, the row are placed on  the same node, thus  the need interconnect traffic is eliminated and the join becomes facilitated.
   
• Query with join and condition
  • This query takes additional step and uses one more spool. One spool is retrieved for the rows that satisfy the condition. Let's say spool 2.
  • The spool 2 is sorted to be used for join step. With this additional step, the total time for this query slightly increased.

•  Query with self-join uses product join.

• For multi join, usually the optimizer uses parallel processing to reduce processing time. Since the total time depends on the latest procedure, it is very important to distribute the rows evenly across the AMPs. Teradata achieve this using sophiscated hashing algorithm.

• Query with set operator

• The query performance for each part of the query separated by set operator is done independently, thus the performance time becomes the double of the each query.
• Even though Teradata optimizer is super effective, it is user's share to avoid set operator as much as possible to get better performance.
• Plain Aggregation is doing almost as same as plain query. The performance time is just slightly increased for this sample database. Sorting is not a  big problem for Teradata, however, we can not jump into conclusion until we try a really big database with many disk I/Os.
• Handling subquery
   
  • Non correlated, Without Aggregation Subquery
     
    • The system handles the query as it is. In other words, if the query has a subquery, the optimizer performs the main query first and pause to get the subquery is done, and integrate the whole query afterwards.
    • When compared the previous query which has a subquery, this flattened query takes less time.
    • Again, it is user's responsibility to make the performance effective by now using subquery as much as possible.
       
  • Correlated subquery
  Usually subquery takes more time to perform, but there are many complex queries that inevitably needs subqueries. We can flatten correlated subquery using temporary table. For conventional databases, using temporary table enhances the performance, but for Teradata, Correlated subquery performs better.