Dear Rekans, Terima kasih banyak banget atas semua pencerahannya.... Saya punya satu pertanyaan lagi yang masih berhubungan dengan hal ini...
Table dengan partisi sudah berhasil saya create. Partisi yang digunakan berdasarkan content_id dan menggunakan range. Selain itu, saya juga membuat sub partisi menggunakan HASH dari kolom no_handphone. Nah, berdasarkan kebutuhan aplikasi, composite antara content_id dan no_handphone ini merupakan primary key. Yang otomatis dicreate indexnya oleh Oracle. Pertanyaan saya, bisa gak yach index composite ini dibuat menjadi local index. Jadi nantinya kalau mau rebuild lebih cepet. Ada temen bilang kalau PK tidak bisa diindex secara lokal. Kalau memang tidak bisa, ada work aroundnya gak yach? Terima kasih.......... On Mon, Apr 20, 2009 at 9:45 AM, Oracle Engineer <[email protected]> wrote: > > > 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]<bre_tigan_tambak%40yahoo.com> > > > > > > > > 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] > > > [Non-text portions of this message have been removed]
