首页 > 代码库 > 20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

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

记得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=http://www.mamicode.com/0 and p2.hash_value=0;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=http://www.mamicode.com/1 and p2.hash_value=1;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=http://www.mamicode.com/2 and p2.hash_value=2;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=http://www.mamicode.com/3 and p2.hash_value=3;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=http://www.mamicode.com/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 有另外的优化方法这里就不做介绍了。

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

??

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