Skip to Content
Previous

Detailed Query Plan and Indexes

By Balkarann

Generate and access a detailed html query plan. Create indexes on columns.

You will learn

  • Accessing a more detailed HTML query plan through SAP HANA Studio.
  • Creating indexes on table columns to improve query performance.
  • Analyzing the impact of indexes on query plans.

Details


Step 1: Enabling the Generation of a Query Plan

In addition to the graphical query plan available through the PlanViz perspective, you can access an even more detailed query plan for the Dynamic Tiering portion of the query. In order to do this we will need to turn on a debug level of tracing.

In SAP HANA Studio, go to SAP HANA Administration Console. In the Systems tab on the left, double click on the system you are working with.

Open Overview

After the administration console opens, go to the Trace Configuration tab.

Trace Configuration

Click on the pencil icon under Database Trace to edit debug level.

Edit Database Trace

A window should pop up where you need to click on the drop-down icon beside INDEXSERVER. Check the box for Show All Components on the bottom left.

Find Configuration

You should now be able to see the field fedtrace under INDEXSERVER. Alternatively, you can also enter “fedtrace” in the type filter search box to find the field. In this row, click under the System Trace Level and select the option DEBUG and then click Finish.

Change fedtrace
Step 2: Generate and Open a Detailed Plan by Running a Query

In SAP HANA Administration Console, open a new SQL console.

SQL Console

Copy and paste the script below into the SQL console.

SELECT 100.00 * SUM(CASE
    WHEN P_TYPE LIKE 'PROMO%'
      THEN L_EXTENDEDPRICE * (1 - L_DISCOUNT)
    ELSE 0
  END) / SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) as promo_revenue
FROM
  "TPCH"."LINEITEM_DT",
  "TPCH"."PART_DT"
WHERE
  L_PARTKEY = P_PARTKEY
  AND L_SHIPDATE >= '2014-10-01'
  AND L_SHIPDATE < '2014-11-01';
Script

Right click inside the SQL console, then select Visualize Plan > Execute. You can also press Ctrl + Shift + X.

Execute Plan

In SAP HANA Studio, go to SAP HANA Administration Console. In the Systems tab on the left, double click on the system you are working with.

Open Overview

After the administration console opens, go to the Diagnosis Files tab.

Diagnosis File

The detailed plan file would have your Dynamic Tiering node as host. You can sort the entries by clicking on the Host header. Click on the Modified header to sort the files by modified date. Find the most recent HTML file with the name similar to “dbo_HANA_ES_###...##.html”. You can also enter “dbo_HANA_ES_*.html” into the Filter box. Right click on the file and select Download… to download the file.

Open HTML Plan

In the “Save As” pop up window, select where you want the file to be saved. Then click Save to save the file.

Navigate to the directory where the detailed query plan file is saved and double click to open it using your default web browser.

You should now see the detailed query plan. Scroll toward the end to find Condition 1 Index.

The highlighted index in the query plan below is the Fast Projection (FP) index. The FP indexes are automatically generated for each column in a Dynamic Tiering table. This FP index optimizes projections and enables certain kinds of search conditions to be evaluated. In the absence of any other index, the query plan will normally reference the FP index for the specific column.

Fast Projection index
Step 3: Creating an Index

Go to SAP HANA Administration Console and open a new SQL console.

Copy and paste the script into the console. Then run the script to create a new index.

CREATE INDEX L_SHIPDATE_INDEX ON LINEITEM_DT (L_SHIPDATE);

Repeat the Generate and Open a Detailed Plan by Running a Query section to generate another detailed plan.

See how it is faster…
As you can see, after creating the index, if we run the query again and view the detailed query plan generated from the fedtrace, we can see that the created index L_SHIPDATE is being used. The type of index used here is the High Group (HG) index which is commonly used for join columns with integer data types. When we create the index, HANA is smart enough to coordinate and figure out which index would be the best choice. In this case, the query optimizer has determined that the HG index will be more effective than the FP index and chosen the HG index for the query execution.

High Group Index

Next Steps

  • Tutorials:

Updated 10/05/2017

Time to Complete

20 Min.

Beginner

Next Steps

  • Tutorials:
Next
Back to top

Call me now