Re: Optimization question: Unrolling subquery into IN clause

From: Wolfram Roesler <wr_at_spam.la>
Date: Mon, 19 Oct 2009 13:04:02 +0000 (UTC)
Message-ID: <Xns9CA99946A9F1wrgrpde_at_news.albasani.net>

Steve Howard <stevedhoward_at_gmail.com> wrote in news: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 Received on Mon Oct 19 2009 - 08:04:02 CDT