Re: Optimization question: Unrolling subquery into IN clause

From: Wolfram Roesler <wr_at_spam.la>
Date: Mon, 19 Oct 2009 06:46:39 +0000 (UTC)
Message-ID: <Xns9CA9594A61F6Bwrgrpde_at_news.albasani.net>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:8 _idnYnpBISXEUTXnZ2dnUVZ8uqdnZ2d_at_bt.com:

> I'd say the same as Steve Howard.
> explain plan for ...
> select * from table(dbms_xplan.display(null,null,'outline'));
>
> We need to see the execution plan before suggesting how to change it.

Ok, here we go. I edited this from my actual SQL to the pseudo table/column names I used in my original posting.

PLAN_TABLE_OUTPUT Plan hash value: 593971052

---

---

| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%
CPU)| Time     |
-------------------------------------------------------------------------
--------------
|   0 | SELECT STATEMENT    |         |   227K|    25M|       | 20352   
(2)| 00:04:05 |
|*  1 |   HASH JOIN         |         |   227K|    25M|       | 20352   
(2)| 00:04:05 |
|*  2 |    TABLE ACCESS FULL| tab2    |  9554 |   149K|       |   140   
(2)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL| tab1    |   681K|    64M|       | 20206   

(2)| 00:04:03 |

---

---

Outline Data

---

  /*+

      BEGIN_OUTLINE_DATA
      USE_HASH(_at_"SEL$5DA710D3" "tab1"@"SEL$1")
      LEADING(_at_"SEL$5DA710D3" "tab2"@"SEL$2" "tab1"@"SEL$1")
      FULL(_at_"SEL$5DA710D3" "tab1"@"SEL$1")
      FULL(_at_"SEL$5DA710D3" "tab2"@"SEL$2")
      OUTLINE(_at_"SEL$2")
      OUTLINE(_at_"SEL$1")
      UNNEST(_at_"SEL$2")
      OUTLINE_LEAF(_at_"SEL$5DA710D3")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---

   1 - access("col2"="key")
   2 - filter("col3"='L')
   3 - filter("col1='Value')

38 rows selected.

> Either way, its choice is dictated by the arithmetic and the current
> constraints. You may have to put in a stack of hints to get the path
> you want - is that acceptable, or are you not allowed to modify the
> query at all ?

I have some limited influence on the query, but since a workaround has already been implemented, I'm rather interested in understanding what's going on in order to avoid similar problems in the future. Also, the query must work (with proper performance) with all Oracle versions >=8i.

Thanks for your help, and best regards
W. Rösler Received on Mon Oct 19 2009 - 01:46:39 CDT