问题 ORA-01652:无法通过128 (在表空间HATEMP1218中)扩展temp
最近在做项目时,需要查询另一个数据库的视图和表。用到dblink远程连接数据库进行查询。dblink查询需要在表或者视图后加@xxxxx(数据库用户名),为了方便在本地开发与部署,我先将需要查询的视图与表数据存到本地表中,而在生产环境中使用创建同名视图的方式,如下:
create view xxxx
as
SELECT * FROM XXXX@XXXXX
这样使得本地与生产环境的sql都不用加上@xxxxx。
在开发完成后,部署到正式环境测试时,发现了问题。 在本地没问题的sql到生产环境中报 ORA-01652:无法通过128 (在表空间HATEMP1218中)扩展temp
google了这个报错信息是因为临时表空间不足,我调整了临时表空间到40G后,发现还是报一样的错误,这就不对劲了,本地数据库的临时表空间只有1个G,也一样能查询啊。 我先是猜测数据量级的问题,可能是因为生产环境的数据量太大了。所以我做了第一个操作,将视图转为物化视图,并且只拉取近两年的数据。如下:
CREATE MATERIALIZED VIEW xxxx
REFRESH FORCE ON DEMAND
START WITH TO_DATE('24-12-2020 02:00:00', 'DD-MM-YYYY HH24:MI:SS') NEXT TO_DATE(CONCAT (TO_CHAR( SYSDATE + 1 , 'DD-MM-YYYY' ), '02:00:00' ), 'DD-MM-YYYY HH24:MI:SS' )
AS
SELECT * from xxxx@xxxxx
where create_time between sysdate-366-366 and sysdate;
物化视图与视图不同,视图只存储SQL语句,物化视图不止存储语句,还将数据存起来。 一波操作之后,我又执行了一下报错的SQL,等待了10分钟后,语句还是报错了。 到这我开始怀疑自己了,我将语句分别在生产环境与本地环境查看了执行计划。 发现两个执行计划不同,生产环境的基数已经飙升到了需要科学计数法了,本地环境却只是百万级。 我发现就是一个三表联查导致的基数飙升,写法如下
select xxx from a,b,c where a.id = b.id and a.id = c.id
这个直接导致生产环境下的基数飙升。这样只需要执行order by 操作,就会直接导致ORA-01652的错误。但是这个在本地却是正常的。这个时候就是排除法了,数据量相差不多,这个时候区别就是本地是三个表,而生产环境是三个物化视图。 这个时候我还是不相信是因为视图导致的这个问题。 我将上面的SQL换成了 left join 写法,如下:
select xxx from a
left join b on a.id = b.id
left join c on c.id = a.id
我继续查看执行计划,这个基数却是正常了!为什么? 我又复习了一下逗号与left join 查询的区别。逗号其实就是inner join 。果然我将上面的left 换成 inner 又出现了基数飙升。这个时候我有两条路,一个是将SQL语句换成left join 写法,另一个就是继续排除法,确定是不是视图的原因导致的这个问题。 我选择了继续。。。 我将物化视图直接 creat select 成了表。继续执行报错的SQL ,不出所料,正常了。 这个问题至此也就解决了,将物化视图转为真正的表就可以了。
反思
物化视图虽然将数据存储了,但是和表还是有所区别。视图与表的查询策略还是不一样的,具体哪里不一样,表是怎么查的,视图是如何查的,这个问题我暂时没有答案,只有通过不断的学习,实践才能明白吧。
评论