Partition Join Performance problem

From: BISWAS SATYAJIT <ext2szb_at_ups.com>
Date: Thu, 15 Mar 2001 21:14:39 +0000 (UTC)
Message-ID: <177CDFBDC421D4119F1B0008C7CFA22F03F53537@02usnjrarps1c30.win.us.ups.com>

I am facing some preformance problem in of my application (Datamart) . I am trying to join two big fact table. Both of these fact table are partitioned three way (Region,year,month) and they have two year worth of data.Both of these table have 81 partitions each. In my query I am inserting data from fact table A into Fact table B. I am trying to insert record in table B from table A the ones which are not there in table B. insert into
o851dm.tjslsts
(

OGZ_DRI_NR,                      
FCT_DRI_NR,            
GL_AC_DRI_NR,
GL_JRN_HDR_DRI_NR,
GL_JRN_LIN_DRI_NR,             
JSL_DRI_NR,           
GL_JRN_SRC_DRI_NR,           
tm_dri_nr,           
ORG_OGZ_DRI_NR,           
tm_prd_yr_dt,           
mo_nr,           
rec_eff_stt_dt,           
prc_inl_grp_cd,           
STA_DR_USU_QY , 
STA_CR_USU_QY ,
STA_NET_USU_QY,
STA_DR_MTU_QY, 

STA_CR_MTU_QY,
STA_NET_MTU_QY)
SELECT
distinct

fin.OGZ_DRI_NR,                      
fin.FCT_DRI_NR,            
fin.GL_AC_DRI_NR,
fin.GL_JRN_HDR_DRI_NR,
fin.GL_JRN_LIN_DRI_NR,             
fin.JSL_DRI_NR,           
fin.GL_JRN_SRC_DRI_NR,           
fin.tm_dri_nr,           
fin.ORG_OGZ_DRI_NR,           
fin.tm_prd_yr_dt,           
fin.mo_nr,           
fin.rec_eff_stt_dt,           
fin.prc_inl_grp_cd,           
 0 , 			-- Default value for STA_DR_USU_QY  
 0 , 			-- Default value for STA_CR_USU_QY
 0 , 			-- Default value for STA_NET_USU_QY
 0 , 			-- Default value for STA_DR_MTU_QY 
 0 , 			-- Default value for STA_CR_MTU_QY
 0  			-- Default value for STA_NET_MTU_QY 

 from o851dm.tjslfin fin
where not exists
(

select 'X'
from o851dm.tjslsts stat
where

stat.OGZ_DRI_NR             = fin.OGZ_DRI_NR        
and stat.FCT_DRI_NR         = fin.FCT_DRI_NR        
and stat.GL_AC_DRI_NR       = fin.GL_AC_DRI_NR      
and stat.GL_JRN_HDR_DRI_NR  = fin.GL_JRN_HDR_DRI_NR 
and stat.GL_JRN_LIN_DRI_NR  = fin.GL_JRN_LIN_DRI_NR 
and stat.JSL_DRI_NR         = fin.JSL_DRI_NR        
and stat.GL_JRN_SRC_DRI_NR  = fin.GL_JRN_SRC_DRI_NR 
and stat.tm_dri_nr          = fin.tm_dri_nr
and stat.org_ogz_dri_nr	    = fin.org_ogz_dri_nr

)
Table A has 5 million and table B has 4 million records. The performance of this query is very slow. If we fire this query it access all the 81 partitions. Is there any way to to increase the partition join performance ?

Satyajit Biswas
Sr DBA
Voice -- 201-8284699

-- 
Posted from xavier2.ups.com [198.80.14.116] 
via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Thu Mar 15 2001 - 15:14:39 CST