20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

柔情只为你懂 2022-02-27 09:06 230阅读 0赞

记得5年前遇到一个SQL，就是一个简单的两表关联，SQL跑了差不多一天一夜，这两个表都非常巨大，每个表都有几十个G，数据量每个表有20多亿，表的字段也特别多。

相信大家也知道SQL慢在哪里了，单个进程的PGA 是绝对放不下几十个G的数据，这就会导致消耗大量temp tablespace，SQL慢就是慢在temp来回来回来回...的读写数据。

遇到这种超级大表与超级大表怎么优化呢？这篇文章将告诉你答案。

首先创建2个测试表 t1,t2 数据来自dba\_objects

create table t1 as select \* from dba\_objects;

create table t2 as select \* from dba\_objects;

我们假设 t1 和 t2 就是 两个超级大表， 要运行的 SQL：   select \* from t1,t2 where t1.object\_id=t2.object\_id;

假设t1 t2 都是几十个GB 或者更大， 那么你懂的，上面的SQL基本上是跑不出结果的。

有些人在想，开个并行不就得了，用并行 hash hash 算法跑SQL，其实是不可以的，原因不多说了。

我们可以利用MPP数据库架构(Greenplum/Teradata/vertica)思想，或者是利用HADOOP的思想来对上面的SQL进行优化。

MPP架构/HADOOP架构的很重要的思想就是把数据切割，把大的数据切割为很多份小的数据，然后再对小的进行关联，那速度自然就快了。

在Oracle里面怎么把大数据切成小数据呢，有两个办法，一个是分区，另外一个是分表。我这里选择的是分区，当然了看了这篇文章你也可以分表。

创建一个表P1，在T1的表结构基础上多加一个字段HASH\_VALUE，并且根据HASH\_VALUE进行LIST分区

CREATE TABLE P1(  
HASH\_VALUE NUMBER,  
OWNER VARCHAR2(30),  
OBJECT\_NAME VARCHAR2(128),  
SUBOBJECT\_NAME VARCHAR2(30),   
OBJECT\_ID NUMBER,  
DATA\_OBJECT\_ID NUMBER,  
OBJECT\_TYPE VARCHAR2(19),  
CREATED DATE,  
LAST\_DDL\_TIME DATE,  
TIMESTAMP VARCHAR2(19),  
STATUS VARCHAR2(7),  
TEMPORARY VARCHAR2(1),  
GENERATED VARCHAR2(1),  
SECONDARY VARCHAR2(1),  
NAMESPACE NUMBER,  
EDITION\_NAME VARCHAR2(30)  
)     
   PARTITION BY  list(HASH\_VALUE)  
(  
partition p0 values (0),  
partition p1 values (1),  
partition p2 values (2),  
partition p3 values (3),  
partition p4 values (4)  
)

同样的，在T2的表结构基础上多加一个字段HASH\_VALUE，并且根据HASH\_VALUE进行LIST分区

CREATE TABLE P2(  
HASH\_VALUE NUMBER,  
OWNER VARCHAR2(30),  
OBJECT\_NAME VARCHAR2(128),  
SUBOBJECT\_NAME VARCHAR2(30),   
OBJECT\_ID NUMBER,  
DATA\_OBJECT\_ID NUMBER,  
OBJECT\_TYPE VARCHAR2(19),  
CREATED DATE,  
LAST\_DDL\_TIME DATE,  
TIMESTAMP VARCHAR2(19),  
STATUS VARCHAR2(7),  
TEMPORARY VARCHAR2(1),  
GENERATED VARCHAR2(1),  
SECONDARY VARCHAR2(1),  
NAMESPACE NUMBER,  
EDITION\_NAME VARCHAR2(30)  
)     
   PARTITION BY  list(HASH\_VALUE)  
(  
partition p0 values (0),  
partition p1 values (1),  
partition p2 values (2),  
partition p3 values (3),  
partition p4 values (4)  
)

注意：P1和P2表的分区必须一模一样

delete t1 where object\_id is null;

commit;

delete t1 where object\_id is null;

commit;

insert into p1  
select ora\_hash(object\_id,4), a.\*  from t1 a;  ---工作中用append parallel并行插入

commit;

insert into p2  
select ora\_hash(object\_id,4), a.\*  from t2 a;  ---工作中用append parallel并行插入

commit;

这样就把 T1 和 T2的表的数据转移到 P1 和 P2 表中了

那么之前运行的 select \* from t1,t2 where t1.object\_id=t2.object\_id  其实就等价于下面5个SQL了

select \* from p1,p2 where p1.object\_id=p2.object\_id and p1.hash\_value=0 and p2.hash\_value=0;  
select \* from p1,p2 where p1.object\_id=p2.object\_id and p1.hash\_value=1 and p2.hash\_value=1;  
select \* from p1,p2 where p1.object\_id=p2.object\_id and p1.hash\_value=2 and p2.hash\_value=2;  
select \* from p1,p2 where p1.object\_id=p2.object\_id and p1.hash\_value=3 and p2.hash\_value=3;  
select \* from p1,p2 where p1.object\_id=p2.object\_id and p1.hash\_value=4 and p2.hash\_value=4;

工作中，大表拆分为多少个分区，请自己判断。另外一个需要注意的就是ORA\_HASH函数

oracle中的hash分区就是利用的ora\_hash函数

partition by hash(object\_id) 等价于 ora\_hash(object\_id,4294967295)

ora\_hash(列,hash桶) hash桶默认是4294967295 可以设置0到4294967295

ora\_hash(object\_id,4) 会把object\_id的值进行hash运算，然后放到 0,1,2,3,4 这些桶里面，也就是说 ora\_hash(object\_id,4) 只会产生 0 1 2 3 4

有兴趣的同学可以自己去测试速度。生产库采用这种优化方法，之前需要跑一天一夜的SQL，在1小时内完成。

为了简便，可以使用PLSQL编写存储过程封装上面操作。

当然了，如果使用hadoop 或者 greenplum 有另外的优化方法这里就不做介绍了。

道森出品必属精品 抄袭翻版必究

参考文献：

[https://blog.csdn.net/robinson1988/article/details/50756921][https_blog.csdn.net_robinson1988_article_details_50756921]

[https_blog.csdn.net_robinson1988_article_details_50756921]: https://blog.csdn.net/robinson1988/article/details/50756921