Clear / Refresh HANA Local Tables ?

[TanmayVerma]

Hi All,

I have been writing SAP HANA SQL Procedures for quite a bit for now and being experienced in ABAP i am always thinking if we need this feature or that in HANA SQL or not .

So here is my question:

1. We use lot of local tables in SQL script for procedures. Do we need to perform a memory flush or clear / refresh for such local tables ?

For example :

************************************************

LT_TAB1 = Select * from TABLE1 WHERE <condition>;

Assume LT_TAB1 fetches 10,000 recrods

LT_TAB2 = Select <field1>, <field2> from LT_TAB1 where <condition>;

Now after this statement if i want my table LT_TAB1 to be cleared:

i) Is it possible ? If yes then whats the keyword / statement to use.

ii) Is it even required ? Since everything is in-memory do we even need this as there is a large expense of memory available.

iii) Any recommendations for best practices to handle local tables ?

BR,

Tanmay

[lbreddemann · Accepted solution]

Your procedures seem to follow the advice to break down logic by using table variables.

An important piece of concept here is: table variables are not the same as internal tables in ABAP.

Table variables don't store/hold any data, while ABAP internal tables (and temporary tables in HANA) do store/keep data.

And since they don't store any data, there is no need to "clean up" here.

A bit of context:

SQLScript uses table variables similar to how you can use functions in your code.

Depending on the actual code-path taken (i.e. what parameters are used and which WHERE conditions have been applied), the code compiler may choose to INLINE the SELECT for the table variable and/or create an intermediate result set (materialise the data) that is then used in the dependent processing steps.

In some cases, the code compiler might even figure out that a table variable won't influence the result set at all and not execute the SELECT for it at all.

Just as with plain SQL statements, HANA handles the creation and deletion of those intermediate result sets automatically. The memory is released back to the HANA-internal memory pool once the statement is finished (at the latest).

Coming back to your example:

LT_TAB1 = Select * from TABLE1 WHERE <condition>;
// Assume LT_TAB1 fetches 10,000 recrods
LT_TAB2 = Select <field1>, <field2> from LT_TAB1 where <condition>;

What you want to happen here is obviously not that HANA first reads the 10000 records with all its columns of LT_TAB1 only to through away some of them when the WHERE condition in LT_TAB2 gets applied.

Instead, you are interested only in the two columns of LT_TAB2 that satisfy the combined WHERE conditions of LT_TAB1 and LT_TAB2.

And that's what the SQLScript compiler gives you by inlining the statements

SELECT 
    <field1>, <field2> 
FROM 
   (Select * from TABLE1 WHERE <condition_1>) lt_tab1
WHERE 
   <condition_2>;

which is then rewritten to something like this:

SELECT 
    <field1>, <field2> 
FROM 
    TABLE1 lt_tab1
WHERE 
   <condition_2> AND <condition_1>;

See? No "internal table" required for this one.

And no cleaning up, either.

[TanmayVerma · Accepted solution]

Wow , Thanks lars.breddemann for such a nice explanation. !! This is a new learning for me.

BR,

Tanmay.