Re: Partition Join Performance problem

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Sat, 24 Mar 2001 01:00:09 +0100
Message-ID: <3ABBE389.F0B954A9@0800-einwahl.de>

Try this:

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, o851dm.tjslsts stat where 1 = 1
-- Now use the trick that Oracle uses for initially asserting foreign keys. -- First part of the trick: use outer join instead of not exists (which works row by row)

and 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

-- Second part of the trick: the join conditions must be unmet!

and stat.OGZ_DRI_NR is null
and stat.FCT_DRI_NR is null
and stat.GL_AC_DRI_NR is null
and stat.GL_JRN_HDR_DRI_NR is null
and stat.GL_JRN_LIN_DRI_NR is null
and stat.JSL_DRI_NR is null
and stat.GL_JRN_SRC_DRI_NR is null
and stat.tm_dri_nr is null
and stat.org_ogz_dri_nr is null

/

You may parallelize the join.

Martin

"BISWAS SATYAJIT (ext2szb)" wrote:
>
> 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 Fri Mar 23 2001 - 18:00:09 CST