Re: Optimization question: Unrolling subquery into IN clause
From: Ind-dba <oraclearora_at_googlemail.com>
Date: Fri, 23 Oct 2009 01:43:36 -0700 (PDT)
Message-ID: <35967f4c-1c86-495f-a194-5ca9a5dc092f_at_b25g2000prb.googlegroups.com>
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
Date: Fri, 23 Oct 2009 01:43:36 -0700 (PDT)
Message-ID: <35967f4c-1c86-495f-a194-5ca9a5dc092f_at_b25g2000prb.googlegroups.com>
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." Received on Fri Oct 23 2009 - 03:43:36 CDT