mungkin bahasa teknikal nya begini:
tapi kesimpulannya, hashing algorithm yg katanya "it will make EVEN
DISTRIBUTION of data" tidak secara presisi benar :-)
create table th (id number, modd number, padding varchar2(40), rrnum number)
tablespace ts1 partition by hash(id) partitions 4
store in (ts1,ts2);
declare
r pls_integer;
strnd varchar2(12);
begin
for i in 1..2000 loop
r := round(dbms_random.value(1,99999999),0);
strnd := dbms_random.string('A',12);
insert into th values(i,r+5,strnd,r/12);
end loop;
commit;
end;
/
SQL> select table_name,partition_name from user_tab_partitions where
table_name='TH';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TH SYS_P21
TH SYS_P22
TH SYS_P23
TH SYS_P24
SQL> select count(id) from th partition(sys_p24);
COUNT(ID)
----------
492
SQL> select count(id) from th partition(sys_p23);
COUNT(ID)
----------
512
SQL> select count(id) from th partition(sys_p22);
COUNT(ID)
----------
505
SQL> select count(id) from th partition(sys_p21);
COUNT(ID)
----------
491
CREATE TABLE kkpap_pruning
(
partition_count NUMBER,
iterator VARCHAR2(32),
partition_level VARCHAR2(32),
order_pt VARCHAR2(12),
call_time VARCHAR2(12),
part# NUMBER,
subp# NUMBER,
abs# NUMBER
);
ALTER SESSION SET tracefile_identifier = 'hash_part';
ALTER SESSION SET EVENTS '10128 trace name context forever, level 2';
select count(id) from th partition(sys_p22);
ALTER SESSION SET EVENTS '10128 trace name context off';
ALTER SESSION SET SQL_TRACE=FALSE;
SQL> select * from th where id=10;
ID MODD PADDING RRNUM
---------- ---------- ---------------------------------------- ----------
10 20656320 xYdicvahRaFk 1721359.58
SQL> select count(id) from th partition(sys_p22);
COUNT(ID)
----------
505
SQL> select count(id) from th;
COUNT(ID)
----------
2000
ALTER SESSION SET EVENTS '10128 trace name context off';
trace file:
===========
*** 2009-04-20 09:36:55.515
Partition Iterator Information:
partition level = PARTITION
call time = RUN
order = ASCENDING
Partition iterator for level 1:
iterator = RANGE [1, 1]
index = 1
current partition: part# = 1, subp# = 1048576, abs# = 1
*** 2009-04-20 09:37:19.156
Partition Iterator Information:
partition level = PARTITION
call time = RUN
order = ASCENDING
Partition iterator for level 1:
iterator = RANGE [1, 1]
index = 1
current partition: part# = 1, subp# = 1048576, abs# = 1
*** 2009-04-20 09:37:43.062
Partition Iterator Information:
partition level = PARTITION
call time = RUN
order = ASCENDING
Partition iterator for level 1:
iterator = RANGE [0, 3]
index = 0
current partition: part# = 0, subp# = 1048576, abs# = 0
current partition: part# = 1, subp# = 1048576, abs# = 1
current partition: part# = 2, subp# = 1048576, abs# = 2
current partition: part# = 3, subp# = 1048576, abs# = 3
Partition Iterator Information:
partition level = PARTITION
call time = RUN
order = ASCENDING
Partition iterator for level 1:
iterator = RANGE [0, 3]
index = 0
current partition: part# = 0, subp# = 1048576, abs# = 0
current partition: part# = 1, subp# = 1048576, abs# = 1
current partition: part# = 2, subp# = 1048576, abs# = 2
current partition: part# = 3, subp# = 1048576, abs# = 3
--
best regards
Oracle DBA
http://www.dbs247.com
http://blog.dbs247.com
2009/4/16 Ananias Sembiring <[email protected]>
>
> Penggunaan HASH bisa jadi tidak rata2 banget.,
> kl mau rata., gunakan jumlah partisi = n pangkat 2., (2, 4, 8, 16, 32)..,
> jumlah partisi di atas pun., ga rata2 banget.,
>
> emang intinya HASH akan membagi/distribusi data secara random., dan
> "diusahakan" rata..,
>
>
> CMIIW.,
>
> Ananias Sembiring
>
>
[Non-text portions of this message have been removed]
