Re: Optimization question: Unrolling subquery into IN clause

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Tue, 20 Oct 2009 07:05:36 -0700 (PDT)
Message-ID: <5e5e5fa9-13b8-4181-b8ad-c6126b861ea3_at_y10g2000prg.googlegroups.com>

On Oct 20, 6:58 pm, Wolfram Roesler <w..._at_spam.la> wrote:
> Ind-dba <oraclear..._at_googlemail.com> wrote innews:0a668e04-8222-4746-aeb3-f4d878fcfe32_at_b3g2000pre.googlegroups.com:
>
> ...>         SELECT /*+ cardinality(20) */ key
>
> ...
>
> That changes nothing.
>
> > If parameters are passed as binds - could you rewrite the query with a
> > dummy comment to confirm the sql gets parsed again - this is rule of
> > any bind peeking/histogram effect. Do you have histograms on the
> > table/ columns?
>
> There are no histograms.
>
> Thanks for your help
> W. Rösler

But Did it change the cardinality?

Could you share:

explain plan for
SELECT several_columns
FROM tab1
WHERE col1='Value'
AND col2 IN
(

        SELECT /*+ cardinality(20) */ key
        FROM tab2
        WHERE tab2.col3='Something'

);

select * from table(dbms_xplan.display);

Also possiblly share the o/p of this (2nd sql):

SELECT /*+ gather_plan_statistics */ several_columns FROM tab1
WHERE col1='Value'
AND col2 IN
(

        SELECT /*+ cardinality(20) */ key
        FROM tab2
        WHERE tab2.col3='Something'

);

Select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); Received on Tue Oct 20 2009 - 09:05:36 CDT