0 Replies Latest reply: Oct 3, 2013 2:35 AM by user1828493 RSS

    Merge command on Partition table

    user1828493

      Hi All,

       

      I created a Partition table and create local index on it. Now my problem is that when I am going to execute the Merge command on this partition table then it gets hanged.

       

      ---------Table Structure----------------------------

       

      CREATE TABLE "PDMS"."PD_DATA_SET_RECORD"

         (    "DSM_ID" NUMBER,

          "DSR_ID" NUMBER,

          "DSR_FLDC1" VARCHAR2(100),

          "DSR_FLDC2" VARCHAR2(100),

          "DSR_FLDC3" VARCHAR2(100),

          "DSR_FLDC4" VARCHAR2(100),

          "DSR_FLDC5" VARCHAR2(100),

          "DSR_FLDC6" VARCHAR2(100),

          "DSR_FLDC7" VARCHAR2(100),

          "DSR_FLDC8" VARCHAR2(100),

          "DSR_FLDC9" VARCHAR2(100),

          "DSR_FLDC10" VARCHAR2(100),

          "DSR_FLDC11" VARCHAR2(100),

          "DSR_FLDC12" VARCHAR2(100),

          "DSR_FLDN1" NUMBER,

          "DSR_FLDN2" NUMBER,

          "DSR_FLDN3" NUMBER,

          "DSR_FLDN4" NUMBER,

          "DSR_FLDN5" NUMBER,

          "DSR_FLDN6" NUMBER,

          "DSR_FLDN7" NUMBER,

          "DSR_FLDN8" NUMBER,

          "DSR_FLDD1" DATE,

          "DSR_FLDD2" DATE,

          "DSR_FLDD3" DATE,

          "DSR_FLDD4" DATE,

          "DSR_FLDD5" DATE

         )

      PARTITION BY HASH(DSM_ID)

      PARTITIONS 100 ;

       

       

      CREATE UNIQUE INDEX "PDMS"."XPKPD_DATA_SET_RECORD" ON "PDMS"."PD_DATA_SET_RECORD" ("DSM_ID", "DSR_ID")  ;

       

      create index pd_ds_recrd_idx3 on PD_DATA_SET_RECORD (DSR_FLDC1) LOCAL;

      create index pd_ds_recrd_idx4 on PD_DATA_SET_RECORD (DSR_FLDC2) LOCAL;

      create index pd_ds_recrd_idx5 on PD_DATA_SET_RECORD (DSR_FLDC3) LOCAL;

      create index pd_ds_recrd_idx6 on PD_DATA_SET_RECORD (DSR_FLDC4) LOCAL;

      create index pd_ds_recrd_idx7 on PD_DATA_SET_RECORD (DSR_FLDC5) LOCAL;

      create index pd_ds_recrd_idx8 on PD_DATA_SET_RECORD (DSR_FLDC6) LOCAL;

      create index pd_ds_recrd_idx9 on PD_DATA_SET_RECORD (DSR_FLDC7) LOCAL;

      create index pd_ds_recrd_idx10 on PD_DATA_SET_RECORD (DSR_FLDC8) LOCAL;

      create index pd_ds_recrd_idx11 on PD_DATA_SET_RECORD (DSR_FLDC9) LOCAL;

      create index pd_ds_recrd_idx12 on PD_DATA_SET_RECORD (DSR_FLDC10) LOCAL;

      create index pd_ds_recrd_idx13 on PD_DATA_SET_RECORD (DSR_FLDC11) LOCAL;

      create index pd_ds_recrd_idx14 on PD_DATA_SET_RECORD (DSR_FLDC12) LOCAL;

      create index pd_ds_recrd_idx15 on PD_DATA_SET_RECORD (DSR_FLDN1) LOCAL;

      create index pd_ds_recrd_idx16 on PD_DATA_SET_RECORD (DSR_FLDN2) LOCAL;

      create index pd_ds_recrd_idx17 on PD_DATA_SET_RECORD (DSR_FLDN3) LOCAL;

      create index pd_ds_recrd_idx18 on PD_DATA_SET_RECORD (DSR_FLDN4) LOCAL;

      create index pd_ds_recrd_idx19 on PD_DATA_SET_RECORD (DSR_FLDN5) LOCAL;

      create index pd_ds_recrd_idx20 on PD_DATA_SET_RECORD (DSR_FLDN6) LOCAL;

      create index pd_ds_recrd_idx21 on PD_DATA_SET_RECORD (DSR_FLDN7) LOCAL;

      create index pd_ds_recrd_idx22 on PD_DATA_SET_RECORD (DSR_FLDN8) LOCAL;

      create index pd_ds_recrd_idx23 on PD_DATA_SET_RECORD (DSR_FLDD1) LOCAL;

      create index pd_ds_recrd_idx24 on PD_DATA_SET_RECORD (DSR_FLDD2) LOCAL;

      create index pd_ds_recrd_idx25 on PD_DATA_SET_RECORD (DSR_FLDD3) LOCAL;

      create index pd_ds_recrd_idx26 on PD_DATA_SET_RECORD (DSR_FLDD4) LOCAL;

      create index pd_ds_recrd_idx27 on PD_DATA_SET_RECORD (DSR_FLDD5) LOCAL;

       

       

       

       

       

      ---------Merge Command -----------------------------------

       

      merge into pd_data_set_record dd

      Using CB_CUST_MAST

      On ( dd.dsm_id = 2 and dd.DSR_FLDC1 = CUSTOMER_CODE  )

      WHEN MATCHED THEN UPDATE SET

      1. dd.DSR_FLDC2 = GENDER,dd.DSR_FLDC3 = NATIONALITY,dd.DSR_FLDC4 = SECTOR,dd.DSR_FLDC5 = CUSTOMER_TYPE,dd.DSR_FLDC6 = BM_PROF_CODE,dd.DSR_FLDC7 = BIRTH_INCORP_DATE,dd.DSR_FLDC8 = POSTING_RESTRICT,dd.DSR_FLDC9 = STREET_1,dd.DSR_FLDC10 = NAME_1,dd.DSR_FLDD1 = WK_CREATE_DATE,dd.DSR_FLDN1 = CUSTOMER_AGE where (dd.dsm_id=2)

      WHEN NOT MATCHED THEN INSERT ( dd.dsm_id,dd.DSR_ID,dd.DSR_FLDC1,dd.DSR_FLDC2,dd.DSR_FLDC3,dd.DSR_FLDC4,dd.DSR_FLDC5,dd.DSR_FLDC6,dd.DSR_FLDC7,dd.DSR_FLDC8,dd.DSR_FLDC9,dd.DSR_FLDC10,dd.DSR_FLDD1,dd.DSR_FLDN1 )

      values ( 2, Data_Set_Rec_MN.nextval, CUSTOMER_CODE,GENDER,NATIONALITY,SECTOR,CUSTOMER_TYPE,BM_PROF_CODE,BIRTH_INCORP_DATE,POSTING_RESTRICT,STREET_1,NAME_1,WK_CREATE_DATE,CUSTOMER_AGE ) ;



      Thanks,

      Omer