Oracle中合理利用临时表解决in语句的优化过程

最新推荐文章于 2023-09-28 14:00:32 发布

萧冒号

最新推荐文章于 2023-09-28 14:00:32 发布

阅读量9.6k

点赞数 2

分类专栏： java Oracle

本文链接：https://blog.csdn.net/xiao_2317/article/details/51771330

版权

java 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

Oracle

1 篇文章 0 订阅

订阅专栏

在很多情况下，如果我们要做一些筛选，很多时候很自然的想到用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临时表是会话或事务级别的，只对当前会话或事务可见。每个会话只能查看和修改自己的数据。

关于临时表的其他知识，大家可以参考http://www.programering.com/a/MTN0MTNwATU.html

方案确定下来后，我们首先建立一个临时表

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) {

        this.connection = connection;

    }

    public void executeBUQuery(String[] bus) {

        StopWatch sw = new StopWatch();

        sw.start();

        int size = 0;

        try {

            connection.createStatement().execute("truncate table HELPER_GTT");

            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();
				}
				p.executeBatch();


           }

        } catch (SQLException e) {
		    e.printStackTrace();
		}
		sw.stop();
		//record the time cost

    }

}

后面的操作就简单了，查询代码就可以写成类似

Connection conn = jdbcTemplate.getDataSource().getConnection();
QueryHelper helper = new QueryHelper(conn);
String[] BUs = .....;
helper.executeBUQuery(BUs);
String sql = "select * from user u join HELPER_GTT helper on u.bu == helper.bu where ....";
ResultSet rs = conn.executeQuery(sql);
....

这样使用临时表处理后，数据查询的时间很好的控制在1s以内。