Re: Correlated update

From: joel garry <joel-garry_at_home.com>
Date: Mon, 6 Dec 2010 09:18:45 -0800 (PST)
Message-ID: <45be1d0f-39fe-4488-993a-2b81a3be666f_at_f21g2000prn.googlegroups.com>

On Dec 5, 3:50 am, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> bob123 schreef:
>
>
>
> >>> Try posting the plan.  The distinct perhaps means a full table scan
> >>> of ...icld for each row of ...formitems. Would you happen to have any
> >>> indices? Does your cpu go nuts? Does statspack have something to say
> >>> about what is going on in that time period?
>
> > I update all the table T_SUBJECTVISITFORMITEMS
> > maybe I can avoid this with a CTAS ?
>
> > below the plan:
> > Plan
> > UPDATE STATEMENT ALL_ROWS Cost: 39,539 Bytes: 68,254,407 Cardinality:
> > 2,527,941
> > 5 UPDATE SYSTEM.T_SUBJECTVISITFORMITEMS
> > 1 TABLE ACCESS FULL TABLE SYSTEM.T_SUBJECTVISITFORMITEMS Cost: 39,539
> > Bytes: 68,254,407 Cardinality: 2,527,941
> > 4 HASH UNIQUE Cost: 3 Bytes: 27 Cardinality: 1
> > 3 TABLE ACCESS BY INDEX ROWID TABLE SYSTEM.T_ITEMFIRSTDATA_STATUS_ICLD
> > Cost: 2 Bytes: 27 Cardinality: 1
> > 2 INDEX RANGE SCAN INDEX SYSTEM.T_ITEMFIRSTDATA_STATUS Cost: 1
> > Cardinality: 1
>
> Wow, my mistake, Oracle does create indexes on its own.

lol!

bob123, Girard correctly pointed out the fishiness, you have to have that distinct in your select, which causes the range scan of the index. You may have a design problem, not being selective enough in what data you are updating with, so as to get it more directly. I'm not thinking too clearly yet this Monday morning, but I think a correct unique update would be faster than a CTAS or multiple runs, though if I'm not interpreting it correctly Girard's answer may be better. And of course, Oracle may come up with something better with a different query. You also want to post predicate information with a plan, see http://jonathanlewis.wordpress.com/2008/12/03/predicate-problems/ for an example.

Version information (select banner from v$version) is very often important too.

jg

--
_at_home.com is bogus.
http://www.bnet.com/blog/technology-business/google-8217s-losing-bid-for-groupon-a-lesson-in-negotiation/7172

Received on Mon Dec 06 2010 - 11:18:45 CST