Union Pruning in HANA SP11

[Former Member]

Dear Experts,

We recently upgraded HANA from SP8 to SP11. In production we are getting performance issues due to more countries going live.

We have 3 ECC source systems for 3 regions. We are creating CA view for each schema and union them in top view. We are thinking Union pruning

will improve the performance in this scenario. LOGSYS is the key column differentiating these projections. Created a pruning configuration table with the

required values and maintained the pruning table in the semantics of the CA view.

When we checked Visual execution plan, still it is hitting all projections and bringing 0 records, except from 1 projection.

Executed SQL statement having Where condition with Logsys value which is maintained in the pruning table.

Please provide your valuable suggestions for achieving the Union pruning. Thank you.

Regards,

Mohan.

[Former Member]

Hi,

Got solution for this issue.

Added an execution hint ce2qo_disable_unfolding = 1. Now union pruning is working as expected.

Regards,

Mohan.

[former_member185132]

Hi,

You can use the Union with Constant Values option for this:

To make it work though, you cannot use the existing LOGSYS field. Because when you filter for LOGSYS='CA', HANA has no way of knowing that you actually need data only from the CA view. That is a fact only the person building the view is aware of.

So you'll need to create a new field in the Union node, as explained in the link. You can assign values such as CA, US etc to that new field. And then, instead of filtering on LOGSYS, filter on this new field.

Regards,

Suhas

[Former Member]

Hi Suhas,

Constant column in Union node is there from quite long time. But in latest versions of HANA, we have pruning configuration table where we maintain these constant values centrally at one place.

In run time, the pruning table must lookup for constant and choose the correct projection to execute, which is not happening right now.

Regards,

Mohan.

[varma_narayana]

Hi Mohan,

Hope you are doing great..

I have implemented a calculation view with the Union pruning table and it is working as expected (i.e. querying only the relevant projection).

Can you please share the details of your view and the pruning configuration table definition / contents?

That might me help to check if there is anything wrong.

regards,

Varma

[Former Member]

Hi Varma Sir,

Good to see a message from you. I have mailed you the detailed steps. Please suggest what could be the issue.

Regards,

Mohan.