Fwd: Need help with altering a query execution plan

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 4 May 2007 11:31:20 +0200

Some more info on the problem i posted earlier.

According to Oracle it isn't possible using stored outlines.

Now this raises some more questions.

1) Oracle development said that the decision wether or not to materialize a
subquery (in a WITH clause) is made at parse time. Before outlines or even
the statistics come into play. And that decision is influenced by a set of
fixed rules. Anyone have *any* clue what does rules are ?

2) We've tried to alter the execution of the query using any methods
a) Query rewrite and a materialized view won't work, because of the
correlated subquery in the select clause
b) dbms_advanced_rewrite doesn't support the WITH clause
c) stored outlines or a sql profile can't be used due to the reasons
mentioned in 1)
d) creating a view instead of the table, and specifying the hint in the view
definition doesn't appear to work either

None of the above worked.

Anyone have any other ideas ?

thanks in advance

Stefan

Other related posts: