query optimization on merge statement

92 views Asked by At

I am trying to update a very large table from an other table. The table i am trying to update is actually having millions of records.

What I have done is here:

merge into mo_moentity m
using ( select * from d_cell3g_cminfo tt )n
on ( m.motypeid= 'a8d52b69-d0e5-48ed-bad3-044ae55b3f92' and m.moentityname <> n.cell_3G   )
when matched then
  update
  set m.attribute05='Not On Air' ,m.isonsite='0'
  where  m.motypeid= 'a8d52b69-d0e5-48ed-bad3-044ae55b3f92'

This query is taking so long time to execute. Is there any way i can cut off the update time? Or I can optimize it? I am pretty new to optimization thing.

DDL of mo_moentity table :

-- Create table
create table MO_MOENTITY
(
  moentityid   NUMBER not null,
  moentitycode VARCHAR2(256) not null,
  moentityname VARCHAR2(256),
  motypeid     VARCHAR2(100) not null,
  emsid        VARCHAR2(100),
  equipmentid  VARCHAR2(100),
  isonsite     NUMBER,
  attribute01  VARCHAR2(128),
  attribute02  VARCHAR2(128),
  attribute03  VARCHAR2(128),
  attribute04  VARCHAR2(128),
  attribute05  VARCHAR2(128),
  attribute06  VARCHAR2(128),
  attribute07  VARCHAR2(128),
  attribute08  VARCHAR2(128),
  attribute09  VARCHAR2(128),
  attribute10  VARCHAR2(128),
  modifiedtime NUMBER,
  attribute11  VARCHAR2(128),
  attribute12  VARCHAR2(128),
  attribute13  VARCHAR2(128),
  attribute14  VARCHAR2(128),
  attribute15  VARCHAR2(128),
  attribute16  VARCHAR2(128),
  attribute17  VARCHAR2(128),
  attribute18  VARCHAR2(128),
  attribute19  VARCHAR2(128),
  attribute20  VARCHAR2(128),
  emsentityid  VARCHAR2(128),
  createtime   NUMBER,
  activetime   NUMBER,
  foreignid    NUMBER,
  version      NUMBER
)
tablespace PM4H_DB0
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create unique index MO_MOENTITY_IDX10 on MO_MOENTITY (MOTYPEID, MOENTITYID, MOENTITYCODE)
  tablespace PM4H_DB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create unique index MO_MOENTITY_IDX11 on MO_MOENTITY (MOTYPEID, MOENTITYCODE)
  tablespace PM4H_DB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create unique index MO_MOENTITY_IDX12 on MO_MOENTITY (EMSENTITYID, MOTYPEID, MOENTITYCODE)
  tablespace PM4H_DB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index MO_MOENTITY_IDX13 on MO_MOENTITY (MOENTITYNAME)
  tablespace PM4H_DB0
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

DDL of D_CELL3G_CMINFO:

-- Create table
create table D_CELL3G_CMINFO
(
  cell_3g    VARCHAR2(4000),
  nodeb      VARCHAR2(4000),
  rnc        VARCHAR2(4000),
  site_id    VARCHAR2(4000),
  lac        VARCHAR2(4000),
  region     VARCHAR2(200),
  vendor     VARCHAR2(100),
  ci         VARCHAR2(4000),
  vci        VARCHAR2(4000),
  state      VARCHAR2(100),
  ho_date    VARCHAR2(4000),
  attr_state NUMBER
)
tablespace PM4H_DB0
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
0

There are 0 answers