SQL & PL/SQL

I'm puzzled that the no_merge hint is not working for you - perhaps it's (yet another) side effect of how Oracle treats ANSI syntax.

Step 1: put the no_merge where you've got the MATERIALIZE in your WITH TEMP_TAB example - this should put the factored subquery inline and then optimize it exactly the way you want.  (There have been cases in the past, though, where "manually inlining" a factored subquery produces a different plan from writing the subquery as a factored subquery and letting the optimizer decide to put it inline).

Can you try it both ways - simply hinting no_merge (and INLINE, if it doesn't move, which it should), and then copy the bracketed query in into place with the alias temp_tab, and tell us if you get two different plans.

Regards

Jonathan Lewis

On second thoughts - the no_merge() hint is about complex view merging, and this is an example of "simple view merging".

But it can't be stopped either by setting _simple_view_merging to false, or by hinting NO_QUERY_TRANSFORMATION

Regards

Jonathan Lewis

Hello Jonathan

I tried the followings:

1.)

WITH TEMP_TAB
AS (SELECT /*+ NO_MERGE */ 
           n_finanzprodnummer
         , fpr_finanzprodnummer
         , ...
      FROM .....
   )
SELECT COUNT(*)
  FROM TEMP_TAB
WHERE n_finanzprodnummer = fpr_finanzprodnummer 
;

Result is the same bad plan -> Here happens a Filter-Push down again.

2.)

SELECT COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer 
;

Result is the same bad plan...

Reading through a 10053 trace, I found that the "problem" feature was "simple filter predicate pushdown".

So I set parameter "_optimizer_filter_pushdown" to false and (at least in my simple join example) bypassed your problem. It still needed the no_merge() hint to "surround" the inline view where you didn't want the predicate to go. Rather than doing "alter session" to set the parameter, you could use the hint:  /*+ opt_param('_optimizer_filter_pushdown','false') */ but this is not one of the parameters listed as working with the hint, and the hint won't get reflected into the outline.

Regards

Jonathan Lewis

Jonathan,

I think the "_optimizer_filter_pushdown" parameter is pretty new and was only added in 11.2.0.2 or 11.2.0.3, I can't remember off the top of my head. The potential problem is that it prevents any filter pushdowns I think, so if there are multiple query blocks where some should push filters and some don't I'm not sure this would be feasible with that parameter.

To the OP: I think one commonly used technique to prevent a filter pushdown is adding the ROWNUM pseudo-column (or a similar analytic function ROW_NUMBER() OVER (ORDER BY NULL)) to the query block where the filter shouldn't be pushed into.

Of course, adding ROWNUM adds its own overhead to the query (there will be an additional COUNT operator) and can have other side effects, some of them rather nasty when dealing with Parallel Execution.

Apart from that it usually makes sense to filter the data as early as possible, so possibly the filter pushdown itself is not the problem but as a side effect of the pushdown you get a less efficient execution plan, so it might make sense to understand what possibly goes wrong with the plan when the filter is pushed.

Randolf

Hello Jonathan

Super! Your solution works!

SELECT /*+ opt_param('_optimizer_filter_pushdown','false') */
       COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;

Thank you and regards,

David

Hello Randolf

Yes. You are right, in this case the optimizer hint operates on the whole query.

I tried your solution and it works too!

SELECT COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
          WHERE ...
            AND ROWNUM >= 0

       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;

Thank you very much!

-> In this case the solution is the preventing of using the filter push-down. Reason: There are Views in the joins which have DB-links and the cost is not estimated good because of the remote objects.

This query is very complicated.

But anyway, I have learnt something again! Thank You all!

Regards, David

DavidBerger wrote:

Hello Randolf

Yes. You are right, in this case the optimizer hint operates on the whole query.

I tried your solution and it works too!

Hi David,

actually you don't need to reference ROWNUM in the WHERE clause, it should be sufficient to add ROWNUM to the list of projected columns. This should minimize the overhead - of course you might need to add then another layer of projection if you don't want to have that ROWNUM information shown in the result set.

Testing a query's performance by using COUNT(*) can be quite misleading, by the way, since the optimizer tries to be clever and avoid any work that's unnecessary. In your particular case it might not make a difference, but in general I would use a different technique, either SET AUTOTRACE TRACEONLY STATISTICS in SQL*Plus or wrapping the original query like this:

SELECT /*+ NO_MERGE(x) */ * FROM (

original query goes here

) x

WHERE ROWNUM > 1

;

The SQL*Plus approach needs to fetch all data to the client but doesn't spend any time in displaying/formatting it, the latter approach discards all data on the database server already, but needs to run the full projection logic due to the NO_MERGE hint. For very large data sets with many rows the FILTER operator that discards all rows on the server side can consume significant CPU time, but for smaller result sets this gives a pretty good and realistic indication of the actual runtime.

Randolf

DavidBerger wrote:

Hello Randolf

Yes. You are right, in this case the optimizer hint operates on the whole query.

I tried your solution and it works too!

One other thing: When using ROWNUM in current releases you don't need to add an explicit NO_MERGE hint as the query block is marked as non-mergeable due to the ROWNUM anyway, but it probably makes sense to use an explicit NO_MERGE hint nevertheless to make the intention clear and add another comment to the SQL stating why this all was done.

Randolf

Hello Randolf

Ok. Thank you very much for your suggestions! They are really good!

DavidBerger wrote:

Hello Jonathan

Super! Your solution works!

SELECT /*+ opt_param('_optimizer_filter_pushdown','false') */
       COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;

Thank you and regards,
David

David,

As Randolf suggests, though, I shouldn't really have mentioned it - but I got a bit curious and decided to see if I could find a method when I realised that the no_merge wasn't supposed to work.

As Randolf also indicates - there's bound to be a way of getting the data faster while having the predicate operating earlier - but there's probably an optimizer "glitch" that's causing a change of plan because an early appearance of that predicate reduces the cardinality of some join to a point where a change of join order or method looks good.  ANSI SQL is much harder to unpick when this happens, though, so the rownum trick is a reasonable compromise between complexity and efficiency.

Regards

Jonathan Lewis

Thank You Jonathan very much!

Randolf Geist wrote:

Jonathan,

I think the "_optimizer_filter_pushdown" parameter is pretty new and was only added in 11.2.0.2 or 11.2.0.3, I can't remember off the top of my head. The potential problem is that it prevents any filter pushdowns I think, so if there are multiple query blocks where some should push filters and some don't I'm not sure this would be feasible with that parameter.

To the OP: I think one commonly used technique to prevent a filter pushdown is adding the ROWNUM pseudo-column (or a similar analytic function ROW_NUMBER() OVER (ORDER BY NULL)) to the query block where the filter shouldn't be pushed into.

Of course, adding ROWNUM adds its own overhead to the query (there will be an additional COUNT operator) and can have other side effects, some of them rather nasty when dealing with Parallel Execution.

Apart from that it usually makes sense to filter the data as early as possible, so possibly the filter pushdown itself is not the problem but as a side effect of the pushdown you get a less efficient execution plan, so it might make sense to understand what possibly goes wrong with the plan when the filter is pushed.

Randolf

Randolf,

I just did a quick check on lists of parameters, and you're right; this one appeared very recently - 11.2.0.3

And you're also right that the OP shouldn't really make use of it.

Regards

Jonathan Lewis