Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 16 Jan 2004 @ 23:38:04 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Query Optimization with Inner Joins
 
From:   Vikram Singh

Hi

Excuse me, I missed the EXPLAIN statement

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DATABASE_1."pseudo table" for read on a RowHash to prevent global deadlock for DATABASE_1.temp.  
  2)Next, we lock a distinct DATABASE_1."pseudo table" for read on a RowHash to prevent global deadlock for DATABASE_1.mission.  
  3)We lock DATABASE_1.temp for read, and we lock DATABASE_1.mission for read.  
  4)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from DATABASE_1.temp by way of an all-rows scan with a condition of ("(NOT (DATABASE_1.temp.SCHEDULE_ID IS NULL )) AND (NOT (DATABASE_1.temp.SESSION_ID IS NULL ))") into Spool 2 (all_amps), which is redistributed by hash code to all AMPs. The size of Spool 2 is estimated with no confidence to be 114 rows. The estimated time for this step is 0.03 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from DATABASE_1.mission by way of an all-rows scan with no residual conditions into Spool 3 (all_amps), which is redistributed by hash code to all AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 3 is estimated with low confidence to be 23,299,420 rows. The estimated time for this step is 1 minute and 29 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 Spool 3 (Last Use). Spool 2 and Spool 3 are joined using a product join, with a join condition of ("(SESSION_ID = SESSION_ID) AND (SCHEDULE_ID = SCHEDULE_ID)"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 550,272 rows. The estimated time for this step is 26.73 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 1 minute and 56 seconds.  


Regards

Vikram



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016