I had a slow running query that joined on two views, A and B. I had this wrong understanding that the query is slow as it prepares the whole view and then runs the query on the view. This was totally incorrect.
Consider view in a join as a sub-query. The optimizer would try to minimize the cost.In my case the join with view A performed better than the join with view B. Both had approximately same amount of data.
I went through the following articleshttps://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_2https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
And the explain plan proved it all.The join with view A was merged as index joins in the query with base table. The reason being view A had none of this (was a simple select with inner join)
- UNION ALL/UNION view
- Outer-joined view
- Anti-joined view
- Semi-joined view
- DISTINCT view
- GROUP-BY view
While View B had many outer joins and an outer join with another view, C. The outer joins on tables were moved to View pushed predicate.
The optimizer does a view merge for a view with outer join when it is possible to do so.
The join on view C was expensive as it was a HASH join.
Consider view in a join as a sub-query. The optimizer would try to minimize the cost.In my case the join with view A performed better than the join with view B. Both had approximately same amount of data.
I went through the following articleshttps://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_2https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
The join on view C was expensive as it was a HASH join.
No comments:
Post a Comment