dear yoel,
sebenarnya testing tsb tidak mencerminkan applicable untuk semua
environment....maklum lah namanya juga wind blowz dengan hardisknya
cuman 1 biji, IDE pula :)
sebenarnya bisa saja update table yg statement pertama bisa lebih
kencang untuk kasus index-index pada table t2, asalkan index tsb
berada di physical disk yg berbeda.
no matter how much blocks it's occupied :)
tapi berapa ter-spreadnya block tsb di physical disk berbeda.
karena sewaktu issue update tsb oracle harus maintain 3 index
berbarengan, jika physical disk cuman satu seperti di mesin saya, ya
wajar toh bakal collision di disk...
FYI,
SGA di mesin ini terbatas, cuman 400MB, sga_target=384MB
SQL> select segment_name,blocks,bytes from dba_segments where
segment_name in ('T1','T2') and owner='SH';
SEGMENT_NAME BLOCKS BYTES
-------------------- ---------- ----------
T1 4608 37748736
T2 4608 37748736
SQL> select segment_name,blocks,bytes from dba_segments where
segment_name like '%IDX%' and owner='SH';
SEGMENT_NAME BLOCKS BYTES
-------------------- ---------- ----------
T1_IDX_CMP 20480 167772160
T2_IDX_TIME 8192 67108864
T2_IDX_CUST 9216 75497472
T2_IDX_CHANNEL 8064 66060288
dari angka2 tsb, mustinya SGA sudah bisa keep, tapi testing sengaja
saya abaikan SGA dgn flush buffer_cache.
just my $0.02 opinion.
btw, pak yoenoes, kok yg bersangkutan gak undang2 ??
--
thanks and regards
ujang | oracle dba
jakarta | http://ora62.wordpress.com
2008/10/16 Yoel Susanto <[EMAIL PROTECTED]>:
> 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
>> >>
