From the filter conditions you can see that the function will execute once for every row in the first part of UNION, whereas it will execute only once for the second part. I traced using DBMS_PROFILER to confirm this. What can be the reason? (BTW, this is why I was trying to push in the MATERIALIZE hint).

Many thanks.

]]>By: CJhttps://jonathanlewis.wordpress.com/2012/05/24/subquery-factoring-7/#comment-46795
Wed, 30 May 2012 14:37:41 +0000http://jonathanlewis.wordpress.com/?p=8937#comment-46795Looks like I have found my answer. The NO_MERGE hint needs to be added not to the CTE, but to every query wherein the CTE referred.

The sourcecode tags (which you correctly put in square brackets) have an open and close form. To close you need a leading /, as in “/sourcecode” (but in square brackets, not quote marks).

If you look in view v$sql_hints you will find that the inline and materialize hints do not have an “outline version” which (I believe) means you can’t inject them through a stored outline, profile, or SQL Baseline. In your case it would be sufficient to inject a no_merge() hint into the subquery factoring clause (CTE). Since the query block in the clause is sel$1 you need a minimum of /*+ no_merge(@sel$1) */ in your outline/profile to get the plan:

Is it possible to ‘push’ the effects of materialize hint in a query plan using SQL profile?

I am trying to attach materialize hint to a query using SQL profile, but the plan generated through the SQL profile doesn’t match with the one generated through direct hint. Version is 10.2.0.5 EE on RHEL.

With materialize hint, the function will be executed only once. The problem is – I don’t have access to the source code. So the only option for me is to use SQL profile.

I took the outline data resulting from the query with materialize hint, created a SQL profile. However, the plan generated by SQL profile looks different from the one generated with materialize hint. The code to generate SQL profile is too big, so giving below a ‘shortcut’ by using outline data as a hint.

In the latter two plan, it is using a HASH JOIN instead of NL, so I believe it will execute the function only once. But why does it not create a temp table, as it does with materialize hint? Is there any way to force this?