> What I notice, however, is that in case a client uses two tables that are joining the protected view, that the corresponding security constraint and the joins themselves are duplicated. Is there a way to prevent this?
Taking the security component out of this, you would still see the same initial structure for the join - that is the USER_MACHINE will be repeated. [TEIID-160] Remove redundant self-joins - JBoss Issue Tracker covers generally using query minimization logic to remove redundant self-joins, but has not yet been worked.
Thank you Steven. That's not very good news then. I suspect similar logic wrt the security constraint.
> I suspect similar logic wrt the security constraint.
The security constraint is entirely secondary as it will always be added to the applicable tables/views in the plan to filter in the appropriate manner.
Another thing to consider is that if two subplans produce identical pushdown queries, then the results will re-reused in the query plan. Otherwise if you have a situation where having the redundant self-join is causing a performance issue, then please update the issue with your details and we can get it addressed with a higher priority.
The results of what will be re-used? We are currently investigating the performance penalty of having these redundant self joins but they are pushed down anyway so I'm not sure what you mean with "if two subplans produce identical pushdown queries, the the results will be re-reused"...
> The results of what will be re-used?
Meaning that is two subplans result in the same pushdown query, then the results of the pushdown query will be reused rather than re-queried. However that doesn't apply here if everything is pushed down.