Re: RE: tuning

ok, my tuning for this comes from Guy harrisons book. he says then if you do the following:

update tab1
  set (col1, col2, etc...) =

         (select b.col1,b.col2, etc...
             from tab2
           where tab1.primary_key = tab2.primary_key)
where exists (select 'x'
             from tab2
           where tab1.primary_key = tab2.primary_key)

When I change it to a cursor for loop and put my update statement inside the cursor and update based on the ROWID it takes twice as long to run. Even metalink says this should be faster.

Any ideas at??? Plan for the Update with where exists is as good as its going to get. All join columns are indexed.

We sometimes have 50 gigabytes plus of data to update and insert a night and we may be moving upwards of 1 TB to a new data mining system, so I need to make this as fast as possible.

>
> From: "Nicoll, Iain" <IAIN.D.NICOLL_at_saic.com>
> Date: 2003/05/08 Thu PM 12:57:16 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: tuning
>
> I'd imagine it should be faster if the only data you're after is in the
> primary keys. Isn't a fast full index scan the equivalent of a full table
> scan (where it's not doing any filtering) but if you need data that's not in
> the index you have to go out to the table for every row.
>
> Iain Nicoll
>
> -----Original Message-----
> Sent: 08 May 2003 16:57
> To: Multiple recipients of list ORACLE-L
>
>
> If two tables are the same size and you can join on a unique Primary key
> index, should Nested Loop join with fast full index scan be the fastest
> method?
>
> Im finding that a hash join or sort merge is faster?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> 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: ListGuru_at_fatcity.com (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: Nicoll, Iain
> INET: IAIN.D.NICOLL_at_saic.com
>
> 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: ListGuru_at_fatcity.com (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: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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: ListGuru_at_fatcity.com (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).