RE: Which method is more efficient
Perhaps it is late to answer, but you might want to have a table that is populated with PK and column values, via column level trigger indicating the changed column and data? Aleem -Original Message- Sent: Wednesday, May 28, 2003 10:21 PM To: Multiple recipients of list ORACLE-L Subject:Re: Which method is more efficient oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1)Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2)Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
Re: Which method is more efficient
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 3:14 AM 1. to totally eliminate redo, load your staging records into a global temp table. it has absolutely no redo and is very fast. Hi The above is not quite true. Global temp tables *do* generate redo (albeit indirectly) and potentially quite a bit of it. The point to make here is that changes to GTT generate undo and this undo makes changes to undo segments and these changes subsequently generate redo. So depending on the type of DML (eg. deletes) and the volume of changes, you can end up generating quite a bit of the redo. Not as much as a non-GTT but enough to certainly invalidate the above statement. Cheers Richard Foote -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Which method is more efficient
so GTT's help reducing redo when you do inserts and deletes? but not for deletes? there was a post by tom kyte on dejanews from a few years back where someone asked the redo question and he recommended GTTs. here is the link... did I misread it? http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=89r5a7%249q3%241%40nnrp1.deja.comrnum=1prev=/groups%3Fq%3Doracle%2Bglobal%2Btemp%2B%2Bkyte%2Bredo%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch From: Richard Foote [EMAIL PROTECTED] Date: 2003/05/29 Thu AM 08:45:46 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Which method is more efficient - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 3:14 AM 1. to totally eliminate redo, load your staging records into a global temp table. it has absolutely no redo and is very fast. Hi The above is not quite true. Global temp tables *do* generate redo (albeit indirectly) and potentially quite a bit of it. The point to make here is that changes to GTT generate undo and this undo makes changes to undo segments and these changes subsequently generate redo. So depending on the type of DML (eg. deletes) and the volume of changes, you can end up generating quite a bit of the redo. Not as much as a non-GTT but enough to certainly invalidate the above statement. Cheers Richard Foote -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Which method is more efficient
oh it wasnt none, it was cut in half. I skimmed it. my bad From: Richard Foote [EMAIL PROTECTED] Date: 2003/05/29 Thu AM 08:45:46 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Which method is more efficient - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 3:14 AM 1. to totally eliminate redo, load your staging records into a global temp table. it has absolutely no redo and is very fast. Hi The above is not quite true. Global temp tables *do* generate redo (albeit indirectly) and potentially quite a bit of it. The point to make here is that changes to GTT generate undo and this undo makes changes to undo segments and these changes subsequently generate redo. So depending on the type of DML (eg. deletes) and the volume of changes, you can end up generating quite a bit of the redo. Not as much as a non-GTT but enough to certainly invalidate the above statement. Cheers Richard Foote -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which method is more efficient
Jared - Thanks. Wow, I learn a new trick each day on this list!
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L
There are easier ways to test redo generation than mucking
about with logminer.
Update only the column that changes and check redo generation:
15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size0
1 row selected.
461 rows updated.
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size 117,128
1 row selected.
Update all columns, only a single column has actually changed:
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size0
1 row selected.
461 rows updated.
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size 226,908
1 row selected.
Updating just the changed field is clearly more efficient. I didn't test
a comparison
between multiple columns, updating 1 at a time versus all at once, change
only
columns with changed data.
Doing so would require all redo and rollback overhead N number of times
rather
than just once, N being the number of changed columns.
Below are the scripts used for testing.
Jared
=
-- create test table
create table redo_test
as select
OWNER
,TABLE_NAME
,TABLESPACE_NAME
,CLUSTER_NAME
,IOT_NAME
,PCT_FREE
,PCT_USED
,INI_TRANS
,MAX_TRANS
,INITIAL_EXTENT
,NEXT_EXTENT
,MIN_EXTENTS
,MAX_EXTENTS
,PCT_INCREASE
,FREELISTS
,FREELIST_GROUPS
,LOGGING
,BACKED_UP
,NUM_ROWS
,BLOCKS
,EMPTY_BLOCKS
,AVG_SPACE
,CHAIN_CNT
,AVG_ROW_LEN
from dba_tables
nologging
/
=
-- redo.sql
-- check redo size
col sid format 999 head 'SID'
col name format a40
col value format ,999,999,999 head 'VALUE'
col username format a10 head 'USERNAME'
break on username skip 1 on sid skip 1
select
sess.username,
stat.sid,
name.name name,
stat.value
from v$sesstat stat, v$statname name, v$session sess
where
stat.sid = sess.sid
and stat.sid = (
select s.sid
from v$session s, v$process p
where p.addr = s.paddr
and userenv('SESSIONID') = s.audsid
)
and stat.statistic# = name.statistic#
and name.name like 'redo size'
order by name
=
-- test 1
@redo
update redo_test
set tablespace_name = reverse(tablespace_name)
/
@redo
rollback;
=
-- test 2
@redo
update redo_test
set
owner = owner
, tablespace_name = reverse(tablespace_name)
, table_name = table_name
, pct_used = pct_used
, pct_free = pct_free
, ini_trans = ini_trans
, max_trans = max_trans
, initial_extent = initial_extent
, next_extent = next_extent
, min_extents = min_extents
, max_extents = max_extents
, pct_increase = pct_increase
, freelists = freelists
, num_rows = num_rows
, blocks = blocks
, empty_blocks = empty_blocks
, avg_space = avg_space
, chain_cnt = chain_cnt
, avg_row_len = avg_row_len
/
@redo
rollback;
=
DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/28/2003 09:59 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Which method is more efficient
Bryan - If this is a critical issue, I would try it both ways on a test
database and use log miner to examine the amount of redo that is
generated.
My recollection is that you will find that the redo record records the
before and after data for each field. So just updating all fields may
generate significantly more redo. But don't trust my recollection on this
issue, test it yourself.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L
Hello everyone,
I have a question for the group of which
RE: RE: Which method is more efficient
Yes, I am in archive log mode and I have had 2 occurances of filling up the archive log filesystem, but I don't want to change one problem for another. Bryan -Original Message- Sent: Wednesday, May 28, 2003 3:20 PM To: Multiple recipients of list ORACLE-L have you run it? isnt that alot slower? you have alot of context switches also. for every record to update, you then switch to SQL. what kind of efficiency improvement are you going for? Speed or cutting down on redo? Are you in archivelog mode and dont want to blow up your archives? From: Rodrigues, Bryan [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 02:40:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Which method is more efficient The fields that are changed are determined by 1) A loop would start until all records in parts change table are done 2) Select a part record from the part changes table 3) Select the same part from the existing part table 4) Compare the value in the parts changes table against the corresponding field in the part table 5) After comparing all fields in the records, create record in a seperate work table with the values populated with null if the field values matched and the new value if the values did not. 6) This loop would continue until all parts are done. 7) After any records in the work table where all fields (outside of part number) are null are deleted. This process normally will decrease the number of records to be processed after this point by 75%. Hope that helps, Bryan -Original Message- Sent: Wednesday, May 28, 2003 1:21 PM To: Multiple recipients of list ORACLE-L oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1) Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2) Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS
RE: Which method is more efficient
Title: RE: Which method is more efficient Jared, Agreed, but what about the resources needs to find _which_ column changed ?? Would that offset the extra redo generated? Heck, I'd just generate the update statements based on two tables to _only_ update the changed columns. It is pretty easy, if both tables have _same_ columns ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, May 28, 2003 6:15 PM To: Multiple recipients of list ORACLE-L There are easier ways to test redo generation than mucking about with logminer. Update only the column that changes and check redo generation: 15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1 [ much stuff deleted ] This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Which method is more efficient
dunno about that. I was making the assumption that *which* columns changed was already known. This would require testing by someone familiar with the data. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 10:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Which method is more efficient Jared, Agreed, but what about the resources needs to find _which_ column changed ?? Would that offset the extra redo generated? Heck, I'd just generate the update statements based on two tables to _only_ update the changed columns. It is pretty easy, if both tables have _same_ columns ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, May 28, 2003 6:15 PM To: Multiple recipients of list ORACLE-L There are easier ways to test redo generation than mucking about with logminer. Update only the column that changes and check redo generation: 15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1 [ much stuff deleted ] This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: RE: Which method is more efficient
ok so your goal is not speed. its reducing redo, correct? so you dont care if its slow? try using a global temp table. that may cut down your redo significantly. From: Rodrigues, Bryan [EMAIL PROTECTED] Date: 2003/05/29 Thu PM 12:44:52 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Which method is more efficient Yes, I am in archive log mode and I have had 2 occurances of filling up the archive log filesystem, but I don't want to change one problem for another. Bryan -Original Message- Sent: Wednesday, May 28, 2003 3:20 PM To: Multiple recipients of list ORACLE-L have you run it? isnt that alot slower? you have alot of context switches also. for every record to update, you then switch to SQL. what kind of efficiency improvement are you going for? Speed or cutting down on redo? Are you in archivelog mode and dont want to blow up your archives? From: Rodrigues, Bryan [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 02:40:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Which method is more efficient The fields that are changed are determined by 1) A loop would start until all records in parts change table are done 2) Select a part record from the part changes table 3) Select the same part from the existing part table 4) Compare the value in the parts changes table against the corresponding field in the part table 5) After comparing all fields in the records, create record in a seperate work table with the values populated with null if the field values matched and the new value if the values did not. 6) This loop would continue until all parts are done. 7) After any records in the work table where all fields (outside of part number) are null are deleted. This process normally will decrease the number of records to be processed after this point by 75%. Hope that helps, Bryan -Original Message- Sent: Wednesday, May 28, 2003 1:21 PM To: Multiple recipients of list ORACLE-L oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1)Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2)Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
Re: RE: Which method is more efficient
how would you do that? if you dont know which columns have changed values? you would have to compare every column? how else can you do it? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/05/29 Thu PM 01:24:43 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Jared, Agreed, but what about the resources needs to find _which_ column changed ?? Would that offset the extra redo generated? Heck, I'd just generate the update statements based on two tables to _only_ update the changed columns. It is pretty easy, if both tables have _same_ columns ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, May 28, 2003 6:15 PM To: Multiple recipients of list ORACLE-L There are easier ways to test redo generation than mucking about with logminer. Update only the column that changes and check redo generation: 15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1 [ much stuff deleted ] This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 Title: RE: Which method is more efficient Jared, Agreed, but what about the resources needs to find _which_ column changed ?? Would that offset the extra redo generated? Heck, I'd just generate the update statements based on two tables to _only_ update the changed columns. It is pretty easy, if both tables have _same_ columns ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, May 28, 2003 6:15 PM To: Multiple recipients of list ORACLE-L There are easier ways to test redo generation than mucking about with logminer. Update only the column that changes and check redo generation: 15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1 [ much stuff deleted ]
RE: Which method is more efficient
Jared, That is correct the fields that are changed is known before going to this point. Bryan P.S. I just want to say thank you for all the responses to this question. We are still talking about the issue, but I am thankful for the input. -Original Message- Sent: Thursday, May 29, 2003 2:10 PM To: Multiple recipients of list ORACLE-L dunno about that. I was making the assumption that *which* columns changed was already known. This would require testing by someone familiar with the data. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 10:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Which method is more efficient Jared, Agreed, but what about the resources needs to find _which_ column changed ?? Would that offset the extra redo generated? Heck, I'd just generate the update statements based on two tables to _only_ update the changed columns. It is pretty easy, if both tables have _same_ columns ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, May 28, 2003 6:15 PM To: Multiple recipients of list ORACLE-L There are easier ways to test redo generation than mucking about with logminer. Update only the column that changes and check redo generation: 15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1 [ much stuff deleted ] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which method is more efficient
Title: RE: Which method is more efficient I vote for the whole record, you'd be spending more time and resources in finding _which_ field has changed. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Rodrigues, Bryan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 28, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Subject: Which method is more efficient Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1) Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2) Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Which method is more efficient
1. to totally eliminate redo, load your staging records into a global temp table. it has absolutely no redo and is very fast. 2. if your in 9i, use an external table and a merge command and update the table directly from the file. dont even load the records to a staging table. Do a search for this on asktom. He explains it in detail. 3. This is the fastest update statement you can right... UPDATE(select a.col1 a_col1, b.col1 b_col1 from tab1 a, tab2 b where a.pk = b.pk) set a_col1 = b_col1; need primary keys on both tables or it wont work. This is hands down the fastest update you can do. From: Rodrigues, Bryan [EMAIL PROTECTED] Date: 2003/05/28 Wed AM 11:49:49 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Which method is more efficient Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1)Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2)Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which method is more efficient
Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1) Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2) Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which method is more efficient
Bryan, First item is to define efficient. Are you looking for efficiency in the update of data on the production database or the standby? The code needed to determine which columns need updating is going to be a real bear and could create several update statements per record. On the other hand the standby is going to expend the same amount of effort one way as the other. Personally I'd update everything, except the primary key. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, May 28, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1) Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2) Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Which method is more efficient
oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1)Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2)Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Which method is more efficient
The fields that are changed are determined by 1) A loop would start until all records in parts change table are done 2) Select a part record from the part changes table 3) Select the same part from the existing part table 4) Compare the value in the parts changes table against the corresponding field in the part table 5) After comparing all fields in the records, create record in a seperate work table with the values populated with null if the field values matched and the new value if the values did not. 6) This loop would continue until all parts are done. 7) After any records in the work table where all fields (outside of part number) are null are deleted. This process normally will decrease the number of records to be processed after this point by 75%. Hope that helps, Bryan -Original Message- Sent: Wednesday, May 28, 2003 1:21 PM To: Multiple recipients of list ORACLE-L oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1)Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2)Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: RE: Which method is more efficient
have you run it? isnt that alot slower? you have alot of context switches also. for every record to update, you then switch to SQL. what kind of efficiency improvement are you going for? Speed or cutting down on redo? Are you in archivelog mode and dont want to blow up your archives? From: Rodrigues, Bryan [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 02:40:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Which method is more efficient The fields that are changed are determined by 1) A loop would start until all records in parts change table are done 2) Select a part record from the part changes table 3) Select the same part from the existing part table 4) Compare the value in the parts changes table against the corresponding field in the part table 5) After comparing all fields in the records, create record in a seperate work table with the values populated with null if the field values matched and the new value if the values did not. 6) This loop would continue until all parts are done. 7) After any records in the work table where all fields (outside of part number) are null are deleted. This process normally will decrease the number of records to be processed after this point by 75%. Hope that helps, Bryan -Original Message- Sent: Wednesday, May 28, 2003 1:21 PM To: Multiple recipients of list ORACLE-L oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1) Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2) Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
RE: RE: Which method is more efficient
Title: RE: RE: Which method is more efficient Bryan, Can you ... create table my_work_table as select * from changed_parts_table minus select * from existing_parts_table / The result will give you all the rows where _something_ is different between your existing table and changed table. This will cut down a lot on your processing. Afterwards, you can drop the my_work_table. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Rodrigues, Bryan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 28, 2003 2:40 PM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Which method is more efficient The fields that are changed are determined by 1) A loop would start until all records in parts change table are done 2) Select a part record from the part changes table 3) Select the same part from the existing part table 4) Compare the value in the parts changes table against the corresponding field in the part table 5) After comparing all fields in the records, create record in a seperate work table with the values populated with null if the field values matched and the new value if the values did not. 6) This loop would continue until all parts are done. 7) After any records in the work table where all fields (outside of part number) are null are deleted. This process normally will decrease the number of records to be processed after this point by 75%. Hope that helps, Bryan -Original Message- Sent: Wednesday, May 28, 2003 1:21 PM To: Multiple recipients of list ORACLE-L oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-L Hello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1) Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2) Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED
RE: RE: Which method is more efficient
Title: RE: RE: Which method is more efficient And with CTAS you can specify nologging to minimize redo generation. "Cloning" a table, renaming/dropping the source, and renaming the clone to the production table could be interesting. You would have to recreate indexes. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 1:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: Which method is more efficient Bryan, Can you ... create table my_work_table as select * from changed_parts_table minus select * from existing_parts_table / The result will give you all the rows where _something_ is different between your existing table and changed table. This will cut down a lot on your processing. Afterwards, you can drop the my_work_table. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Rodrigues, Bryan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 28, 2003 2:40 PM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Which method is more efficient The fields that are changed are determined by 1) A loop would start until all records in parts change table are done 2) Select a part record from the part changes table 3) Select the same part from the existing part table 4) Compare the value in the parts changes table against the corresponding field in the part table 5) After comparing all fields in the records, create record in a seperate work table with the values populated with null if the field values matched and the new value if the values did not. 6) This loop would continue until all parts are done. 7) After any records in the work table where all fields (outside of part number) are null are deleted. This process normally will decrease the number of records to be processed after this point by 75%. Hope that helps, Bryan -Original Message- Sent: Wednesday, May 28, 2003 1:21 PM To: Multiple recipients of list ORACLE-L oh i missed part of it. the question is how do you figure out which fields have changed? if you have to do an anti-join on each field, then do an update of every field. the question is how will you determine which fields have changed? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/05/28 Wed PM 12:59:51 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Which method is more efficient Bryan - If this is a critical issue, I would try it both ways on a test database and use log miner to examine the amount of redo that is generated. My recollection is that you will find that the redo record records the before and after data for each field. So just updating all fields may generate significantly more redo. But don't trust my recollection on this issue, test it yourself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED]-Original Message- Sent: Wednesday, May 28, 2003 10:50 AM To: Multiple recipients of list ORACLE-LHello everyone, I have a question for the group of which method is more efficient. To set the stage my company has a process to load part changes from vendors into the tables in an 8.1.7.4 Oracle database with archiving on and this database has a standby database at disaster recovery site, so nologging is not an option. There is a discussion going on as to which method is more effective for updating the information in a table. In looking at effectiveness, I am looking at reducing the amount of redo information produced and having the database do the least amount of work. 1) Method 1 is to update the information only for the fields that have changed, 1 field at a time. 2) Method 2 is to update the information for all the fields in the record whether they have changed or not, 1 record at a time. The size of the record is 1843 bytes and the distribution of field sizes: 2 fields varchar2(240). 1 field varchar2(150) 15 fields varchar2(50) 1 field varchar2(3) 2 fields varchar2(20) 4 fields varchar2(40) 3 fields varchar2(1) 2 fields varchar2(25) 2 fields number(10,2) 1 field number(13,2) 1 field number(1) 1 field number 1 field varchar2(6) 1 field number (17,2) 1 field varchar2(4) 3 fields that are date. In the past couple of months the average number of fields changed per record was 3 to 4 fields per record. Thanks for your help, Bryan Rodrigues Oracle DBA Elcom, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat Ci
RE: Which method is more efficient
There are easier ways to test redo generation than mucking
about with logminer.
Update only the column that changes and check redo generation:
15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @t1
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size0
1 row selected.
461 rows updated.
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size 117,128
1 row selected.
Update all columns, only a single column has actually changed:
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size0
1 row selected.
461 rows updated.
USERNAMESID NAME VALUE
-- -
JKSTILL 10 redo size 226,908
1 row selected.
Updating just the changed field is clearly more efficient. I didn't test
a comparison
between multiple columns, updating 1 at a time versus all at once, change
only
columns with changed data.
Doing so would require all redo and rollback overhead N number of times
rather
than just once, N being the number of changed columns.
Below are the scripts used for testing.
Jared
=
-- create test table
create table redo_test
as select
OWNER
,TABLE_NAME
,TABLESPACE_NAME
,CLUSTER_NAME
,IOT_NAME
,PCT_FREE
,PCT_USED
,INI_TRANS
,MAX_TRANS
,INITIAL_EXTENT
,NEXT_EXTENT
,MIN_EXTENTS
,MAX_EXTENTS
,PCT_INCREASE
,FREELISTS
,FREELIST_GROUPS
,LOGGING
,BACKED_UP
,NUM_ROWS
,BLOCKS
,EMPTY_BLOCKS
,AVG_SPACE
,CHAIN_CNT
,AVG_ROW_LEN
from dba_tables
nologging
/
=
-- redo.sql
-- check redo size
col sid format 999 head 'SID'
col name format a40
col value format ,999,999,999 head 'VALUE'
col username format a10 head 'USERNAME'
break on username skip 1 on sid skip 1
select
sess.username,
stat.sid,
name.name name,
stat.value
from v$sesstat stat, v$statname name, v$session sess
where
stat.sid = sess.sid
and stat.sid = (
select s.sid
from v$session s, v$process p
where p.addr = s.paddr
and userenv('SESSIONID') = s.audsid
)
and stat.statistic# = name.statistic#
and name.name like 'redo size'
order by name
=
-- test 1
@redo
update redo_test
set tablespace_name = reverse(tablespace_name)
/
@redo
rollback;
=
-- test 2
@redo
update redo_test
set
owner = owner
, tablespace_name = reverse(tablespace_name)
, table_name = table_name
, pct_used = pct_used
, pct_free = pct_free
, ini_trans = ini_trans
, max_trans = max_trans
, initial_extent = initial_extent
, next_extent = next_extent
, min_extents = min_extents
, max_extents = max_extents
, pct_increase = pct_increase
, freelists = freelists
, num_rows = num_rows
, blocks = blocks
, empty_blocks = empty_blocks
, avg_space = avg_space
, chain_cnt = chain_cnt
, avg_row_len = avg_row_len
/
@redo
rollback;
=
DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/28/2003 09:59 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Which method is more efficient
Bryan - If this is a critical issue, I would try it both ways on a test
database and use log miner to examine the amount of redo that is
generated.
My recollection is that you will find that the redo record records the
before and after data for each field. So just updating all fields may
generate significantly more redo. But don't trust my recollection on this
issue, test it yourself.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L
Hello everyone,
I have a question for the group of which method is more efficient.
To set the stage my company has a process to load part changes from
vendors
into the tables in an 8.1.7.4 Oracle database with archiving on and this
database has a standby database at disaster recovery site, so nologging
