Pak Ujang,
menarik nih....
"so, lama tidaknya tergantung composite or non composite :)"
bisa gak kasih tau kenapa hal ini bisa berbeda?
1. composite --> 1 index 3 column (3 column ini dalam 1 blok) -- less data
block to process
2. non-composite --> 3 index masing2 1 column (1 column dalam 1 block) --
more data block to process
(karena 3 index/segment blok datanya gak bisa
campur)
Jadi data blocknya antara composite dan non-composite adalah 1:3.
kenapa yang lebih banyak data block bisa lebih cepat dari less data block.
2008/10/15 Ujang Jaenudin <[EMAIL PROTECTED]>
> 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
>
> 2008/10/15 Yoel Susanto <[EMAIL PROTECTED] <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]<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
> >>
> >> 2008/10/10 Yoenoes Lucas
> >> <[EMAIL PROTECTED] <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
> >>
>
>
--
Cheers,
Yoel Susanto
www.indo-oracletech.com
[Non-text portions of this message have been removed]
