Re: Optimization question: Unrolling subquery into IN clause

From: HansP <Hans-Peter.Sloot_at_atosorigin.com>
Date: Tue, 20 Oct 2009 00:14:29 -0700 (PDT)
Message-ID: <6be649bd-ac4b-4b69-b021-7085e0f75f9c_at_s6g2000vbp.googlegroups.com>

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 Received on Tue Oct 20 2009 - 02:14:29 CDT