Check filter push-down with Debug View

This example is referenced in my other post about how to check the push-down of filters. Please have a look at the other post to get a better understanding of the context for this example

The following example illustrates how the feature “Debug this view” can be used to analyze the push-down of filters.

During the Debug session you will see the outcome of the Calculation View instantiation process (for more details on the instantiation process see e.g., SAP Note 1764658). However, you will not see optimizations that are done at later stages (see end of this text for an example)

Start the debug session for the Calculation View by pressing “Debug this view” button while the Semantics node is selected:

To start the debug session select the node “Semantics” and press button “Debug this view”

Delete the proposed standard query, replace it with the query on the view under investigation and execute by pressing the green arrow on the right.

Debug query to run. Execution button is highlighted in yellow.

Click at the individual nodes on the left side and check tab “Debug Query” on the right to see whether the filter is used within the node:

Filter shows up in Debug Query at node “J_SO_BP”

Filter does not show up at node “Proj_BP” (this should come as no surprise given that field “PHONE_NUMBER” is not present at all)

Filter is shown in Debug Query for node “Proj_BPC” which uses table “BusinessPartnerContacts” as a source

If you see a filter at the node which uses the respective table as a data source you know that the Calculation View instantiation process found a way to push down the filter. However, if you don’t see a filter here it is still possible that the other optimizations found a way to push the filter down as the following example illustrates

Example where Debug View does not show the push-down of the filter even though filter is pushed-down

Change the query so that the filter is provided by a join:

SELECT
 "PartnerId",
 "TITLE",
 "LAST_NAME",
 "FIRST_NAME",
 "GENDER",
 SUM("NetAmount") AS "NetAmount"
FROM
  <...>::S2EX1_SALES_CUSTOMER" a,
  (SELECT '7412697340' "PHONE_NUMBER" from DUMMY) b 
WHERE
   a.PHONE_NUMBER=b."PHONE_NUMBER"
GROUP BY
  "PartnerId",
  "TITLE",
  "LAST_NAME",
  "FIRST_NAME",
  "GENDER"

Query that filters through a join

In this query the view S2EX1_SALES_CUSTOMER is joined to a single entry and thus filtered by this entry (7412697340). The question is where this filter will be applied: already at table “BusinessPartnerContacts” or later.

Using method “Debug this View” no filter push-down is shown:

In this example “Debug this view” does not show a push-down of the filter when the filter is provided by a SQL join

We will apply now each of the two other methods described in this post

a) Explain Plan shows that the plan cannot be totally unfolded (you do not see the tables) and therefore filter push-down cannot be further investigated with it (see e.g., SAP Note 2291812 for more details on unfolding):

Tables are not visible using EXPLAIN PLAN  because the plan could not be totally unfolded

b) Visualize Plan/Analyze SQL show that the filter was pushed down:

Visualize plan after drill-down to table

Analyze SQL after filtering for operator “Basic predicate”

Using Visualize Plan and Analyze SQL both show that the filter is pushed-down to table “BusinessPartnerContacts”

As a summary of this example, the debug view does not show the filter push-down that is due to later optimizations and the Explain Plan cannot be used because the plan is not completely unfolded. Instead the filter push-down can be seen using the methods “Visualize Plan” and “Analyze SQL”.

Therefore, if you see a filter push-down when using method “Debug View” you know that the filter has been applied early and you can basically stop your investigation. However, if you do not see a filter push-down by using “Debug View” you need to check in addition with the other methods that are discussed in this post

This example is referenced in my other post about how to check the push-down of filters. Please have a look at the other post to get a better understanding of the context for this example