性能调优总结

 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. 待续。。。