Oracle中合理利⽤临时表解决in语句的优化过程
在很多情况下，如果我们要做⼀些筛选，很多时候很⾃然的想到⽤in语句。
⽐如 select * from user where id in (id1, id2, id3, ...., idN);
数据量不多的时候，这么做确实没有什么问题，⽆⾮是全表扫描。
关于oracle中in语法的劣势，⽹上有很多⽂档可以参考，还有很多关于⽤exist替代in的⽅案。在这⾥就不讨论了。
先来说说我碰到了案例吧。
表中有百万条数据，也建了相关的索引，我们要根据传递过来的⼀些business参数对数据做汇总。表结构⼤致如下：
id number,
datasetId number,
bu varchar(50),
....,
name varchar(50)
现在⾯临的问题是：前台穿过来的business字段是⼀个Tree结构的⽗节点，⽽数据库⾥⾯存放的是Tree结构的叶⼦节点。
我们这边会调⽤⼀个restService去拿到当前⽗节点的所有叶⼦节点。
然后SQL就变成了类似select * from user where id in (id1, id2, id3, ...., idN);
运⾏的时候，SQL出现异常，in语句⾥⾯的参数超过了1000个，那我就将SQL语句改成了类似于
select * from user where id in (id1, id2, id3, ...., id1000) or id in (id1001, id1002, id1003, ...., id2000) or ...;
到了这⾥的话，其实已经可以预见SQL的执⾏效率了，时间⼤概6-10秒左右，这是没法接受的。我调⽤service去查询⼀些极端的情况，有些⽗节点的所有叶⼦节点数已经超过了10000，我仿佛已经看到噩梦了。
当然我也希望有⼀些关联表能够让我不通过in的⽅式，⽽是采⽤join的⽅法加快查询速率。结果不如⼈意，并没有什么表直接存储⽗节点与叶⼦节点的关联关系。
既然没有的话，那我就使⽤临时表来代替关联表，然后与数据表做join操作。
临时表就是⽤来暂时保存临时数据（亦或叫中间数据）的⼀个数据库对象，它和普通表有些类似，然⽽⼜有很⼤区别。它只能存储在临时表空间，⽽⾮⽤户的表空间。ORACLE临时表是会话或事务级别的，只对当前会话或事务可见。每个会话只能查看和修改⾃⼰的数据。
⽅案确定下来后，我们⾸先建⽴⼀个临时表
CREATE GLOBAL TEMPORARY TABLE 'HELPER_GTT' {
'BU' VARCHAR2(20)
} ON COMMIT PRESERVE ROWS;
我们在拼装SQL语句的时候，⾸先将所有查询出来的叶⼦节点存⼊临时表中，这⾥要注意的是： 临时表的数据是存储在会话级别的，所以插⼊临时表和查询时，要使⽤同⼀个Connection对象.
public class QueryHelper {
private Connection connection;
public QueryHelper(Connection connection) {
}
public void executeBUQuery(String[] bus) {
StopWatch sw = new StopWatch();
sw.start();
int size = 0;
try {
if(bus != null) {
size = bus.length;
PrepareStatement p = connection.prepareStatement("insert into HELPER_GTT(BU) values (?)"); for(int i = 0; i < size; i++) {
p.setString(1, bus[i].trim());
p.addBatch();
}
}
} catch (SQLException e) {
e.printStackTrace();join和in哪个查询更快
}
sw.stop();
//record the time cost
}
}
后⾯的操作就简单了，查询代码就可以写成类似
Connection conn = DataSource().getConnection();
QueryHelper helper = new QueryHelper(conn);
String[] BUs = .....;
String sql = "select * from user u join HELPER_GTT helper on u.bu == helper.bu where ...."; ResultSet rs = uteQuery(sql);
....
这样使⽤临时表处理后，数据查询的时间很好的控制在1s以内。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论