1.删除部分字段重复数据 使用临时表优化
delete from table1 a where f1,f2 in (
select b.f1,b.f2,count(*) from table1 b where rownum > 1 group by b.f1,b.f2 having count(*) > 1
)
优化
查询到的重复的数据插入到一个临时表中,然后对进行删除,这样,执行删除的时候就不用再进行一次查询
with temp as (
select b.f1,b.f2,count(*) from table1 b where rownum > 1 group by b.f1,b.f2 having count(*) > 1
)
delete from table1 a where f1,f2 in(select f1,f2 from temp)
2,Java代码中查询多个信息
循环调用
SELECT * FROM TBL_LAB1
WHERE ID = ?
优化
SELECT * FROM TBL_LAB1
WHERE ID IN (?,?,?....)
如果这些id是从另外的表中根据某一条件查得,再优化
SELECT * FROM TBL_LAB1 T1,TBL_LAB2 T2
WHERE T1.ID = T2.ID
AND T2.SOME_FIELDS = 某一条件
3. Java代码中更新多条纪录status
UPDATE TBL_LAB1 SET STATUS = 'S' WHERE STATUS = 'F' AND ID = ?
优化
UPDATE (SELECT CASE WHEN STATUS = 'F' THEN 'S'
ELSE STATUS
END NEW_STATUS
FROM TBL_LAB1
WHERE ID IN (?,?,?.....)
)
SET STATUS = NEW_STATUS
如果这些id是从另外的表中根据某一条件查得,再优化
UPDATE (SELECT CASE WHEN STATUS = 'F' THEN 'S'
ELSE STATUS
END NEW_STATUS
FROM TBL_LAB1 T1
WHERE EXISTS (
SELECT 1 FROM TBL_LAB2 T2
WHERE T1.ID = T2.ID AND T2.SOME_FIELDS = 某一条件
)
)
SET STATUS = NEW_STATUS
4. 待续。。。