Explain Plan unable to pick the Part IDs when expression is used in filter condition SAP HANA SPS 2

[former_member565459]

Hello Team,

I've a partitioned table on a column with data-type date.
The Explain Plans differ when I change the where condition for a typical hard-coded date and an expression

Case 1 : When I use normal where condition without any expression:

Case 2 : When I use an expression to derive the same date in where condition

I cannot hard-code the date condition in my requirement. Hence, I will always need to use the expression to derive the desired date to be applied in filter. But, at the same time I want to take advantage of the partitioned table.
What might be the ideal way to the result from case 1 using an expression in filter condition.

florian.pfeffer , lars.breddemann Please, enlighten me on this scenario.

TIA.

Regards,

Prathamesh H.

[Cocquerel]

You may use script like this:

DO BEGIN
	DECLARE MYDATE NVARCHAR(8);
	SELECT TO_NVARCHAR(ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE,-3)),1),'YYYYMMDD') INTO MYDATE FROM DUMMY;
	EXECUTE IMMEDIATE 'SELECT * FROM SUM_GMA_BILLING_SUMARY WHERE BILLING_DATE >= ''' || :MYDATE || '''';
END

[former_member565459]

How do I check the explain plan of these SQL statements?

[Cocquerel]

You can find them in SQL Plan Cache (view SYS.M_SQL_PLAN_CACHE) and then, retrieve plan_id value from there.

Then, you can run following statements. Note that OPTIMIZER ADMIN privilege is required

explain plan set statement_name = '<some_name>' for sql plan cache entry <plan_id>;
select * from explain_plan_table where statement_name = '<some_name>';
delete from sys.explain_plan_table where statement_name = '<some_name>';