Re: Optimization question: Unrolling subquery into IN clause
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Fri, 23 Oct 2009 09:27:31 -0700 (PDT)
Message-ID: <f4f9d68f-6b48-4be8-95c5-47bc8ab5f1dd_at_h2g2000vbd.googlegroups.com>
On Oct 23, 4:43 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> On Oct 23, 12:42 pm, Wolfram Roesler <w..._at_spam.la> wrote:
>
>
>
> > Randolf Geist <mah..._at_web.de> wrote innews:badd2686-0166-47fa-a260-bfd7c7c43d01_at_e34g2000vbm.googlegroups.com:
>
> > > So if the TAB2.COL3 column is skewed and the value "L" is only showing
> > > up 20 times, then you need to tell the optimizer about that and add a
> > > histogram on this column, if it's not there already.
>
> > That was the reason of the problem. I fixed the histograms on the
> > column in question, and now my query finishes in less than a second.
> > For the version with bind variables, this means a speed-up by a
> > factor of 1000.
>
> > The problem is thus completely understood and solved.
> > Thanks to all who helped!
>
> > Best regards
> > W. Rösler
>
> Great! that your issue is fixed.
>
> But you earlier mentioned that you dont use histograms:
> "There are no histograms."
Date: Fri, 23 Oct 2009 09:27:31 -0700 (PDT)
Message-ID: <f4f9d68f-6b48-4be8-95c5-47bc8ab5f1dd_at_h2g2000vbd.googlegroups.com>
On Oct 23, 4:43 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> On Oct 23, 12:42 pm, Wolfram Roesler <w..._at_spam.la> wrote:
>
>
>
> > Randolf Geist <mah..._at_web.de> wrote innews:badd2686-0166-47fa-a260-bfd7c7c43d01_at_e34g2000vbm.googlegroups.com:
>
> > > So if the TAB2.COL3 column is skewed and the value "L" is only showing
> > > up 20 times, then you need to tell the optimizer about that and add a
> > > histogram on this column, if it's not there already.
>
> > That was the reason of the problem. I fixed the histograms on the
> > column in question, and now my query finishes in less than a second.
> > For the version with bind variables, this means a speed-up by a
> > factor of 1000.
>
> > The problem is thus completely understood and solved.
> > Thanks to all who helped!
>
> > Best regards
> > W. Rösler
>
> Great! that your issue is fixed.
>
> But you earlier mentioned that you dont use histograms:
> "There are no histograms."
That is what I thought. I stopped responding because it sounded like all of the information wasn't being posted. Editing queries, "left that out", etc. Received on Fri Oct 23 2009 - 11:27:31 CDT