Oracle hash分區

1.建立表

create table t1  
(  
  id varchar2(10),
  name varchar2(20)
)  
partition by hash(id)  
(  
  partition p1,  
  partition p2,  
  partition p3  
);      

2.檢視資料

insert into t1 values('p1',1);
insert into t1 values('p1',2);
insert into t1 values('p3',3);      

3.檢視資料

SQL> select * from t1;

ID                             NAME
------------------------------ ------------------------------------------------------------
p1                             1
p1                             2
p3                             3

SQL> select * from t1 partition(p1);

ID                             NAME
------------------------------ ------------------------------------------------------------
p1                             1
p1                             2

SQL> select * from t1 partition(p2);

no rows selected

SQL> select * from t1 partition(p3);

ID                             NAME
------------------------------ ------------------------------------------------------------
p3                             3      

4.存儲過程插入大量資料

declare
  i number;
  begin
  for i in 1..1000000 loop
   insert into t1 values(i,'name');
  end loop;
  commit;
  end;
  /      

5.檢視每個分區的資料，可以看出，存在資料傾斜。​

SQL> select count(1) from t1 partition(p1);

  COUNT(1)
----------
    249867

SQL> select count(1) from t1 partition(p2);

  COUNT(1)
----------
    500616

SQL> select count(1) from t1 partition(p3);

  COUNT(1)
----------
    249517