Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 03 Jun 2004 @ 14:01:36 GMT


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


Subj:   Re: Performance issue while creating View
 
From:   Dieter Noeth

Shilpa Ramlal Bateja wrote:

  We are trying to create this view,  


     in which FP_CARR_INV ---------- INV_ID (Primary index) and SBU_ID  (secondary index)
     FP_CARR_PYMT ------------------ INV_ID (Primary index) and SBU_ID  (secondary index)
     FP_SHP_ORD_LN_REORG ------ MSTR_BOL ,MATL_ID  (Primary index)
  are the base views as we don't have access to use the tables directly,  


Those views are just "SEL * FROM base_table"?


  Teradata is taking almost 1 hr 20 mins in creating this view please suggest me some good way of increasing the performance.  


Could you provide more details?

RowCounts and a HELP STATISTICS for each table and EXPLAIN.

Some remarks:

- Secondary indexes on SBU_ID will not be used in Joins.

     dev_vws.FP_CARR_INV as FP_CARR_INV Left Outer Join
             dev_vws.FP_CARR_PYMT as FP_CARR_PYMT on
             FP_CARR_INV.INV_ID = FP_CARR_PYMT.INV_ID and
             FP_CARR_INV.SBU_ID = FP_CARR_PYMT.SBU_ID
     Where  Current_date between FP_CARR_INV.EFF_DT and FP_CARR_INV.EXP_DT
             and Current_date between FP_CARR_PYMT.EFF_DT and
     FP_CARR_PYMT.EXP_DT

This is no Outer Join, because you're using the Inner table in Where -> the optimizer will treat it as Inner Join.


Dieter



     
  <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