Visualize a Graphical Query Plan

This tutorial assumes that you have completed all the prerequisites tutorials. You should have setup Dynamic Tiering, created a TPCH schema, created tables, and imported data into those tables. To ensure that the tutorial steps will run smoothly, we will start by refreshing the data in the tables used in this tutorial

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 run the script below in the SQL console to refresh the data. Make sure you replace each instance of “<SID>” in the script with your system’s System Id. Verify everything executed successfully.

TRUNCATE TABLE "TPCH"."LINEITEM_DT";
TRUNCATE TABLE "TPCH"."LINEITEM_CS";
TRUNCATE TABLE "TPCH"."PARTSUPP_CS";
TRUNCATE TABLE "TPCH"."PART_DT";
DELETE FROM "TPCH"."PART_CS";

IMPORT FROM CSV FILE '/hana/shared/<SID>/HDB00/work/TPCH_DATA/Part.csv' INTO TPCH.PART_CS
  WITH THREADS 4 BATCH 10000;

IMPORT FROM CSV FILE '/hana/shared/<SID>/HDB00/work/TPCH_DATA/LineItem.csv' INTO TPCH.LINEITEM_CS
  WITH THREADS 8 BATCH 10000;

IMPORT FROM CSV FILE '/hana/shared/<SID>/HDB00/work/TPCH_DATA/PartSupp.csv' INTO TPCH.PARTSUPP_CS
  WITH THREADS 4 BATCH 10000;

IMPORT FROM CSV FILE '/hana/shared/<SID>/HDB00/work/TPCH_DATA/Part.csv' INTO TPCH.PART_DT;

IMPORT FROM CSV FILE '/hana/shared/<SID>/HDB00/work/TPCH_DATA/LineItem.csv' INTO TPCH.LINEITEM_DT;

We will now start by generating a query plan for an in-memory column store table. Delete everything in the SQL console, then paste in the script below.

SELECT
  L_RETURNFLAG,
  L_LINESTATUS,
  SUM(L_QUANTITY) AS sum_qty,
  SUM(L_EXTENDEDPRICE) AS sum_base_price,
  SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS sum_disc_price,
  SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS sum_charge,
  AVG(L_QUANTITY) AS avg_qty,
  AVG(L_EXTENDEDPRICE) AS avg_price,
  AVG(L_DISCOUNT) AS avg_disc,
	COUNT(*) AS count_order
FROM
  TPCH.LINEITEM_CS
WHERE
  L_SHIPDATE <= '2018-10-01'
GROUP BY
  L_RETURNFLAG,
  L_LINESTATUS
ORDER BY
  L_RETURNFLAG,
  L_LINESTATUS;

Right click inside the SQL console, then select Visualize Plan > Prepare. You can also press Ctrl + Shift + V. Since we are querying against the LINEITEM_CS table which was created as an in-memory column store table, we will also be generating a plan which is for an in-memory table.

A dialogue will pop up prompting you to open the SAP HANA PlanViz perspective. Check Remember my decision to skip this step in the future. Then click Yes to open the PlanViz perspective.

You should now see the Prepared Query Plan for the column store statements. You can expand and view to see the entire Prepared Plan by clicking on the drop-down as highlighted in red above.