Hi Cary, Thanks for the mail. We were thinking that clusters was the next option if this did not make a difference.
Regards, Madhavan On Fri, 27 Jun 2003 21:49:39 -0800, "Cary Millsap" <[EMAIL PROTECTED]> said: > Two features already exist that will preserve table physical order even > through various DML-motivated data transformations: > > - Clusters > - Index-organized tables > > Heap-ordered tables (that is, regular old tables) are designed not to > carry > any guarantees about physical order. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney > - Hotsos Symposium 2004, March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > > -----Original Message----- > Binley Lim > Sent: Friday, June 27, 2003 6:20 PM > To: Multiple recipients of list ORACLE-L > > > The ordering of the temp (driving) table to line up with the fact table > index-key order is a very powerful technique indeed. > > If the driving table is random wrt to the index ordering, it would have > to > look up an index block for each row, pin the block, do the update for > just a > key or two, and repeat the process for the next row. If the rows are > ordered, then it will only have to pin the block only once, and update > all > the rows in one pass and never visit that block again. Whereas jumping > all > round memory in random order is very expensive in terms of the code-path > that Oracle has to travel. Contrary to what you might have read about > HIT-RATIOS, the ratio of "buffer is pinned/not pinned" count gives a good > indication you might have a problem in the respect, especially in DSS > environments where such updates are not uncommon. > > The temp table ordering alone may not help all that much. Its because you > also ordered the fact table according to index key-order! Same concept > here > - access the table block once (or fewer times). Unfortunately, the fact > table ordering will deteriorate over time with DMLs. Now, if only a > future > Oracle release can do this re-ordering incrementally and quietly in the > background by SMON perhaps?... > > ----- Original Message ----- > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Saturday, June 28, 2003 5:09 AM > > > > -- 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Binley Lim > 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: Cary Millsap > 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). > -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - One of many happy users: http://www.fastmail.fm/docs/quotes.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur 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).
