Optimized Views
Gary Sherman
May 31, 2013

Within your Clarify / Dovetail schema, there’s a concept of an Optimized View. This is really an implicit feature of a view, but if you’re aware of it, and understand how they work, you can use it to your advantage.


An optimized view happens when you have a join to a table, but the only field that you’re including from the joined in table is the objid field, and the relation type from the main table to the joined table is a OTOP or MTO.


When these conditions are met, then the schema editing tools (ddcomp, SchemaManager, Dovetail SchemaEditor) will optimize the underlying view, meaning that it will not actually create a join to the table, but instead will simply use the column from the main table as part of the select clause.


That’s a bunch of info to digest, so lets look at an example.


Simple Example


Lets say I want a view that inner joins the case table to the condition table. The view fields will include the case objid and id_number. We’ll also include the condition’s objid. Simple.


We would expect the schema editing tool to create the view so that it looks something like this:


But, what actually happens is that the view is created something like so:



Notice that there isn’t a join to the condition table, as we don’t actually need to – as we already have the condition’s objid data on the case table (because it’s a OTOP relation).


Therefore, this is an optimized view. When this happens, there’s a flag set on that view in the ADP table that indicates that it’s an optimized view.


Also, notice that because this is an INNER join, we want to make sure we actually have a related condition, so the schema editing tool adds in a WHERE clause of case_state2condition IS NOT NULL. This guarantees that there is a related condition, which is what we expect from an INNER join.



In BOLT, we show the flags for a view, and that includes the VIEW_OPTIMIZED flag.  Here’s an example:


Inner Join


The user_case_load view (which is a baseline view) is OPTIMIZED because we’re doing a join to the user table, but since we’re only including the objid column from the user table, and the case_owner2user relation is a MTO, we can simply use the case_owner2user column to supply the user objid date, and not have to join in the user table.


Here’s what the view definition looks like:


Notice that the SELECT clause has: table_case.case_owner2user AS user_objid


Notice that the WHERE clause has: table_case.case_owner2user IS NOT NULL

What happens if it’s an OUTER join?


We can look at the wipelm_case view (which is a baseline view) and see what happens with an OUTER join.


In that view, we have an OUTER join from case to workaround. It’s outer because a case may or may not be linked to a workaround.





The underlying view definition looks like this:



Notice a couple of things:


1. The workaround view column is getting its data from table_case.case_soln2workaround


2. There is NOT a where clause enforcing that case_soln2workaround is not null.


So the workaround column will either return an actual objid, or NULL, which is what we would expect from an OUTER join.

How can I take advantage of an optimized view?


Let me give a real-world example of where an optimized view was useful. I was recently working with a customer, and we were working on tuning the performance of a particular view that joined the case table to a bunch of other tables. To improve the query performance, we wanted to add an additional constraint so that we only returned open cases. By looking at the query explain plan, and some experimenting, we concluded that if added an additional constraint ofWHERE case_wip2wipbin > 0 then our query performance was good.


However, we didn’t want to actually join in the wipbin table, and then constrain on a field from that table, as that wouldn’t have the same effect. And, there were other queries that used that same view (and included closed cases, meaning that case_wip2wipbin would be null), and we didn’t want to affect those queries.


So, by understanding how optimized views worked, we were able to add an outer join to the wipbin table, but because we only included the wipbin’s objid, the schema editing tool optimized that view, and we are now able to add a constraint using that case_wip2wipbin column, which actually constrains the base table (table_case), so we get the query performance that we’re looking for.



In short, if you understand what an optimized view is, and how they work, you can use them to your advantage when needed.


Hope this helps.


Rock on.

Leave a Comment

International: +1 (512) 610-5400
Toll Free: 1 (800) 684-2055