wah sampai para senior turun tangan... newbie mengucapkan terima kasih... sekali lagi newbie mengerti deh knp si om oracle kasih script update table nya sampe banyak gitu.. padahal cuma 1 table yang di update...
hadiahnya buat pak ujang ditunggu nih sama temans di sini .. mo makan izzi pizza nih pak.... ada yang mo ultah tuh di sini... rekan pak ujang yang panda itu lho...he..he..he.. thx Yoenoes Lucas Ujang Jaenudin wrote: > > iseng2 ditesting di wind blowz..... kali aja dapet hadiah dari pak > yoenoes :) > > kesimpulan: > update table t1 > pada saat composite index, either semua kolom index diupdate pada 1 > eksekusi atau multiple eksekusi, timingnya gak jauh2 amat :) > > update table t2 > pada saat non composite index, jika diupdate kolom berindex dalam 1 > eksekusi akan memakan waktu disini sekitar 5 menit :) > tapi jika update masing2 kolom index tsb waktunya cuman 1:30 saja, > jika dijumlahkan sekitar 4:30 saja :) > > so, lama tidaknya tergantung composite or non composite :) > > C:\Documents and Settings\mild>sqlplus sys/oracle as sysdba > SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 15 17:37:46 2008 > Copyright (c) 1982, 2007, Oracle. All Rights Reserved. > > Connected to: > Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production > With the Partitioning, OLAP, Data Mining and Real Application Testing > options > > SQL> archive log list; > Database log mode No Archive Mode > Automatic archival Disabled > Archive destination D:\oracle\ora102\RDBMS > Oldest online log sequence 169 > Current log sequence 171 > > SQL> select count(*) from sales; > COUNT(*) > ---------- > 918843 > > SQL> set timing on; > SQL> select count(*) from sales; > COUNT(*) > ---------- > 918843 > > Elapsed: 00:00:00.00 > > SQL> desc sales; > Name Null? Type > ----------------------------------------- -------- --------------- > PROD_ID NOT NULL NUMBER > CUST_ID NOT NULL NUMBER > TIME_ID NOT NULL DATE > CHANNEL_ID NOT NULL NUMBER > PROMO_ID NOT NULL NUMBER > QUANTITY_SOLD NOT NULL NUMBER(10,2) > AMOUNT_SOLD NOT NULL NUMBER(10,2) > > SQL> create table t1 as select * from sales; > Table created. > Elapsed: 00:00:05.65 > > SQL> create table t2 as select * from t1; > Table created. > Elapsed: 00:00:02.86 > > SQL> create index t1_idx_cmp on t1(prod_id,cust_id,channel_id); > Index created. > Elapsed: 00:00:05.57 > > SQL> create index t2_idx_time on t2(prod_id); > Index created. > Elapsed: 00:00:01.92 > > SQL> create index t2_idx_cust on t2(cust_id); > Index created. > Elapsed: 00:00:05.31 > > SQL> create index t2_idx_channel on t2(channel_id); > Index created. > Elapsed: 00:00:01.64 > > SQL> alter system flush buffer_cache; > System altered. > Elapsed: 00:00:00.01 > > t1 composite > ----------------- > > SQL> update t1 set prod_id=0,cust_id=0,channel_id=0; > 918843 rows updated. > Elapsed: 00:01:30.40 > > SQL> alter system flush buffer_cache; > > SQL> update t1 set prod_id=1; > 918843 rows updated. > Elapsed: 00:01:26.09 > > SQL> alter system flush buffer_cache; > > SQL> update t1 set cust_id=1; > 918843 rows updated. > Elapsed: 00:01:47.57 > > SQL> alter system flush buffer_cache; > > SQL> update t1 set channel_id=1; > 918843 rows updated. > Elapsed: 00:01:49.15 > > SQL> alter system flush buffer_cache; > > t2 non composite > ----------------- > > SQL> update t2 set prod_id=0,cust_id=0,channel_id=0; > 918843 rows updated. > Elapsed: 00:04:56.23 > > SQL> alter system flush buffer_cache; > > SQL> update t2 set prod_id=1; > 918843 rows updated. > Elapsed: 00:01:24.75 > > SQL> alter system flush buffer_cache; > > SQL> update t2 set cust_id=1; > 918843 rows updated. > Elapsed: 00:01:29.17 > > SQL> update t2 set channel_id=1; > 918843 rows updated. > Elapsed: 00:01:29.31 > > -- > thanks and regards > ujang | oracle dba > jakarta | http://ora62.wordpress.com <http://ora62.wordpress.com> > > 2008/10/15 Yoel Susanto <[EMAIL PROTECTED] > <mailto:yoel.susanto%40gmail.com>>: > > Menurut pendapat saya yah tetep 1 SQL lebih cepat dari lebih dari 2 atau > > lebih SQL with or without index. > > > > alasan: > > 1. berapa kali oracle harus parse dan execute command > > 2. brapa kali oracle harus re-visit the blok > > 1 blok x 5 statement dibanding 1 blok x 1 statement > > 3. brapa jumlah undo & redo generated > > alasan sama dengan #2 > > 4. tergantung how big data & redo & undo generated > > berapa kali log switch, checkpoint dsb > > > > 1 sql tetep paleng murah. > > > > 2008/10/15 Ujang Jaenudin <[EMAIL PROTECTED] > <mailto:ujang.jaenudin%40gmail.com>> > > > >> hallo juragan, > >> > >> kalo indexnya composite dari kolom2 col_g sampai col_k seharusnya yg > >> pertama lebih cepat. karena sekali saja oracle memaintain index. > >> > >> tapi jika indexnya tidak komposite (masing2 kolom diindex) > >> statement berikutnya lebih kenceng, karena memaintain banyak index > >> sekaligus pada saat issue 1 line update maka akan lebih lambat... > >> > >> CMIIW :) > >> > >> -- > >> thanks and regards > >> ujang | oracle dba > >> jakarta | http://ora62.wordpress.com <http://ora62.wordpress.com> > >> > >> 2008/10/10 Yoenoes Lucas > >> <[EMAIL PROTECTED] > <mailto:yoenoes.lucas%40gmail.com><yoenoes.lucas%40gmail.com> > >> >: > >> > >> > Dear gurus, > >> > > >> > asumsi suatu table dengan jumlah record 2 juta record, dan > mempunyai 3 > >> > index biasa (bukan unique) > >> > > >> > saya mau update table tersebut dengan perintah sql sbb : > >> > > >> > update table_x set col_g = 0, col_h =0, col_i = 0, cold_j = 0, > cold_k = > >> 0; > >> > > >> > dibandingkan dengan > >> > > >> > update table_x set col_g = 0; > >> > update table_x set col_h = 0; > >> > update table_x set col_i = 0; > >> > update table_x set col_j = 0; > >> > update table_x set col_k = 0; > >> > > >> > mana yang lebih cepat perintah update yang di atas atau yang di > bawah? > >> > serta alasannya kenapa lebih cepat yah.. > >> > > >> > thx > >> > Yoenoes > >> > >
