Re: Optimization question: Unrolling subquery into IN clause

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Tue, 20 Oct 2009 03:51:32 -0700 (PDT)
Message-ID: <0a668e04-8222-4746-aeb3-f4d878fcfe32_at_b3g2000pre.googlegroups.com>

On Oct 20, 12:14 pm, HansP <Hans-Peter.Sl..._at_atosorigin.com> wrote:
> On Oct 19, 3:04 pm, Wolfram Roesler <w..._at_spam.la> wrote:
>
>
>
>
>
> > Steve Howard <stevedhow..._at_gmail.com> wrote innews:c365a644-242b-4226-92df-1a16bb1fd53c_at_a32g2000yqm.googlegroups.com:
>
> > > What plan does it show when you pass in the list rather than use the
> > > subquery?
>
> > With the expanded list (without subquery) the explain plan looks
> > like this (index1 is the index on tab1.col1):
>
> > -------------------------------------------------------------------------
> > -----------------
> > | Id  | Operation                    | Name      | Rows  | Bytes | Cost
> > (%CPU)| Time     |
> > -------------------------------------------------------------------------
> > -----------------
> > |   0 | SELECT STATEMENT             |           |   855 | 62415 |   161  
> > (0)| 00:00:02 |
> > |   1 |  INLIST ITERATOR             |           |       |       |            
> > |          |
> > |   2 |   TABLE ACCESS BY INDEX ROWID| tab1      |   855 | 62415 |   161  
> > (0)| 00:00:02 |
> > |*  3 |    INDEX RANGE SCAN          | index1    |   855 |       |    44  
> > (0)| 00:00:01 |
> > -------------------------------------------------------------------------
> > -----------------
>
> > Outline Data
> > -------------
>
> >   /*+
> >       BEGIN_OUTLINE_DATA
> >       INDEX_RS_ASC(_at_"SEL$1" "tab1"@"SEL$1" ("tab1"."col2"))
> >       OUTLINE_LEAF(_at_"SEL$1")
> >       ALL_ROWS
> >       OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
> >       IGNORE_OPTIM_EMBEDDED_HINTS
> >       END_OUTLINE_DATA
> >   */
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> >    3 - access(("col2"='Value1' OR "col2"='Value2'
> >               ...
> >               "col2"='Value20') AND "col1"='Value')
>
> > > Also, are these bind variables or literals you are using in
> > > the actual query?  I ask because you said it should only return 300
> > > rows total, so this looks like the problem...
>
> > I'm using bind variables in my application, which makes it even slower.
> > The plan with bind variables is something like this (this is from tkprof
> > and from the slightly more complex version of the query issued by
> > the application):
>
> > Rows     Execution Plan
> > -------  ---------------------------------------------------
> >       0  SELECT STATEMENT   MODE: ALL_ROWS
> >     260   SORT (ORDER BY)
> >     260    HASH JOIN (RIGHT OUTER)
> >  651633     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'tab2' (TABLE)
> >  651633     HASH JOIN
> >  651633      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'tab2' (TABLE)
> >  651633      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'tab1' (TABLE)
>
> > Thanks for your help
> > W. Rösler
>
> Hi Wolfram,
>
> If the sub query yields 20 distinct values and no null values, I think
> you could try to rewrite the query as:
>
> SELECT tab1.several_columns
> FROM tab1, tab2
> WHERE tab.col1='Value'
> and tab2.col3='Something'
> and tab1.col2=tab2.key
>
> Regards Hansp- Hide quoted text -
>
> - Show quoted text -

As you said in your first post:
"However, only 20 rows of tab2 match the subquery, and only 300 rows of tab1 match the entire query."

Could you try the following

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

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

);

This way the hint will be telling optimizer on the no of rows that should be returned by subquery.

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?

Regards,
Sachin Received on Tue Oct 20 2009 - 05:51:32 CDT