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]>:
> 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]>
>
>> 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>
>> >:
>>
>> > 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
>>
