I can't see the region, year and month in the
WHERE clause of the existence test; are
they there ? If not, Oracle hasn't a hope of
doing any partition elimination on the test.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
"BISWAS SATYAJIT (ext2szb)" wrote in message
<177CDFBDC421D4119F1B0008C7CFA22F03F53537_at_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:25:51 CST
