Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 17 Jun 2003 @ 13:57:11 GMT


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


Subj:   Performance Analyze
 
From:   Villeneuve Philippe

Hello,

Sorry, I am new to Teradata and I would like to know i the following request is good or not on big tables (~ 10 000 000 rows) (I can't test for the moment, I still don't have the production datas) :

select
DATE_ARRT_INFO,CODE_GUIC,CODE_ORGN_FINN,REFR_PRDT,NUMR_ENTT_TITL,....
from DW_SUIV_PRDT_COLT where
DATE_ARRT_INFO >=(select ('01/06/2003' (date, format 'DD/MM/YYYY')) -

INTERVAL '6' MONTH )

Primary index of this table :

DATE_ARRT_INFO, CODE_ORGN_FINN, CODE_GUIC, CODE_PRDT_SERV, REFR_PRDT

explain :

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DBRVCE1."pseudo table" for read on a RowHash to prevent global deadlock for DBRVCE1.DW_SUIV_PRDT_COLT.  
  2)Next, we lock DBRVCE1.DW_SUIV_PRDT_COLT for read.  
  3)We do an INSERT into Spool 3.  
  4)We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated with high confidence to be 1 row. The estimated time for this step is 0.17 seconds.  
  5)We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 5, which is duplicated on all AMPs. The size of Spool 5 is estimated with high confidence to be 28 rows. The estimated time for this step is 0.17 seconds.  
  6)We do an all-AMPs JOIN step from DBRVCE1.DW_SUIV_PRDT_COLT by way of an all-rows scan with no residual conditions, which is joined to Spool 5 (Last Use). DBRVCE1.DW_SUIV_PRDT_COLT and Spool 5 are joined using a product join, with a join condition of ("DBRVCE1.DW_SUIV_PRDT_COLT.DATE_ARRT_INFO >= Field_2"). The input table DBRVCE1.DW_SUIV_PRDT_COLT will not be cached in memory, but it is eligible for synchronized scanning. 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 1,696,838 to 5,090,512 rows. The estimated time for this step is 1 hour and 37 minutes.  
  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.  


Have I to add a secondy index on the DATE_ARRT_INFO field?


Thanks in advance for your help

Bests regards,

Philippe VILLENEUVE



     
  <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: 15 Jun 2023