创建数据库：
- C# code
create table DATA_MODEL_FINAL( OBJID NUMBER not null, OBJUSERID VARCHAR2(20), OBJELEVATION FLOAT, GROUPID VARCHAR2(20) default 0);create bitmap index GROUPID_model_index on data_model_final(GROUPID);create unique index objuserid_index on data_model_final(objuserid); create table DATA_PIPE_FINAL( OBJID NUMBER not null, STARPOINTELEVATION FLOAT, GROUPID VARCHAR2(20) default 0, START_POINT VARCHAR2(20));create index START_POINT_index on data_pipe_final(START_POINT);create bitmap index GROUPID_pipe_index on data_pipe_final(GROUPID);insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910615,'AJ31123',0,'0201');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910616,'AJ31124',0,'0202');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910617,'AJ31125',0,'0203');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910618,'AJ31126',0,'0204');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910619,'AJ31127',0,'0204');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910620,'AJ31128',0,'0204');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201101,'AJ31126',1.5,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201102,'B000001',1.8,'0101');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201103,'AJ31126',2.2,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201104,'AJ31126',1.5,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201105,'AJ31127',1.5,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201106,'AJ31127',1.9,'0104');
update语句：
- SQL code
UPDATE DATA_MODEL_FINAL A SET OBJELEVATION=(SELECT MAX(B.STARPOINTELEVATION) FROM DATA_PIPE_FINAL B WHERE B.GROUPID='0104' AND B.START_POINT=A.OBJUSERID ) WHERE A.GROUPID='0204';
以上数据库是我精简了很多字段和数据的，实际上这2个表的数据都超过50万条了，使用update很影响效率，因此我想把以上update语句改为merge into以提高效率，各位有什么好方法否？
- SQL code
merge into DATA_MODEL_FINAL A using DATA_PIPE_FINAL B on (B.GROUPID='0104' AND A.GROUPID='0204' AND B.START_POINT=A.OBJUSERID) when matched then update set A.OBJELEVATION=MAX(B.STARPOINTELEVATION)
------解决方案--------------------
- SQL code
merge into DATA_MODEL_FINAL A using (SELECT MAX(STARPOINTELEVATION) as STARPOINTELEVATION, START_POINT FROM DATA_PIPE_FINAL WHERE GROUPID='0104' group by START_POINT) b on (A.GROUPID='0204' AND B.START_POINT=A.OBJUSERID) when matched then update set A.OBJELEVATION=MAX(B.STARPOINTELEVATION)