oracle大表执行慢的优化方法,oracle大表关联的优化方法

执行的 SQL：   select * from t1,t2 where t1.object_id=t2.object_id;    -- 两个20亿大表关联耗时一天一夜

关联慢的原因：单个进程的PGA装不下大表数据 --> 消耗大量临时表空间 --> 临时表来回读写

优化方法：

1)开并行，并行hash。 -- 不可行

2)利用MPP架构/HADOOP架构思想：数据分割。

数据分割方法：一个是分区；另外一个是分表。本文选用分区。

步骤：

1)创建表P1，P2。在T1的表结构基础上多加一个字段HASH_VALUE，而且依据HASH_VALUE进行LIST分区。同理，P2。

注意：工作中具体需要多少分区需自己判断，但P1和P2表的分区必须一模一样。

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)

)

2)向P1，P2中插入数据。

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 这些桶里面。

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; ---工作中用append parallel并行插入

commit;

insert into p2

select ora_hash(object_id,4), a.* from t2 a; ---工作中用append parallel并行插入

commit;

3)执行表关联。

优化后执行时间大约1小时。可将该过程整理为存储过程。

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;