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>>

Attachments

Library

Quick Reference