20亿和20亿的大表关联如何优化？

记得几天前，老师让我们优化遇到一个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 t2 where object_id is null;
commit;
insert into p1 select ora_hash(object_id,4), a.* from t1 a;
commit;
—工作中用append parallel并行插入

insert into p2 select ora_hash(object_id,4), a.* from t2 a;
commit;
—工作中用append parallel并行插入

这样就把 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到4294967295ora_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 有另外的优化方法这里就不做介绍了。