Skip to Content
0
May 23, 2018 at 08:28 AM

using table variables in combined virtual and non virtual environment

456 Views Last edit May 23, 2018 at 08:29 AM 2 rev

According to one of the earlier posts on table variables in HANA SQL Script, a table variable is not materialized when it is created but it is rather 'inlined' whenever it is used in a query. See answer from Lars Breddemann in this thread: https://archive.sap.com/discussions/thread/3633470. According to Lars this allows for more optimizations than simply materializing, which makes sense. However, for an environment where you have data in HANA and data in virtual tables (SDA), this is unwanted behavior (at least it is in my use case). I've tried creating table variables that only query data in the virtual tables and table variables that only query data in tables that are in HANA memory and then combining the two data sets assuming that HANA would materialize both data sets. But as Lars confirmed, HANA doesn't materialize the table variables which in the end causes the overall query that contains both virtual and non-virtual tables to be pushed to the virtual database. For performance reasons that is not what I want. Is there a way to force HANA to materialize the data that I pull from the virtual tables before I join it to the data that lives on HANA? If I can't force HANA to materialize a table variable, could I use an array to 'materialize' the virtual data?