Tuesday, December 20, 2016

Query join with View - Oracle Performance

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)
  • 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.

No comments: