On Feb 20, 12:44 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Sam Durai wrote:
> > Hello, I need to merge a small table (of rows less than 100,sometimes
> > even 0 rows) to a big table (of rows around 4 billion). I used the PK
> > of the big table as merge key but merge does a table scan so it runs
> > for ever.
>
> > I checked the table and PK statistics of the big table and it looks
> > good. Please let me know if I need to check for something else.
>
> > Here are more details
> > Small table - Non Partitioned ( Node 0)
> > Big table - Partitioned across 7 logical nodes (Node 1 - 8)
>
> > Query:
> > MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
> > SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> > FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> > WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
> > B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
> > A.VEH_OPTN_CD_CSI =13)
> > WHEN NOT MATCHED
> > THEN
> > INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
> > PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
> > DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
> > B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
> > 2007-02-20 10:39:53', '2007-02-20 10:39:53')
> > ELSE IGNORE
>
> > Reorgchk stats of big table is as below
> > Table statistics:
> > SCHEMA NAME CARD OV NP FP ACTBLK
> > TSIZE F1 F2 F3 REORG
> > ----------------------------------------------------------------------------------------
> > Table: CARD.VIN_VEH_OPTNS
> > CARD VIN_VEH_OPTNS 4.6e+09 0 3e+07 3e+07 - 4.96e
> > +11 0 97 97 ---
> > ----------------------------------------------------------------------------------------
>
> > Index statistics:
>
> > SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL
> > KEYS F4 F5 F6 F7 F8 REORG
> > -------------------------------------------------------------------------------------------------
> > Table: CARD.VIN_VEH_OPTNS
> > CARD VVO_IX1 5e+09 7e+06 6916 4 10 13755
> > 1000 100 80 0 0 0 -----
> > CARD VVO_IX2 5e+09 7e+06 287 4 12 8e+05 1e
> > +07 100 78 0 0 0 -----
> > SYSIBM SQL061028160336900 5e+09 6e+07 0 5 27 84 5e
> > +09 98 68 1 0 0 -----
> > -------------------------------------------------------------------------------------------------
>
> > Indexes on Big table:
> > CARD VVO_IX1
> > D 1
> > +DWH_EFCTV_TIMSTM
> > CARD VVO_IX2
> > D 2 +DWH_UPD_TIMSTM
> > +VEH_OPTN_CD_CSI
> > SYSIBM SQL061028160336900
> > P 3 +VEH_IDENT_NBR+OPTN_CD
> > +VEH_OPTN_CD_CSI
>
> > Access Plan:
> > Optimizer Plan:
>
> > INSERT
> > ( 2)
> > / \
> > DTQ Table:
> > ( 3) CARD
> > | VIN_VEH_OPTNS
> > TBSCAN
> > ( 4)
> > |
> > TEMP
> > ( 5)
> > |
> > FILTER
> > ( 6)
> > |
> > FILTER
> > ( 7)
> > |
> > TBSCAN
> > ( 8)
> > |
> > SORT
> > ( 9)
> > |
> > NLJOIN
> > ( 10)
> > / \
> > TBSCAN TBSCAN
> > ( 11) ( 12)
> > | |
> > Table: TEMP
> > ETL ( 13)
> > STG_NEWS_VIN_VEH_OPTNS2 |
> > BTQ
> > ( 14)
> > |
> > TBSCAN
> > ( 15)
> > |
> > Table:
> > CARD
> > VIN_VEH_OPTNS
>
> > Please share your thoughts.
>
> Do/Can you have dups in the source?
> SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> WHERE ERR_FLAG IN ('N', 'Y'))AS B
>
> assuming you don't have a UNIQUE key on VEH_IDENT_NBR, OPTN_CD
> I recommend SELECT VEH_IDENT_NBR, OPTN_CD, MAX(ERR_FLAG)
> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> WHERE ERR_FLAG IN ('N', 'Y')
> GROUP BY VEH_IDENT_NBR, OPTN_CD) AS B
>
> Do you have statistics on the small table?
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Thanks for looking into the issue. We cannot have duplicates on
VEH_IDENT_NBR, OPTN_CD, ERR_FLAG. I do have updated stats on the small
table and most of the time this table would be empty so I'm not sure
whether Unique Index would be of help.