大資料量資料查詢最佳化

在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;

有些人在想，開個並行不就得了，用並行 hash hash 跑SQL，其實是不可以的，原因不多說了。

我們可以利用MPP(Greenplum/Teradata/vertica)思想，或者是利用的思想來對上面的SQL進行最佳化。

 MPP架構/HADOOP架構的很重要的思想就是把資料切割，把大的資料切割為很多份小的資料，然後再對小的進行關聯，那速度自然就快了

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