Checking filter-push down with Explain Plan

[jan_zwickel]

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