ada artikel dari asktom.oracle.com yang kasusnya mirip dengan bapak, dan solusi dari tom kyte pun kurang lebih sama dengan yang dilakukan pak teguh :
-------------------------------------------------------------------------------------------------------------------------------- You Asked (Jump to Tom's latest followup) Good Morning Tom. I need your expertise in this regard. I got a table which contains millions or records. I want to update and commit every time for so many records ( say 10,000 records). I dont want to do in one stroke as I may end up in Rollback segment issue(s). Any suggestions please ! ! ! Murali and we said... If I had to update millions of records I would probably opt to NOT update. I would more likely do: CREATE TABLE new_table as select <do the update "here"> from old_table; index new_table grant on new table add constraints on new_table etc on new_table drop table old_table rename new_table to old_table; you can do that using parallel query, with nologging on most operations generating very little redo and no undo at all -- in a fraction of the time it would take to update the data. -------------------------------------------------------------------------------------------------------------------------------- http://asktom.oracle.com/pls/ask/f?p=4950:8:8723689962104432863::NO::F4950_P8_DISPLAYID,F4950_P8_B:6407993912330,Y On 5/10/05, Ferry <[EMAIL PROTECTED]> wrote: > Betul pak Tpriyambodo. > > salam, > ferry > > ----- Original Message ----- > From: "tpriyambodo" <[EMAIL PROTECTED]> > To: <[email protected]> > Sent: Tuesday, May 10, 2005 9:04 AM > Subject: [indo-oracle] Re: Menghilangkan row chaining dan row migration > > > > Thanks pak vicko dan pak ferry. > > Untuk pak ferry, maksusnya cara "ini" adalah cara yang saya lakukan > > selama ini kan? > > > > \--- In [email protected], "Ferry" <[EMAIL PROTECTED]> wrote: > > > Saya menyukai cara ini untuk menghapus banyak record. > > > Pros: > > > 1. Proses DELETE lebih cepat. > > > 2. Updating Index tree tidak perlu > > > 3. Dikerjakan pada DDL > > > 4. Fragmentasi blok data terhindari (diminimalkan) > > > > > > Cons: > > > Untuk sementara waktu (selama proses maintenance) tabel > > unavailable . > > > > > > Cara ini efektif bagi saya jika ada kompromi > > > untuk ketidaktersediaan tabel. > > > > > > regs, > > > ferry > > > > > > ----- Original Message ----- > > > From: "tpriyambodo" <[EMAIL PROTECTED]> > > > To: <[email protected]> > > > Sent: Friday, May 06, 2005 8:01 PM > > > Subject: [indo-oracle] Menghilangkan row chaining dan row migration > > > > > > > > > > Dear all > > > > > > > > jika tabel kita terdiri dari 1 juta baris. kita proses update. > > trus > > > > kita melakukan delete untuk kondisi tertentu, yang berjumlah 800 > > ribu > > > > baris shg sisa 200 ribu baris. Dan proses seperti diatas > > berlangsung > > > > harian dimana ada data yang dimasukkan ( 100% jumlah data), > > > > diproses/update , dan didelete ( 80% jumlah data). > > > > > > > > Dari karakteristik proses yang kita lakukan terhadap tabel > > tersebut, > > > > saya mendapatkan > > > > 1. banyak blok kosong di bawah watermark ( saat di lihat blok > > yang > > > > dibaca, walau untuk data yang sedikit. > > > > 2. banyak row chaining dan row migration > > > > > > > > Yang ingin saya tanyakan , bagaimana cara untuk mengatur data > > pada > > > > tabel itu (istilahnya kalau di hard disk kita melakukan defrag ) > > shg > > > > data pada tabel tersebut teratur, dan proses query menjadi lebih > > cepat. > > > > > > > > Hal sekarang yang saya lakukan adalah > > > > 1. create tabel_temp baru yang memindahkan semua data dari tabel > > lama > > > > CREATE TABLE TBL_TEMP AS SELECT * FROM TBL_LAMA > > > > 2. drop tabel lama > > > > 3. ubah tabel_temp menjadi nama tabel lama, > > > > RENAME TBL_TEMP TO TBL_LAMA > > > > 4. lalu saya create index lagi untuk tabel tersebut > > > > > > > > adakah cara yang lebih efektif temans ???? > > > > > > > > thanks before > > > > > > > > teguh > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > -----------I.N.D.O - O.R.A.C.L.E--------------- > > > > Keluar: [EMAIL PROTECTED] > > > > Website: http://indo-oracle.blogspot.com > > > > ----------------------------------------------- > > > > > > > > Bergabung dengan Indonesia Thin Client User Groups, > > > > Terminal Server, Citrix, New Moon Caneveral, di: > > > > http://indo-thin.vze.com > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > -----------I.N.D.O - O.R.A.C.L.E--------------- > > Keluar: [EMAIL PROTECTED] > > Website: http://indo-oracle.blogspot.com > > ----------------------------------------------- > > > > Bergabung dengan Indonesia Thin Client User Groups, > > Terminal Server, Citrix, New Moon Caneveral, di: > > http://indo-thin.vze.com > > Yahoo! Groups Links > > > > > > > > > > > > > > > -- > -----------I.N.D.O - O.R.A.C.L.E--------------- > Keluar: [EMAIL PROTECTED] > Website: http://indo-oracle.blogspot.com > ----------------------------------------------- > > Bergabung dengan Indonesia Thin Client User Groups, > Terminal Server, Citrix, New Moon Caneveral, di: > http://indo-thin.vze.com > > > ________________________________ > Yahoo! Groups Links > > > To visit your group on the web, go to: > http://groups.yahoo.com/group/indo-oracle/ > > To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. -- ------------------------------------------------------------------------------------------------------------------------------------------ >> Semulia-mulia kekayaan milik pribadi adalah meninggalkan banyak keinginan [Imam Ali r.a] >> Everyone dies, but not everyone really lives [William Wallace, Braveheart] >> Death come to us all ... [Princess Wales, Braveehart] ------------------------------------------------------------------------------------------------------------------------------------------ -- -----------I.N.D.O - O.R.A.C.L.E--------------- Keluar: [EMAIL PROTECTED] Website: http://indo-oracle.blogspot.com ----------------------------------------------- Bergabung dengan Indonesia Thin Client User Groups, Terminal Server, Citrix, New Moon Caneveral, di: http://indo-thin.vze.com Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indo-oracle/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
