oracle 用库内分片的方法来 优化sql

转自落落公开课

create table t1 as select * from dba_objects;

create table t2 as select * from dba_objects;



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)
)






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)
)




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;


--比较两个sql 的 
select * from t1,t2 where t1.object_id=t2.object_id;  -- 全表扫描
select * from  p1,p2 where p1.object_id=p2.object_id;  -- 用到hash join 和 分区