Re: Need help with altering a query execution plan

OK I remember I somewhere somehow recently read a question is there a hint opposite to materialize and I assume this time this is the same question.
So if my assumption is correct then read this post http://www.freelists.org/archives/oracle-l/12-2004/msg01256.html and here is an example:
SQL> set autot on explain
SQL> ed
Wrote file afiedt.buf

  1 with a as (select count(*) a from dual d)   2 select * from a
  3 union all
  4* select * from a
SQL> /          A

         1
         1

Elapsed: 00:00:00.00

Execution Plan

   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=2 Bytes=2
          6)

   1    2     RECURSIVE EXECUTION OF 'SYS_LE_2_0'
   2    0   TEMP TABLE TRANSFORMATION
   3    2     UNION-ALL
   4    3       VIEW (Cost=2 Card=1 Bytes=13)
   5    4         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6644_C4AD8CE'
          (Cost=2 Card=1 Bytes=13)

   6    3       VIEW (Cost=2 Card=1 Bytes=13)
   7    6         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6644_C4AD8CE'
          (Cost=2 Card=1 Bytes=13)

SQL> ed
Wrote file afiedt.buf

  1 with a as (select /*+ inline*/ count(*) a from dual d)   2 select * from a
  3 union all
  4* select * from a
SQL> /          A

         1
         1

Elapsed: 00:00:00.00

Execution Plan

   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=22 Card=2 Bytes=
          26)

   1    0   UNION-ALL
   2    1     VIEW (Cost=11 Card=1 Bytes=13)
   3    2       SORT (AGGREGATE)
   4    3         TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168)
   5    1     VIEW (Cost=11 Card=1 Bytes=13)
   6    5       SORT (AGGREGATE)
   7    6         TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168)

SQL> Gints Plivna
http://www.gplivna.eu

2007/5/3, Stefan Knecht <knecht.stefan_at_gmail.com>:
> .. .Or does perhaps someone have any clues how I could alter the stats of
> the involved table to get the optimizer to favour inline execution instead
> of materializing the subquery ?
>
> Stefan
>
>

--
http://www.freelists.org/webpage/oracle-l