Explain Plan showing different tables

[former_member718715]

explain-plan-screen-shot.jpg

Hi Team,

I have left join on VBRP and VBRK table... when I execute Explain Plan its showing EKKO and EKPO tables which is wrong. Cardinality set for VBRP and VBRK is N:1. Please see the below screen shot tables EKPO and EKKO can be seen when I query EXPLAIN PLAN FOR SELECT VBRP_VBELN,VBRP_POSNR,IA_FKIMG FROM "CalculationView"

Please Suggest.

[former_member182343]

Hi,

Attachment is missing.

[former_member718715]

page0001.jpg

Added attachedment.

Thanks.

[michael_piesche]

Can you show your „calculation view“ as well.

[former_member718715]

Hi Lars Breddemann,

Could you please elaborate your below point:

That’s only possible with outer joins that can have a max. of a single matching record (ie the cardinality has to be specified in the view).

Cardinality I have set as N:1 for all the nodes.

[lbreddemann]

I could do that, after going through your model bit by bit. But that’s not a reasonable use of my time. The effects of the cardinality settings are documented, so all that is left here is to “do the legwork” of going through all operators that apply for this query.

Happy to do that when paid, but not as free work.

[lbreddemann]

I understand your question like this:

If only columns from those two tables are selected (from the calc view) why are other tables touched during processing?

If that’s the question then the answer is: because this is what has been specifically set up in the calculation view.
the ability to “optimise away” unnecessary joins and table accesses based on what columns are selected depends heavily on the kind of joins in the calc view.

An especially important requirement for this is that the result set must be guaranteed to not change due to the skipping of joins. That’s only possible with outer joins that can have a max. of a single matching record (ie the cardinality has to be specified in the view).

I can’t tell from the screenshot whether that’s the case or not - but based on the explain plan I would bet that this hasn’t been considered for the view.

Long story short: if you specify a join then that’s what HANA/databases in geneis going to do: compute the join.

[former_member718715]

koolncrazy4u 16 hours ago

Thanks @Lars Breddemann

You got my question correctly.

[former_member718715]

Hi All,

Thanks for the reply.. Please find attached screen shot of calculationview.

@ lars.breddemann yes calculation view used more tables I agreed.. but explain plan query executed only for VBRP and VBRK then why its showing EKKO,EKPO tables.. there is no relation between them right.

Please suggest.

Regards,

Ubaid.

[former_member718715]

Thanks @Lars Breddemann

You got my question correctly.

[lbreddemann]

From the EXPLAIN PLAN it is pretty clear that the calculation view selects more tables than just VBRP and VBRK. In fact, it accesses two different schemas.

So, there is no mystery here. Check the calculation view to learn how and (maybe) why the additional tables are in there.