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).
Received on Mon Jun 30 2003 - 00:45:26 CDT
