-- Sorry the earlier post was incomplete.
We were running a serial update on a fact table (45 mill rows) using the
old tech of
declare
cursor .......table temp
begin
for c1rec in c1 loop
update fact
where period_key = c1rec.period_key and loan_key = c1rec.loan_key
----commit every 10,000 rows
end loop;
end;
fACT table partitioned on period key (per month) and there was a unique
local index on period_key and loan_key
the update was going at a rate of 10,000 rows every 1.5 min. very
slow...
Myself and Madhavan(another avid reader of this list) came up with
something....
When we looked at the cluster factor the index..it was close to the
number of rows..so we decided to rearrange the fact table with period_key
and mortgage_loan_key. The cluster factor of the index now came close to
the number of blocks per partition but On running the above query, the
waits on sequential file reads were still very significant.
Then we did one last thing were we arranged the temp table(table in the
cursor) also with period key and mortgage loan key. and Voila the query
was running at 10,000 rows every 4 sec.
unbelivable..db_file_sequential_read waits very minimal and disks reads
also very minial..query ran from 69 hours to 5 hours.
Thought i shall share with you folks...
Sathish.
--
http://www.fastmail.fm - Access all of your messages and folders
wherever you are
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
