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
);