Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Tue, 17 Nov 2009 16:59:31 -0700

Do you really think this is a problem with the statistics itself?  It ran
well in 9 using the same statistics gathering methods.  I've tried computing
histograms on pm_intake.pcn (its the PK of wfs_reg_intake_workers).

I am taking a closer look at the SQL and think it's quite horrible.  For
starters, I can't even see why the group by is even in the query.  Removing
the group by clause removes the hash group by and then I'm back to my
regular execution times.

The second query I included wasn't the best example.  At the time I thought
it pertained to all updates with correlated queries. I now believe that it
only pertains to updates with correlated queries that have a subquery
containing a group by clause.  What a mouthful!

On Tue, Nov 17, 2009 at 4:00 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> A 100% sample will generally be OK for single table cardinality, but
> it may not mean the join cardinality is accurate.
> Look at the plan line #2 in the first example.
> The HASH JOIN version has a cardinality of 8775, the HASH JOIN RIGHT
> SEMI version has a cardinality of 19.  That's a difference of 462x.
> Pretty big!
> How many rows are really returned?
> In the second example the access order and path is identical, the only
> difference being lines 6 & 9 where one is SORT UNIQUE and the other is
> HASH UNIQUE.  I'm wondering how much difference there really is
> between these two.  I would think very little, but what is it?
> On Tue, Nov 17, 2009 at 2:28 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> > I'm using
> > estimate_percent=>100 and method_opt=>'FOR ALL COLUMNS SIZE 1' for these
> > tables
> --
> Regards,
> Greg Rahn

Other related posts: