在mysql中创建临时表:
create table temptable_2
(
instanceid NUMERIC(30),
celltype varchar(128),
enodeb_id NUMERIC(30),
eutrancell_id NUMERIC(30)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE INDEX temptable_2_1 ON temptable_2
(enodeb_id,eutrancell_id);
create table temptable_3
(
EVENTTIMESTAMP date,
team_name varchar(400),
lac NUMERIC(30),
ci varchar(20),
e_node_id NUMERIC(30),
cell_type varchar(10)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE INDEX temptable_3_1 ON temptable_3
(lac,ci);
<action tableName="CQDW_TEAMCELL" id="CQDW_TEAMCELL" bean="save"
targetKey="" targetDBName="NMSServer.app"
sourceDBName="database.tempdb">
<properties>
<property name="sql" value="
select t3.EVENTTIMESTAMP EVENTTIMESTAMP,
t2.instanceid, t3.team_name, t3.lac, t3.ci, t3.e_node_id, t3.cell_type
from temptable_2 t2 ,temptable_3 t3
where t2.enodeb_id||t2.eutrancell_id = t3.ci" />
</properties>
</action>
where t2.enodeb_id||t2.eutrancell_id = t3.ci语句中t2.enodeb_id||t2.eutrancell_id产生了笛卡尔积,正常情况下temptable2中有4万多条数据,拼接之后产生了笛卡尔积有大于16亿条数据,造成内存溢出。处理的方法:将temptable2中多建立一列enodeb_eutrancell_id,存放拼接之后的数据。然后直接用where t2.enodeb_id||t2.eutrancell_id = t3.ci匹配。以下是更改之后的代码。(注意建立索引)
create table temptable_2
(
instanceid NUMERIC(30),
celltype varchar(128),
enodeb_id NUMERIC(30),
eutrancell_id NUMERIC(30),
enodeb_eutrancell_id NUMERIC(30)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE INDEX temptable_2_1 ON temptable_2
(enodeb_eutrancell_id);
<action tableName="CQDW_TEAMCELL" id="CQDW_TEAMCELL" bean="save"
targetKey="" targetDBName="NMSServer.app"
sourceDBName="database.tempdb">
<properties>
<property name="sql" value="
select t3.EVENTTIMESTAMP EVENTTIMESTAMP,
t2.instanceid, t3.team_name, t3.lac, t3.ci, t3.e_node_id, t3.cell_type
from temptable_2 t2 ,temptable_3 t3
where t2.enodeb_eutrancell_id = t3.ci" />
</properties>
</action>
create table temptable_2
(
instanceid NUMERIC(30),
celltype varchar(128),
enodeb_id NUMERIC(30),
eutrancell_id NUMERIC(30)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE INDEX temptable_2_1 ON temptable_2
(enodeb_id,eutrancell_id);
create table temptable_3
(
EVENTTIMESTAMP date,
team_name varchar(400),
lac NUMERIC(30),
ci varchar(20),
e_node_id NUMERIC(30),
cell_type varchar(10)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE INDEX temptable_3_1 ON temptable_3
(lac,ci);
<action tableName="CQDW_TEAMCELL" id="CQDW_TEAMCELL" bean="save"
targetKey="" targetDBName="NMSServer.app"
sourceDBName="database.tempdb">
<properties>
<property name="sql" value="
select t3.EVENTTIMESTAMP EVENTTIMESTAMP,
t2.instanceid, t3.team_name, t3.lac, t3.ci, t3.e_node_id, t3.cell_type
from temptable_2 t2 ,temptable_3 t3
where t2.enodeb_id||t2.eutrancell_id = t3.ci" />
</properties>
</action>
where t2.enodeb_id||t2.eutrancell_id = t3.ci语句中t2.enodeb_id||t2.eutrancell_id产生了笛卡尔积,正常情况下temptable2中有4万多条数据,拼接之后产生了笛卡尔积有大于16亿条数据,造成内存溢出。处理的方法:将temptable2中多建立一列enodeb_eutrancell_id,存放拼接之后的数据。然后直接用where t2.enodeb_id||t2.eutrancell_id = t3.ci匹配。以下是更改之后的代码。(注意建立索引)
create table temptable_2
(
instanceid NUMERIC(30),
celltype varchar(128),
enodeb_id NUMERIC(30),
eutrancell_id NUMERIC(30),
enodeb_eutrancell_id NUMERIC(30)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE INDEX temptable_2_1 ON temptable_2
(enodeb_eutrancell_id);
<action tableName="CQDW_TEAMCELL" id="CQDW_TEAMCELL" bean="save"
targetKey="" targetDBName="NMSServer.app"
sourceDBName="database.tempdb">
<properties>
<property name="sql" value="
select t3.EVENTTIMESTAMP EVENTTIMESTAMP,
t2.instanceid, t3.team_name, t3.lac, t3.ci, t3.e_node_id, t3.cell_type
from temptable_2 t2 ,temptable_3 t3
where t2.enodeb_eutrancell_id = t3.ci" />
</properties>
</action>