Skip to Content

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

 

In the following it will be shown with help of an example how the push-down of filters can be analyzed using feature “Explain Plan”.

Generate an Explain Plan for the to be analyzed statement by prefixing the statement with string  “EXPLAIN PLAN FOR” and executing the statement

EXPLAIN PLAN FOR
SELECT
         "PartnerId",
         "TITLE",
         "LAST_NAME",
         "FIRST_NAME",
         "GENDER",
         SUM("NetAmount") AS "NetAmount"
FROM
         <...>::S2EX1_SALES_CUSTOMER"
WHERE
        PHONE_NUMBER='7412697340'
GROUP BY
        "PartnerId",
         "TITLE",
         "LAST_NAME",
         "FIRST_NAME",
         "GENDER"

Running EXPLAIN PLAN using SQL  

 

As an alternative you can also right-click at the statement and select Explain Plan in SAP HANA Studio:

EXPLAIN PLAN through user interface

 

In the current example this generates the following output:

Output EXPLAIN PLAN: Filter on “PHONE_NUMBER” is applied on table “BusinessPartnerContacts”

 

From the result you can see that the filter on “PHONE_NUMBER” is effective on table “BusinessPartnerContacts”. As a consequence, in this example, the OUTPUT_SIZE is “1” which illustrates that the filter is effective on the table source. However, in some cases it might not be possible to unfold the whole plan. In this case you would see COLUMN VIEWs but not the tables themselves. If you don’t see the tables of interest in the EXPLAIN PLAN you can use the other two methods mentioned in this post  to further analyze the filter push-down

 

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

    1. Jan Zwickel Post author

      Hi Shanthi,

      There are basically two situations:

      a) sometimes pushing down the filter will change the semantics and thus lead to potentially different results. These situations are discussed e.g., here . In these situations you do not want that the filter is pushed down automatically. The post discusses some flags that are avaiable with HANA 2.0 SPS02 that allow to overrule this and force the filters to be also pushed down in these situations

      b) sometimes it could happen that the optimized plan does not decide for a push-down of a filter. This does not in itself neccessarily mean that the decision is wrong (think about a filter that reduces nearly no data but needs to scan the unaggregated data). Therefore, looking for a missing filter is especially interesting in situations where a large amount of data is indeed transfered and you suspect that a filter is not really used that could be used. Generally speaking, you should find situations with bad decisions pretty rarely but in complex modelling they might occur.

      Best,
      Jan

      (0) 

Leave a Reply