Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 31 Oct 2015 @ 15:35:07 GMT


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


Subj:   Re: Multiple single-column SI vs Single Multi-column SI
 
From:   Myrna, James

A query may still use a composite secondary index even if all of the columns are not specified in the WHERE clause. If the optimizer believes it is better to scan the index and retrieve row id's rather than to scan the entire table (collecting stats are important for the columns), then the index can still be used. Also, if the secondary index covers the query, then no access to the base table is required.

Explanation
--------------------------------------------------
 
  3)We do an all-AMPs RETRIEVE step from EMPLOYEE by way of a traversal of index # 4 extracting row ids only with a residual condition of ("EMPLOYEE.LST_NME = 'SMITH'") into Spool 2 (all_amps) (compressed columns allowed), which is built locally on the AMPs. Then we do a SORT to order Spool 2 by row id eliminating duplicate rows. The size of Spool 2 is estimated with high confidence to be 125 rows. The estimated time for this step is 0.53 seconds.  
  4)We do an all-AMPs RETRIEVE step from EMPLOYEE by way of row ids from Spool 2 (Last Use) with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 30,956 rows (24,888,624 bytes). The estimated time for this step is 0.54 seconds.  


Regards,

Jim



     
  <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: 23 Jun 2019