On Feb 20, 9:32 pm, "Tonkuma" <tonk...@jp.ibm.com> wrote:
> On Feb 21, 2:07 am, "Sam Durai" <reachsamdu...@gmail.com> 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
>
> I thought that it is worth to try following index.
> Because, to check MATCHED or NOT, data of columns A.VEH_IDENT_NBR,
> A.OPTN_CD, A.VEH_OPTN_CD_CSI from Table "CARD.VIN_VEH_OPTNS AS A" are
> necessary. If there is no appropriate index, DB2 need to see table
> itself to acquire these values. Consequentry, tablespace scan may be
> inevitable.
> CREATE INDEX CARD_IDNBR_CD_CSI ON CARD.VIN_VEH_OPTNS
> (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI)

Thats the PK in big table.
SYSIBM SQL061028160336900
P 3 +VEH_IDENT_NBR+OPTN_CD
+VEH_OPTN_CD_CSI