In this tutorial you will be learning the differences between a Prepared and Executed plan. In brief, The key difference between the two plans is that a Prepared Plan is less detailed than an Executed Plan due to the fact that an Executed Plan has more information to work with.
Lets start off by creating a Prepared Plan. In SAP HANA Studio, go to SAP HANA Administration Console perspective. In the Systems tab on the left hand side, right click on the system you are working with, and select Open SQL Console.
Copy and paste the script below into the SQL console.
Note: This script is different from the script used in the previous tutorial
SELECT
SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) as revenue
FROM
"TPCH"."LINEITEM_DT",
"TPCH"."PART_CS"
WHERE
(
P_PARTKEY = L_PARTKEY
AND P_BRAND = 'Brand#54'
AND P_CONTAINER IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
AND L_QUANTITY >= 6 AND L_QUANTITY <= 6 + 10
AND P_SIZE BETWEEN 1 AND 5
AND L_SHIPMODE IN ('AIR', 'REG AIR')
AND L_SHIPINSTRUCT = 'DELIVER IN PERSON'
)
OR
(
P_PARTKEY = L_PARTKEY
AND P_BRAND = 'Brand#13'
AND P_CONTAINER IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
AND L_QUANTITY >= 10 AND L_QUANTITY <= 10 + 10
AND P_SIZE BETWEEN 1 AND 10
AND L_SHIPMODE IN ('AIR', 'REG AIR')
AND L_SHIPINSTRUCT = 'DELIVER IN PERSON'
)
OR
(
P_PARTKEY = L_PARTKEY
AND P_BRAND = 'Brand#54'
AND P_CONTAINER IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
AND L_QUANTITY >= 29 AND L_QUANTITY <= 29 + 10
AND P_SIZE BETWEEN 1 AND 15
AND L_SHIPMODE IN ('AIR', 'REG AIR')
AND L_SHIPINSTRUCT = 'DELIVER IN PERSON'
);
Right click inside the SQL console, then select Visualize Plan > Prepare. You can also press Ctrl + Shift + V.
Click on the drop down arrow to fully expand the query plan. Keep the Prepared Plan window open for future comparison. Then go back to the SQL console tab by click on it.
Note: Notice how you cannot expand “Remote Row Scan”. The reason for this is because the Prepared Plan does not include detailed plan information from Dynamic Tiering and as such the “Remote Row Scan” does not have any information to present.